Clarity

Expand all | Collapse all

GEL Script Question

Jump to Best Answer
  • 1.  GEL Script Question

    Posted 06-07-2019 12:34 PM
    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.


  • 2.  RE: GEL Script Question
    Best Answer

    Posted 06-07-2019 03:51 PM
    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

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



  • 3.  RE: GEL Script Question

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


  • 4.  RE: GEL Script Question

    Posted 06-18-2019 07:51 AM
    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


  • 5.  RE: GEL Script Question

    Posted 06-18-2019 09:47 AM
    After implementing NVL, it is working as expected. Thank you everyone