Clarity

  • 1.  Using >= or <= in query of GEL

    Posted Nov 21, 2012 08:18 AM
    Hello,
    I need to use relational operators in query of my GEL script. I need to use exactly >= or <=. I tried to use escapeText="true" and escapeText="false". But nothing is working, I also tried &gt; and &lt;

    Have anybody got idea how to solve it?

    Here is my query (using custom object tables).
    select code, 'PSR', name
    from inv_investments inv
    left join odf_ca_inv oin on oin.id=inv.id
    left join odf_ca_project oip on oip.id=inv.id
    where inv.odf_object_code='project'
    and oin.i_inv_categ='project'
    and oip.last_status_rep_date is not null
    and oip.partition_code = 'STANDARD'
    and stage_code in ('DDRE','FS')
    and ((to_date(sysdate)-to_date(oip.last_status_rep_date))
    -FLOOR ((to_date(sysdate)-to_date(oip.last_status_rep_date))/7)*2
    -(SELECT Count(ID) FROM odf_ca_i_svatky  WHERE i_datum BETWEEN oip.last_status_rep_date AND sysdate)
    -DECODE(SIGN(TO_CHAR(sysdate,'D')- TO_CHAR(oip.last_status_rep_date,'D')),-1,2,0)
    +(CASE WHEN to_char(oip.last_status_rep_date,'D')=7 then 1 else 0 End)
    -(CASE WHEN to_char(sysdate,'D')=7 then 1 else 0 End)) >= 3
    Whole script attached

    Thanks,
    Martin Snizek


  • 2.  RE: Using >= or <= in query of GEL

    Posted Nov 21, 2012 08:30 AM
    I always use CDATA, then you don't have to worry about &gt and the like, just write your query normally
    <sql:query dataSource="${clarityDS}" escapeText="False" var ="demo">
    <![CDATA[   Select top 10 * from prtask where practsum > 3600
    ]]>
    
    </sql:query>


  • 3.  RE: Using >= or <= in query of GEL

    Posted Nov 21, 2012 08:31 AM
    With escapeText="true" and using &lt; in place of < should have been sufficient I believe.

    Whilst I don't usually recommend it as a regular practice, for a 'plain alphabet' query (no accented letters or characters among the SQL) you could also wrap the query text in a character-data block and that should also work:

    E.g. from this:
        <sql:query var="projects">
    select code, 'PSR', name
    ...
    -(CASE WHEN to_char(sysdate,'D')=7 then 1 else 0 End)) >= 3
    
    </sql:query>
    To this:
        <sql:query var="projects">
    <![CDATA[select code, 'PSR', name
    ...
    -(CASE WHEN to_char(sysdate,'D')=7 then 1 else 0 End)) >= 3]]>
    
    </sql:query>


  • 4.  RE: Using >= or <= in query of GEL

    Posted Nov 21, 2012 09:05 AM
    Hello, thanks for quick reply, but it didn't helped me.

    My query modified according to your advice:
    <sql:query var="projects">
    <![CDATA[select code, 'PSR', name
    from inv_investments inv
    left join odf_ca_inv oin on oin.id=inv.id
    left join odf_ca_project oip on oip.id=inv.id
    where inv.odf_object_code='project'
    and oin.i_inv_categ='project'
    and oip.last_status_rep_date is not null
    and oip.partition_code = 'STANDARD'
    and stage_code in ('DDRE','FS')
    and ((to_date(sysdate)-to_date(oip.last_status_rep_date))
    -FLOOR ((to_date(sysdate)-to_date(oip.last_status_rep_date))/7)*2
    -(SELECT Count(ID) FROM odf_ca_i_svatky  WHERE i_datum BETWEEN oip.last_status_rep_date AND sysdate)
    -DECODE(SIGN(TO_CHAR(sysdate,'D')- TO_CHAR(oip.last_status_rep_date,'D')),-1,2,0)
    +(CASE WHEN to_char(oip.last_status_rep_date,'D')=7 then 1 else 0 End)
    -(CASE WHEN to_char(sysdate,'D')=7 then 1 else 0 End)) <= 3]]>
    
    </sql:query>
    I get this error in bg-niku.log
    Using input: 
    {process_instance_id=5104016, step_condition_id=-1, process_version_id=-1, step_instance_id=5195034, step_action_result_id=5147021, step_action_id=5031095, step_id=-1, attribute1=null, attribute2=null, message_code=bpm.CUSTOM_SCRIPT_EXECUTION_ERROR, exception_trace2=org.apache.commons.jelly.JellyTagException: null:19:31: <sql:query> select code, 'PSR', name
    from inv_investments inv
    left join odf_ca_inv oin on oin.id=inv.id
    left join odf_ca_project oip on oip.id=inv.id
    where inv.odf_object_code='project'
    and oin.i_inv_categ='project'
    and oip.last_status_rep_date is not null
    and oip.partition_code = 'STANDARD'
    and stage_code in ('DDRE','FS')
    and ((to_date(sysdate)-to_date(oip.last_status_rep_date))
    -FLOOR ((to_date(sysdate)-to_date(oip.last_status_rep_date))/7)*2
    -(SELECT Count(ID) FROM odf_ca_i_svatky  WHERE i_datum BETWEEN oip.last_status_rep_date AND sysdate)
    -DECODE(SIGN(TO_CHAR(sysdate,'D')- TO_CHAR(oip.last_status_rep_date,'D')),-1,2,0)
    +(CASE WHEN to_char(oip.last_status_rep_date,'D')=7 then 1 else 0 End)
    -(CASE WHEN to_char(sysdate,'D')=7 then 1 else 0 End)) &lt;= 3: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00920


  • 5.  RE: Using >= or <= in query of GEL

    Posted Nov 21, 2012 09:17 AM
    Which is the same error you were getting previously?


  • 6.  RE: Using >= or <= in query of GEL

    Posted Nov 21, 2012 09:24 AM
    OK - you have combined the 2 proposed solutions - you are trying to use &lt; as well as CDATA, you only need one of those.

    EDIT : Ignore me I was looking at the error trace rather than the SQL statement. Oops. :*)


  • 7.  RE: Using >= or <= in query of GEL

    Posted Nov 21, 2012 09:26 AM

    nick_darlington wrote:

    Which is the same error you were getting previously?
    Yes, it's the same error all the time.


  • 8.  RE: Using >= or <= in query of GEL

    Posted Nov 21, 2012 09:51 AM
    You may need to restart the bg services then, the process instance already in use / in memory appears to be retaining knowledge of the earlier script and not noting the changes you are making.

    If you may some other alteration to the query, and the SQL in the error message doesn't change accordingly to show that alteration, that would show this to be the case too.


  • 9.  RE: Using >= or <= in query of GEL

    Posted Nov 21, 2012 09:53 AM
    It's resolved. I used post by Owen_R (thanks).


  • 10.  RE: Using >= or <= in query of GEL

    Posted Nov 22, 2012 06:00 AM
    Credit to Nick too though, he suggested the same thing at the same time I did!


  • 11.  RE: Using >= or <= in query of GEL

    Posted Nov 22, 2012 06:05 AM
    But, there was important to use escapeText="False" in <sql:query ... > in combination with CDATA