Home » SQL & PL/SQL » SQL & PL/SQL » Problem writing a sql query for a select list item in a form
Problem writing a sql query for a select list item in a form [message #294392] Thu, 17 January 2008 07:46 Go to next message
oraclechick
Messages: 33
Registered: February 2006
Member
Hi,

I have the following table...

VALIDATIONS
ID Number (PK)
APP_ID Number
REQUESTED Date
APPROVED Date
VALID_TIL Date
DEPT_ID Number (FK)


In my application, I have a search form with the following field item variables...

DEPTID (select list based on values from depts table)
VALID (Yes/No select list)


A sql report on the columns of the Validations table is shown based on the values that the user fills into the search form. So far, my sql query for the report is...

SELECT v.APP_ID,
             v.REQUESTED,
             v.APPROVED,
             v.VALID_TIL,
             d.DEPT
FROM DEPTS d, VALIDATIONS v
WHERE d.DEPT_ID = v.DEPT_ID(+)
AND (d.DEPT_ID = DEPTID OR DEPTID = -1)


This query works so far. My problem is that I don't know how to do a search based on the VALID item - if 'yes' is selected, then the VALID_TIL date is still valid. If 'no' is selected then the VALID_TIL date has passed.
Can anyone help me to extend my query to include this situation?
Thanks.
Re: Problem writing a sql query for a select list item in a form [message #294402 is a reply to message #294392] Thu, 17 January 2008 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
One thing I don't understand: why do you need to join with depts as you already did it to get all deptid, so why don't you get the dept value at the same time?

For your question, it could be:
v.valid_til >= decode(valid,'YES',sysdate,v.valid_til)


In addition, it would be better to have 2 queries in your code and choose a query or the another one depending on deptid is -1 or not.
And this is also valid for the solution I gave above.

Regards
Michel
Re: Problem writing a sql query for a select list item in a form [message #294411 is a reply to message #294402] Thu, 17 January 2008 08:44 Go to previous messageGo to next message
oraclechick
Messages: 33
Registered: February 2006
Member
Hi,
Thanks for your reply.

The following statement, shows all the rows in the table and not only the ones that are valid...

v.valid_til >= decode(valid,'YES',sysdate,v.valid_til)

Any other suggestions?
Re: Problem writing a sql query for a select list item in a form [message #294420 is a reply to message #294411] Thu, 17 January 2008 09:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What is the exact value of your valid variable? Is it "YES" or is it "yes" ?

Adjust the decode to that.
Re: Problem writing a sql query for a select list item in a form [message #294421 is a reply to message #294411] Thu, 17 January 2008 09:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe all the rows are valid, in the meaning I understood: are valid the rows for which valid_til is greater or equal to now.
Or maybe the value for valid is not 'YES' as I also assumed, maybe it is 'Yes' or 'Y'.

Regards
Michel
Re: Problem writing a sql query for a select list item in a form [message #294646 is a reply to message #294421] Fri, 18 January 2008 05:14 Go to previous messageGo to next message
oraclechick
Messages: 33
Registered: February 2006
Member
Hi,

These are the values of my select list...

Display Value Return Value
------------------------------
-Select Value- NULL
Yes Y
No N

At the moment, I have only 4 rows in my Validations table, with the following VALID_TIL dates...

14-JAN-09
15-JAN-08
30-DEC-08
NULL

I have now got the code to work when Yes is selected (the result shows the correct 2 rows: 14-JAN-09 & 30-DEC-08), but I can't get it to work when No or -Select Value- is chosen.
When No is chosen, the result should have only 1 row (15-JAN-08) and when -Select Value- is chosen, the result should show all the rows in the table. However, my code shows the first 3 rows (14-JAN-09, 15-JAN-08, 30-DEC-08) when either No or -Select Value- is chosen. It only works for Yes. This is the extension to my code....

AND    (
       v.valid_til >= decode(valid,'Y',trunc(sysdate),v.valid_til) OR
       v.valid_til < decode(valid,'N',trunc(sysdate),v.valid_til) OR
       valid IS NULL
       )


What am I doing wrong??
Re: Problem writing a sql query for a select list item in a form [message #294666 is a reply to message #294646] Fri, 18 January 2008 06:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If I understand:
- if valid = 'Y' you must return rows valid_til >= now
- if valid = 'N' you must return rows valid_til < now
- if valid is null you must return all rows

If you want just a formula then use:
case 
when valid = 'Y' and valid_til >= sysdate then 'Y'
when valid = 'N' and valid_til < sysdate then 'Y'
when valid is null then 'Y'
else 'invalid input: do what you want'
end = 'Y'

Regards
Michel

Re: Problem writing a sql query for a select list item in a form [message #294681 is a reply to message #294666] Fri, 18 January 2008 07:39 Go to previous messageGo to next message
oraclechick
Messages: 33
Registered: February 2006
Member
Hi,

Could you please explain that code? Why is it always "Then 'Y'"?

The code works when either Yes or No is selected, but when
-Select Value- (null) is selected, it doesn't return all the rows. Instead I get the message, "no data found".

However, something else is also going wrong. As I mentioned before, my search form has 2 fields...

DEPTID (select list based on values from depts table)
VALID (Yes/No select list)

The user can choose to search on either the dept name or if date is valid (yes or no) or both.
With this code, the search on only the dept name returns "no data found" eventhough data exists.

This is part of the data in my table...

Valid_til     Dept_ID
----------    --------
14-JAN-09        1
15-JAN-08        2
30-DEC-08        2
-                2

And my changed code is....

SELECT v.APP_ID,
             v.REQUESTED,
             v.APPROVED,
             v.VALID_TIL,
             d.DEPT
FROM DEPTS d, VALIDATIONS v
WHERE d.DEPT_ID = v.DEPT_ID(+)
AND (d.DEPT_ID = DEPTID OR DEPTID = -1)
AND (case 
when valid = 'Y' and valid_til >= sysdate then 'Y'
when valid = 'N' and valid_til < sysdate then 'Y'
when valid is null then 'Y'
else 'invalid input: do what you want'
end = 'Y')


[Updated on: Fri, 18 January 2008 07:40]

Report message to a moderator

Re: Problem writing a sql query for a select list item in a form [message #294688 is a reply to message #294681] Fri, 18 January 2008 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The code works when either Yes or No is selected, but when
-Select Value- (null) is selected, it doesn't return all the rows. Instead I get the message, "no data found".

This is because your variable is not null but contains something, maybe a blank or the like.

Quote:
Could you please explain that code? Why is it always "Then 'Y'"?

I made a mistake it should be:
case 
when valid = 'Y' and valid_til >= sysdate then 'Y'
when valid = 'N' and valid_til < sysdate then 'Y'
when valid is null then 'Y'
else 'N' -- invalid input or not selected row
end = 'Y'

I check all valid cases and put 'Y' in these cases and (now) 'N' if it is not a valid row. So I test if the "case" return Y. I could choose any values for instance 'Valid' and 'Not Valid':
case 
when valid = 'Y' and valid_til >= sysdate then 'Valid'
when valid = 'N' and valid_til < sysdate then 'Valid'
when valid is null then 'Valid'
else 'Not Valid'
end = 'Valid'

Regards
Michel
Re: Problem writing a sql query for a select list item in a form [message #294715 is a reply to message #294688] Fri, 18 January 2008 10:18 Go to previous message
oraclechick
Messages: 33
Registered: February 2006
Member
Hi,

I now know why it wasn't working for the null value. The NULL value was being treated as a string, so when i change the code to as below, everything works perfectly!

when valid = 'NULL' then 'Y'

Now, I have to add another YES/NO select list to my search form and thus to my search query....hopefully I can get it to work!

Thanks a lot for all your help!
Previous Topic: delete A QUEUE TABLE HOW TO ?
Next Topic: reading a file into the database
Goto Forum:
  


Current Time: Sat Dec 10 14:40:12 CST 2016

Total time taken to generate the page: 0.08530 seconds