Home » SQL & PL/SQL » SQL & PL/SQL » Can we build condition using CASE in WHERE Clause ?
Can we build condition using CASE in WHERE Clause ? [message #205265] Fri, 24 November 2006 01:48 Go to next message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member
Dear all,

i would like to know here can we build condition dynamically
using CASE in where Clause of SQL query.


SQL> select * from TEST;

      NUM1 NUM2
---------- ----------
        10 100
        20 100
        30 100
        10 100

Query 1: 

SELECT * 
FROM   TEST
WHERE   1= (CASE NUM1
               WHEN 10
                  THEN 1
               when null 
               then 2
            END);

     NUM1 NUM2
--------- ----------
       10 100
       10 100

Query 1 working fine.

I am trying to run the query 2 shown below 
where i am using bind variable :v_NUM1.
But its not working, is there anyother way 
i can solve this by SQL query.

Query 2: 

SELECT * 
FROM   TEST
WHERE    (CASE NUM1
               WHEN 10
                  THEN NUM1=:v_NUM1
               when null 
               then  1=1
            END);   

Note: here i want if :V_NUM1 is 10 then 
its should show only 2 records from the 
table and if its null then its should show 
all records from the table.



Regards
Pravin
Re: Can we build condition using CASE in WHERE Clause ? [message #205281 is a reply to message #205265] Fri, 24 November 2006 02:52 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
try this.
SELECT * 
FROM   TEST
WHERE  NUM1 =   (CASE 
                 WHEN to_number(:v_NUM1) = 10
                 THEN 10
                 when to_number(:v_NUM1) is null  -- i missed it. i had put =
                 then  num1
                 END);

By
Vamsi

[Updated on: Fri, 24 November 2006 03:53]

Report message to a moderator

Re: Can we build condition using CASE in WHERE Clause ? [message #205297 is a reply to message #205265] Fri, 24 November 2006 03:29 Go to previous messageGo to next message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member
hi vamsi
i checked ur query but it doesnt work.may be you have not tested it there as its giving error.
" ORA-00904: "NUM1": invalid identifier "

anyother solution ??


regrds
pravin



Re: Can we build condition using CASE in WHERE Clause ? [message #205303 is a reply to message #205297] Fri, 24 November 2006 03:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That query works just fine for me.
Can you cut and paste your test.
Re: Can we build condition using CASE in WHERE Clause ? [message #205333 is a reply to message #205265] Fri, 24 November 2006 04:57 Go to previous messageGo to next message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member
hi
vamsi solution works fine now.
Sorry I cant cut n paste my TEST here for security reason !!



Re: Can we build condition using CASE in WHERE Clause ? [message #205345 is a reply to message #205333] Fri, 24 November 2006 05:35 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Security? What security? Is bunch of numbers from a TEST table confidential?
Re: Can we build condition using CASE in WHERE Clause ? [message #205350 is a reply to message #205333] Fri, 24 November 2006 05:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
But you already have cut and pasted it in your OP.

Unless what you mean is that you implemented this solution into another more complex query, got it wrong, and reported that back as an error in @vamsi kasina's soltion, which would be a bit impolite.
Re: Can we build condition using CASE in WHERE Clause ? [message #205363 is a reply to message #205281] Fri, 24 November 2006 06:09 Go to previous message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member
hi
i need ur help once again

SELECT * 
FROM   TEST
WHERE  NUM1 =   (CASE 
                 WHEN to_number(:v_NUM1) = 10
                 THEN 10
                 when to_number(:v_NUM1) is null  -- i missed it. i had put =
                 then  num1
                 END);

this working fine on Oracle database. 
I want to run the  same query on DB2 database,
Now the problem is i cant used bind variable like ':v_NUM1' 
Can anybody write me how can use bind varible 
to run the above query in db2 database.




[Updated on: Fri, 24 November 2006 06:10]

Report message to a moderator

Previous Topic: Jus read this code dealing with cursors and then answer
Next Topic: HELP ON validating CLOB and converting to XML
Goto Forum:
  


Current Time: Sat Dec 10 09:21:37 CST 2016

Total time taken to generate the page: 0.09829 seconds