GEL Script Question

[Arunachalam Angusamy]

Hello Everyone, 

I am pulling the columns that are required in SQL and concatenating with ~ simple since all these details should come as a one column. When I execute the below SQL in SQL Developer, it gives the expected result but it shows only NULL value when I execute it through GEL Script. I put the log statement in the GEL Script to see the result but it is still giving me the NULL values for the same SQL. 

 <sql:query escapeText="0" var="result"> <sql:query escapeText="0" var="result">       
<![CDATA[           
SELECT            'Project ID: '||(select code from inv_investments where idea_id = inv.id and odf_object_code='project') ||'~ Project Name: '|| INV.NAME ||'~ Project Start: '|| TO_CHAR(INV.SCHEDULE_START,'dd-Mon-YYYY') ||'~ Project Finish: '|| TO_CHAR(INV.SCHEDULE_FINISH-1,'dd-Mon-YYYY') ||'~ Project Manager: '|| (SELECT SRM.FULL_NAME FROM SRM_RESOURCES SRM WHERE SRM.USER_ID = IDA.INITIATOR_ID) ||'~ Project Manager ID:'||            (SELECT substr(srm.sms_email, 0, instr(srm.sms_email, '@')-1) FROM SRM_RESOURCES SRM WHERE SRM.USER_ID = IDA.INITIATOR_ID) ||'~ Development : '||            ODFINV.methodology ||'~  Function: '||             ODF.function ||'~ IT Application Owner: '||            (SELECT SRM.FULL_NAME FROM SRM_RESOURCES SRM WHERE SRM.USER_ID = IDA.INITIATOR_ID) Description FROM INV_INVESTMENTS INV JOIN INV_IDEAS IDA ON IDA.ID = INV.ID JOIN ODF_CA_IDEA ODF ON ODF.ID = INV.ID JOIN odf_ca_inv ODFINV ON ODFINV.ID = INV.ID   WHERE INV.ODF_OBJECT_CODE='idea' AND INV.ID = ?       
]]> <
sql:param value="${gel_objectInstanceId}"/>   
</sql:query>

Any one come across this issue ? Please advise.

[Alan Brobst]

In Oracle SQL, when you concatenate multiple values into a single string, if any of the values is null the entire string becomes null.

Try wrapping each individual field inside an NVL() function and giving a non-null value, then concatenate all those values with ||.

Let me know if this works!

Alan

------------------------------
Alan Brobst
GE Transportation - a Wabtec Company
[e-mail]

------------------------------

[Roland Parrotte]

​Alan, are you sure as the below will return data:
select NULL || to_char(sysdate, 'YYY-MM-DD') || 'A' description from dual

[Alan Brobst]

I stand corrected.  The query below, which I deliberately constructed to return a null value for d.subdate, still returned a non-null value in the "outer" query.  That's very surprising, as I swear I've seen the behavior I described in my original response in past queries.  Thanks for pointing that out, Roland!

select aa.prfinish, aa.subdate,
aa.subdate || 'ABC' || '123'
from
(
select t.prfinish, d.subdate
from PRTIMEPERIOD t
left outer join ODF_CA_TS_SUB_DEAD d
on t.prfinish = d.subdate
where t.prfinish = to_date('2019-01-01', 'YYYY-MM-DD')
) aa

[Arunachalam Angusamy]

After implementing NVL, it is working as expected. Thank you everyone