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
Original Message:
Sent: 06-18-2019 03:39 AM
From: Roland Parrotte
Subject: GEL Script Question
Alan, are you sure as the below will return data:
select NULL || to_char(sysdate, 'YYY-MM-DD') || 'A' description from dual
Original Message:
Sent: 06-07-2019 03:36 PM
From: Alan Brobst
Subject: GEL Script Question
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
alan.brobst@ge.com
Original Message:
Sent: 06-07-2019 12:29 PM
From: Arunachalam Angusamy
Subject: GEL Script Question
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.