UPDATE: It seems to be this value in particular and nothing to do with the query itself. When run directly against the database it returns values without issue. However, when extracting the value to .csv via GEL or to a portlet via NSQL the value is always NULL. I am still looking in to the issue but perhaps this might give someone more context or a clue on what might be the issue.
Even a simple query like:
SELECT INV_INVESTMENTS.CODE, FYActualsCost.FYACTLABOR FY_LABOUR_ACTUALSFROM INV_INVESTMENTS LEFT OUTER JOIN ( SELECT INV_INVESTMENTS.ID PROJECT_ID, REPLACE(Sum(PPA_WIP_VALUES.TOTALCOST), ' ', '') AS FYACTLABOR FROM INV_INVESTMENTS LEFT OUTER JOIN PPA_WIP ON INV_INVESTMENTS.CODE = PPA_WIP.PROJECT_CODE LEFT OUTER JOIN PPA_WIP_VALUES ON PPA_WIP.TRANSNO = PPA_WIP_VALUES.TRANSNO
WHERE PPA_WIP.TRANSDATE >= (CASE WHEN TO_NUMBER(TO_CHAR(SYSDATE, 'MM')) < 7 THEN '01/JUL' || TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'YY'))-1) ELSE '01/JUL/' || TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'YY'))) END) AND PPA_WIP.TRANSDATE <= (CASE WHEN TO_NUMBER(TO_CHAR(SYSDATE, 'MM')) < 7 THEN '30/JUN' || TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'YY'))) ELSE '30/JUN/' || TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE,'YY'))-1) END) AND PPA_WIP_VALUES.CURRENCY_TYPE = 'HOME' AND INV_INVESTMENTS.IS_ACTIVE = 1
GROUP BY INV_INVESTMENTS.ID )FYActualsCost ON FYActualsCost.PROJECT_ID = INV_INVESTMENTS.ID
Will return values for FYACTLABOR, but when taken put in GEL or NSQL, the returned value is always NULL.
I'm currently adding another column to an already working GEL process that writes a large query for project information to a .csv file and emails it. When adding this column, the query returns values for current Financial Year Labour Actuals for each of the projects. However, when I add this value to the list of values that exports to .csv, it returns NULL (or in this case 0 as I wrap it in a Nvl() in SELECT).
Is there any reason why this particular value might be doing this? I've attached the entire GEL script and query for reference. It might be a little hard to read, it is quite messy.
Any insights or ideas would be extremely appreciated!
EDIT: just replaced the sql file with one with less people information in it! whoops!
1) Can you run the query in a SQL editor and get a result (I use the excellent XOG Query Bridge from IT ROI Solutions)?
2) Can you change it to define the time window from the annual time period from BIZ_COM_PERIODS instead (assuming your organisation has defined an annual time period, and it equates to the fiscal year):
SELECT ... JOIN BIZ_COM_PERIODS ON PPA_WIP.TRANSDATE >= BIZ_COM_PERIODS.START_DATE AND PPA_WIP.TRANSDATE <= BIZ_COM_PERIODS.END_DATE AND BIZ_COM_PERIODS.START_DATE <= SYSDATE AND BIZ_COM_PERIODS.END_DATE > SYSDATEWHERE ... AND BIZ_COM_PERIODS.PERIOD_TYPE = 'ANNUALLY'
AND BIZ_COM_PERIODS.IS_ACTIVE = 1 ...
Not sure the above is 100% - I don't have access to query right now but it may be helpful?
Thanks for the suggestions. I can confirm that query is returning values for all columns correctly. It's only somewhere between the query running successfully and writing to .csv where something goes wrong, or something in the GEL makes the query execute incorrectly.
For the other suggestion, unfortunately we use a custom financial solution in PPM so we don't have defined time periods to use BIZ_COM_PERIODS.
It was a data conversion issue. I had to convert the output of the THEN/ELSE clauses to be TO_DATE( [data], 'DD/MON/YY'). Why GEL or NSQL needed this extra conversion while the direct query on the DB did not, I am still unsure. However, it has been resolved.