Search code examples
javasqloracle-databaseibatis

ORA-00920: | unabale to set INTERVAL via ibatis

Ok, so after researching a lot still I couldn't find the answer

Below is the SQL query that I am trying to execute through ibatis

<select id="selectSQL" parameterClass="String" resultMap="get-resultMap">   
     SELECT * FROM MY_TABLE WHERE SYSDATE - INTERVAL #value#  DAY > LAST_UPD_DTIM
 </select>

This throws an error

--- Cause: java.sql.SQLSyntaxErrorException: ORA-00920: invalid relational operator

but when I hard code the interval value it works fine

SELECT * FROM MY_TABLE WHERE SYSDATE - INTERVAL '90'  DAY > LAST_UPD_DTIM

What is wrong here ?

Solution

  • Ok, So finally I found the issue. The issue here was the expression 

    INTERVAL -'90' DAY

    is a literal, so we cannot replace a part of it with a variable (parameter)

    To fix this I used function NUMTODSINTERVAL

    <select id="selectSQL" parameterClass="String" resultMap="get-resultMap">   
   SELECT * FROM MY_TABLE WHERE SYSDATE - NUMTODSINTERVAL(#value#, 'DAY') > LAST_UPD_DTIM
 </select>