Query to get the result from a search form
604983Jan 2 2008 — edited Jan 4 2008Hi,
I'm building an application using Oracle Application Express. In my application, I have a form which is used to search for employees - depending on the value(s) that the user fills into the form, the result is shown. I'm trying to write a query for this - this is my query so far...
SELECT e.EMP_ID,
e.FIRST_NAME||' '||e.LAST_NAME NAME,
e.ADDRESS||', '||e.POSTCODE||' '||e.CITY ADDRESS,
d.DEPT DEPT
FROM EMP e, DEPT d
WHERE d.DEPT_ID = e.DEPT_ID
AND (
(UPPER(e.FIRST_NAME) LIKE UPPER('%'||:P2_FIRST_NAME||'%') OR :P2_FIRST_NAME IS NULL)
OR (UPPER(e.LAST_NAME) LIKE UPPER('%'||:P2_LAST_NAME||'%') OR :P2_LAST_NAME IS NULL)
OR (d.DEPT_ID = :P2_DEPT_ID OR :P2_DEPT_ID = -1 OR :P2_DEPT_ID IS NULL)
)
:P2_FIRST_NAME & :P2_LAST_NAME are variables for a text field and :P2_DEPT_ID is a variable for a select list.
This query doesn't work however - it shows all the rows in the table no matter what is filled into the form.
Does anyone have an example of a query for this type of situation or can anyone help me out with this query?
Thanks.