Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Query to get the result from a search form
Query to get the result from a search form [message #290994] Wed, 02 January 2008 10:10 Go to next message
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 #291018 is a reply to message #290994] Wed, 02 January 2008 13:13 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:

it shows all the rows in the table no matter what is filled into the form.
Provide some sample sets of parameters for which it is not working.

By
Vamsi

[Updated on: Wed, 02 January 2008 13:13]

Report message to a moderator

Re: Query to get the result from a search form [message #291019 is a reply to message #290994] Wed, 02 January 2008 13:38 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
'OR :parameter IS NULL' doesn't make much sense - you can live without it as it will be true whenever you don't enter value for this parameter.

Concatenating a parameter value with a wildcard in front of the parameter and behind it will always return all records when parameter value is NULL.

Also, you might consider changing OR to AND in order to further restrict fetched record set.

Try with something like this; it probably isn't perfect, but *might* bring some improvement:
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||'%')
         AND 
         UPPER(e.LAST_NAME)  LIKE UPPER('%'||:P2_LAST_NAME ||'%')
         AND
         d.DEPT_ID = :P2_DEPT_ID
       )
Re: Query to get the result from a search form [message #291156 is a reply to message #291019] Thu, 03 January 2008 03:28 Go to previous messageGo to next message
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 #291166 is a reply to message #291156] Thu, 03 January 2008 04:11 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Try to put to_number
Quote:

d.DEPT_ID = TO_NUMBER(:P2_DEPT_ID)
Edit: I think better to have is null for p_dept_id
Quote:

AND (d.DEPT_ID = to_number(:P2_DEPT_ID)
OR :P2_DEPT_ID IS NULL)
By
Vamsi

[Updated on: Thu, 03 January 2008 04:14]

Report message to a moderator

Re: Query to get the result from a search form [message #291168 is a reply to message #291166] Thu, 03 January 2008 04:26 Go to previous messageGo to next message
oraclechick
Messages: 33
Registered: February 2006
Member
Hi,

I tried that, but i still get the same error. Sad
Re: Query to get the result from a search form [message #291191 is a reply to message #291168] Thu, 03 January 2008 05:56 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:

Provide some sample sets of parameters for which it is not working.
Till now you didn't provide anything.......
Post your latest code.

By
Vamsi
Re: Query to get the result from a search form [message #291204 is a reply to message #291191] Thu, 03 January 2008 06:42 Go to previous messageGo to next message
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 #291219 is a reply to message #291204] Thu, 03 January 2008 07:35 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:

:P2_DEPT_ID is a variable for a select list
I suspect on this item. Are you passing something (like white space, etc)when it is null?
Paste the property palette of the select list (at least General and Functional and make it as small as you can and save it as jpg not as bmp).

By
Vamsi
Re: Query to get the result from a search form [message #291278 is a reply to message #291219] Thu, 03 January 2008 10:47 Go to previous messageGo to next message
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?
Re: Query to get the result from a search form [message #291289 is a reply to message #291278] Thu, 03 January 2008 12:25 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Try
Quote:

d.DEPT_ID = nvl(:P2_DEPT_ID,d.DEPT_ID)
instead.

By
Vamsi
Previous Topic: Check Box with Column names
Next Topic: FireFox 2.0 is not compatible with OWS4.0.8
Goto Forum:
  


Current Time: Mon Dec 05 12:39:20 CST 2016

Total time taken to generate the page: 0.09750 seconds