Using a new value from an item to my sql statement without saving this value to database

Question

I'm trying to compare a date value I just entered in my item (:P2007_Session_Date -it is a date picker-) with date values from my table. This new value has not yet been saved in my database. How can i do that? I am trying with DA but nothing happens

When:

Event: Change, item: P2007_Session_date

True Action Set Value:

SQL Statement

SELECT POLICIES.PRICE FROM POLICIES WHERE POLICIES.ACTIVE = 1 AND to_char(**:P2007_Session_Date**, 'YYYY-MM-DD') BETWEEN to_char(POLICIES.START_DATE, 'YYYY-MM-DD') AND to_char(POLICIES.END_DATE, 'YYYY-MM-DD');

Items to Submit

:P2007_Session_Date, :P2007_Price

Affected Elements

:P2007_Price

This gives me this error Ajax call returned server error ORA-01722: not acceptable number for Set Value. but when i change my query from to_char(:P2007_Session_Date, 'YYYY-MM-DD') to to_char(sysdate, 'YYYY-MM-DD') it works! The problem is that i want to check not with sysdate but with the value that user just enter to :P2007_Session_Date item and it is not yet stored in the database.

Answer 1

Apex items are strings, no matter what datatype they represent. So if you have a date value in a page item, you'll have to convert it to a date in your pl/sql. The statement TO_CHAR(:P2007_SESSION_DATE, 'YYYY-MM-DD') is not correct, it expects the bind variable :P2007_SESSION_DATE to be of datatype DATE or TIMESTAMP, but since this is an apex item, it is just a string.

Cleanest solution is to just do a date comparison, not a character comparison (which would work in your case).

Give this statement a try instead:

SELECT
  policies.price
  FROM
  policies
 WHERE
  policies.active = 1 AND
  TO_DATE (:P2007_SESSION_DATE,'YYYY-MM-DD' ) BETWEEN policies.start_date AND policies.end_date