Home » SQL & PL/SQL » SQL & PL/SQL » how to use Case statement in where clause (with respect to Input Params &) (merged)
how to use Case statement in where clause (with respect to Input Params &) (merged) [message #413403] Wed, 15 July 2009 09:56 Go to next message
aswani7
Messages: 14
Registered: July 2009
Junior Member
Select * FROM hsi.ChartQueueTxLog cq, hsi.Chart c
WHERE c.FacilityNum in
(CASE WHEN &Facility = 0 THEN 101,102
ELSE &Facility
END)
AND cq.ChtNum = c.ChtNum


here what i am doing is if the user enters input value as 0 then it take both 101 and 102 so the result will be as the below query

Select * FROM hsi.ChartQueueTxLog cq, hsi.Chart c
WHERE c.FacilityNum in (101,102)
AND cq.ChtNum = c.ChtNum


if user enter other than 0 then the query where clause will made up with user entered valued

example: (if user entered value as 210 (other than 0) then it should look like as below

Select * FROM hsi.ChartQueueTxLog cq, hsi.Chart c
WHERE c.FacilityNum in (210)
AND cq.ChtNum = c.ChtNum



Please help me sir,

Best Regards
asita
how to use Case statement in where clause (with respect to Input Params &) [message #413404 is a reply to message #413403] Wed, 15 July 2009 09:58 Go to previous messageGo to next message
aswani7
Messages: 14
Registered: July 2009
Junior Member
Hello All,

i am getting error with below statement

Select * FROM hsi.ChartQueueTxLog cq, hsi.Chart c
WHERE c.FacilityNum in
(CASE WHEN &Facility = 0 THEN 101,102
ELSE &Facility
END)
AND cq.ChtNum = c.ChtNum



Error at Command Line:6 Column:2
Error report:
SQL Error: ORA-00905: missing keyword
00905. 00000 - "missing keyword"
*Cause:
*Action:




here what i am doing is if the user enters input value as 0 then it take both 101 and 102 so the result will be as the below query

Select * FROM hsi.ChartQueueTxLog cq, hsi.Chart c
WHERE c.FacilityNum in (101,102)
AND cq.ChtNum = c.ChtNum


if user enter other than 0 then the query where clause will made up with user entered valued

example: (if user entered value as 210 (other than 0) then it should look like as below

Select * FROM hsi.ChartQueueTxLog cq, hsi.Chart c
WHERE c.FacilityNum in (210)
AND cq.ChtNum = c.ChtNum



Please help me sir,

Best Regards
asita
Re: how to use Case statement in where clause (with respect to Input Params &) [message #413405 is a reply to message #413404] Wed, 15 July 2009 10:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a FAQ, search for "varying inlist".

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: how to use Case statement in where clause (with respect to Input Params &) [message #413407 is a reply to message #413405] Wed, 15 July 2009 10:12 Go to previous messageGo to next message
aswani7
Messages: 14
Registered: July 2009
Junior Member
Hello Michael Sir,

thanks for your information about format post , i will go through it and surely apply in future posts

can you please leave me for this post


Best regards
asita
Re: how to use Case statement in where clause (with respect to Input Params &) [message #413410 is a reply to message #413405] Wed, 15 July 2009 10:34 Go to previous messageGo to next message
aswani7
Messages: 14
Registered: July 2009
Junior Member
i searched for "varying inlist" it displays all the posts like me, but it didn't showed me the actual information


Could you please give me the link

thanks in advance
asita
Re: how to use Case statement in where clause (with respect to Input Params &) [message #413416 is a reply to message #413410] Wed, 15 July 2009 11:19 Go to previous messageGo to next message
aswani7
Messages: 14
Registered: July 2009
Junior Member
Hello All,


Can any one please help me


Thanks in advance
asita

Re: how to use Case statement in where clause (with respect to Input Params &) [message #413426 is a reply to message #413416] Wed, 15 July 2009 11:39 Go to previous message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Be patient, we don't live just to help you.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061

Regards
Michel

[Updated on: Wed, 15 July 2009 11:40]

Report message to a moderator

Previous Topic: Connect By NoCycle not returning all rows
Next Topic: Getting this issue
Goto Forum:
  


Current Time: Mon Dec 05 10:44:54 CST 2016

Total time taken to generate the page: 0.05991 seconds