AnsweredAssumed Answered

Why is GEL script writing returned query value as NULL?

Question asked by Matthew.Paxman on Feb 7, 2017
Latest reply on Feb 9, 2017 by Matthew.Paxman

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!

 

Outcomes