I have Spring JPA.
I have a query like this in sql oracle:
SELECT * FROM MY_TABLE WHERE SYSDATE > MY_DATE + INTERVAL '10' DAY;
I have a CrudRepository in which I use the above query in native mode, like this:
@Query(nativeQuery = true, value = "SELECT * FROM MY_TABLE WHERE SYSDATE > MY_DATE + INTERVAL :myValue DAY") public List<Object[]> myMethod(@Param("myValue") String myValue );
I get ORA-00933:
Passing myValue as integer between quotes I get ORA-01867
Passing myValue as integer without quotes I get ORA-00933
How to do it?
Advertisement
Answer
See comments on question, there is a similar question/answer for spring/jpa/postgresql, solution for me is:
@Query(nativeQuery = true, value = "SELECT * FROM MY_TABLE WHERE SYSDATE > MY_DATE + :myValue * (INTERVAL 1 DAY")) public List<Object[]> myMethod(@Param("myValue") int myValue );