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_ACTUALS
FROM 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!