Clarity

  • 1.  Last 24 Hours Gel Script on sql server

    Posted Nov 26, 2018 02:11 PM

    I am writing a gel script to look at the audit tables to do an insert based on updates made in the last 24 hours.  I am able to create the query and test it on SQL Server but when I put it into GEL the process fails.  I have tried multiple different date methods and they all work on SQL Server when I directly query the DB but the do not work in a gel script If i can get any assistance I would really appreciate it. 

    VERSION 1

    Select
    srm.FULL_NAME,
    srm.UNIQUE_NAME,
    srm.id,
    cmn.ATTRIBUTE_CODE,
    CASE WHEN cmn.ATTRIBUTE_CODE='manager_id' THEN 'nis_mgr_change'
    WHEN cmn.ATTRIBUTE_CODE='prprimaryroleid' THEN 'nis_role'
    ELSE 'N/A' END Resource_history_type,
    cmn.VALUE_BEFORE,
    cmn.RAW_VALUE_BEFORE,
    cmn.VALUE_AFTER,
    cmn.RAW_VALUE_AFTER,
    cmn.CREATED_DATE
    from CMN_AUDITS cmn
    join srm_resources srm on srm.id=cmn.OBJECT_ID
    where cmn.object_code='resource'
    and cmn.OPERATION_CODE='U'
    and cmn.CREATED_DATE >= DATEADD(day, -1, GETDATE())

     

    VERSION 2

    Select
    srm.FULL_NAME,
    srm.UNIQUE_NAME,
    srm.id,
    cmn.ATTRIBUTE_CODE,
    CASE WHEN cmn.ATTRIBUTE_CODE='manager_id' THEN 'nis_mgr_change'
    WHEN cmn.ATTRIBUTE_CODE='prprimaryroleid' THEN 'nis_role'
    ELSE 'N/A' END Resource_history_type,
    cmn.VALUE_BEFORE,
    cmn.RAW_VALUE_BEFORE,
    cmn.VALUE_AFTER,
    cmn.RAW_VALUE_AFTER,
    cmn.CREATED_DATE
    from niku.CMN_AUDITS cmn
    join niku.srm_resources srm on srm.id=cmn.OBJECT_ID
    where cmn.object_code='resource'
    and cmn.OPERATION_CODE='U'
    and cmn.CREATED_DATE >= CURRENT_TIMESTAMP -1



  • 2.  Re: Last 24 Hours Gel Script on sql server

    Posted Nov 27, 2018 01:43 AM

    Hi,

     

    What error message does the process fail with?



  • 3.  Re: Last 24 Hours Gel Script on sql server

    Posted Nov 27, 2018 03:06 AM

    Yes, your write

    GEL the process fails

    How does it fail?

    My understanding is that the SQL date format does not work in gel.

    See

    How to convert to Date format in Gel script 

    and may be
    https://communities.ca.com/message/241971171-re-how-to-format-date-from-query-to-gel-script-for-xoging?commentID=2419711… 



  • 4.  Re: Last 24 Hours Gel Script on sql server

    Posted Nov 27, 2018 03:47 AM

    The problem may not even be "date format", but rather the >= since '>' is part of XML, then this causes problems when trying to drop SQL into GEL.

     

    But the other answers are correct too; you need to tell us exactly what "but when I put it into GEL the process fails" means for us to be able to give specific help.

     

    Typically you should be coding GEL scripts with error handing / trapping so that any error raised in the script is handled and reported out as an error (so you can fix it  ) - for example if there were a SQL syntax error, this would be reported to you and would immediately tell you which but of code is "wrong".



  • 5.  Re: Last 24 Hours Gel Script on sql server



  • 6.  Re: Last 24 Hours Gel Script on sql server
    Best Answer

    Posted Nov 27, 2018 02:27 PM

    The issue with my Gel Script failing was that the query wouldn't return records so there wasn't a xml build to xog in.  

     

    I have fixed the issue by using:

    <sql:query escapeText="0" var="result">

    <![CDATA[

    sql query

    ]]>