| Query to get the result from a search form [message #290994] |
Wed, 02 January 2008 10:10  |
oraclechick
Messages: 33
Registered: February 2006
|
Member |
|
|
Hi,
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.
|
|
|
|
|
|
|
|
| Re: Query to get the result from a search form [message #291156 is a reply to message #291019] |
Thu, 03 January 2008 03:28  |
oraclechick
Messages: 33
Registered: February 2006
|
Member |
|
|
Hi,
I tried what you suggested.
When I change OR to AND, I get this error...
report error:
ORA-01722: invalid number
But i only get this error for the first name &/or last name (which are text fields)
If i only fill in the dept id variable (which is from a select list), I get the correct result.
Any idea what is going wrong?
|
|
|
|
|
|
|
|
|
|
| Re: Query to get the result from a search form [message #291204 is a reply to message #291191] |
Thu, 03 January 2008 06:42 |
oraclechick
Messages: 33
Registered: February 2006
|
Member |
|
|
What do you mean by sample sets? - the data from my table??
This is my latest code....
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 e.DEPT_ID = d.DEPT_ID
AND (
UPPER(e.FIRST_NAME) LIKE UPPER('%'||:P2_FIRST_NAME||'%')
AND UPPER(e.LAST_NAME) LIKE UPPER('%'||:P2_LAST_NAME||'%')
AND (d.DEPT_ID = TO_NUMBER(:P2_DEPT_ID) OR :P2_DEPT_ID IS NULL)
)
I only get the error when I do a search on....
first name, or
last name, or
first name & last name (only when both values are correct. if one value doesn't match with the other, as it is in the table, then i get the message 'no data found', which is good)
The error does NOT occur when I do a search on....
dept, or
first name & dept, or
last name & dept, or
first name & last name & dept
|
|
|
|
|
|
| Re: Query to get the result from a search form [message #291278 is a reply to message #291219] |
Thu, 03 January 2008 10:47 |
oraclechick
Messages: 33
Registered: February 2006
|
Member |
|
|
The problem seems to be solved. I changed NULL to 0, and changed my code to this....
AND (d.DEPT_ID = :P2_DEPT_ID OR :P2_DEPT_ID = 0)
This way, it seems to be working fine, but I'm not sure if it's a good solution to the problem.
Especially since the result from the search has a link for each row to edit the values. Clicking the edit link for a employee, will show another form with the values of that employee populated in the fields. There I have to change NULL to 0 also, otherwise I get a ORA-20001: Invalid numeric value NULL for column error.
I'm afraid that this might lead to other problems. If you know another way to do this so that I can stick to using NULL, I'd appreciate hearing from you.
Do you still need to have the select list picture?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
