I´m building an application (V4.2) in wich customer introduce a date (item P11_F_ACTIVIDAD, Display as Date Picker). I need to sustract the month from that field and use it in a SQL query to fetch all rows from a table that met that condition (column F_ACTIVIDAD) . The WHERE clause should has something like this: WHERE substr(to_char(h.F_ACTIVIDAD,'ddmmyyyy'),3,2)= substr(to_char(:P11_F_ACTIVIDAD,'ddmmyyyy'),3,2) but SQL engine does not see ":P11_F_ACTIVIDAD" like a column name..... Of course, it is not a column name.
I appreciate recomend me an idea to overcome this issue.
Thanks so much!
In APEX, items can certainly be used as bound variables in a SQL statement; like this.
SELECT emp.ename, emp.hiredate, EXTRACT (MONTH FROM emp.hiredate) hiredate_month, EXTRACT (MONTH FROM :p11_f_actividad) datepicker_month FROM emp WHERE SUBSTR (TO_CHAR (emp.hiredate, 'ddmmyyyy'), 3, 2) = SUBSTR (TO_CHAR ( :p11_f_actividad, 'ddmmyyyy'), 3, 2)
The :P11_F_ACTIVIDAD are treated as VARCHAR2 data types.
EMP.HIREDATE and H.ACTIVIDAD are DATE data types.
You should be comparing dates to dates.
You should never compare strings-that-look-like-dates to other-strings-that-look-like-dates and believe that you'll get the right answer.
If you want all the rows that are in the same month and year as the date picker
WHERE trunc( H.ACTIVEDAD, 'MON' ) -- truncates the date to 1st day of the month and keeps it as a DATE = trunc( to_date( :P11_F_ACTIVIDAD, 'DDMMYYYY') -- always explicitly convert strings to dates ,'MON' )
The trunc() on the column will force a full-table-scan (ie slow=true) unless you have a function based index on that column.