Home » SQL & PL/SQL » SQL & PL/SQL » doubt with case
doubt with case [message #384933] Thu, 05 February 2009 15:23 Go to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
Please advice if the following case stmt is correct

SELECT 'TRUE'
  (CASE 5 
    WHEN 5 THEN ii.r_id NOT LIKE '%F%' AND (oi.p) NOT LIKE 'C%'
    WHEN 6 THEN ii.r_id  LIKE '%F%' AND (oi.p) NOT LIKE 'C%'
     END ) FROM tbl_L li, tbl_I ii,tbl_o oi 
WHERE ii.i_c = li.i_c
AND li.LN = oi.LN
AND oi.ln = 45



i get 2 parameters from front end,

parameter 2- values are 5 or 6

parameter 1 - value is 45

i have to setup the following

if parameter is 5 -- exclude all rows with r_id like '%F%' and exclude rows like oi.p '%c%'
if parameter is 6 -- include all rows with r_id like '%F%' and exclude rows like oi.p '%c%'

in normal sql, the above 2 conditions would appear in where clause


so it works based on the parameter 2 i get, is the above select correct ? should i go for decode ?
Re: doubt with case [message #384964 is a reply to message #384933] Thu, 05 February 2009 22:55 Go to previous messageGo to next message
st33chen
Messages: 11
Registered: January 2009
Junior Member
HI,
PLEASE TRY IF THIS WORKS :
( i didn't find any boolean funcion in oracle's built-in functions, so i try the INSTR function to act as LIKE)
SELECT FLD1, FLD2, ...
  FROM tbl_L li, tbl_I ii,tbl_o oi 
 WHERE ii.i_c = li.i_c
   AND li.LN  = oi.LN
   AND oi.ln  = :PARAM2
   AND CASE WHEN :PARAM1<>5 THEN 0 ELSE INSTR(ii.r_id, 'F')+INSTR(oi.p,'C') END <=0
   AND CASE WHEN :PARAM1<>6 THEN 1 ELSE INSTR(ii.r_id, 'F') END >0
   AND CASE WHEN :PARAM1<>6 THEN 0 ELSE INSTR(oi.p, 'C') END <=0

MAYBE DECODE IS MORE CLEAR :
SELECT FLD1, FLD2, ...
  FROM tbl_L li, tbl_I ii,tbl_o oi 
 WHERE ii.i_c = li.i_c
   AND li.LN  = oi.LN
   AND oi.ln  = :PARAM2
   AND DECODE(:PARAM1, 5,0, INSTR(ii.r_id,'F')+INSTR(oi.p,'C'))<=0
   AND DECODE(:PARAM1, 6,1, INSTR(ii.r_id,'F'))>0
   AND DECODE(:PARAM1, 6,0, INSTR(oi.p,'C'))<=0

hth

[Updated on: Thu, 05 February 2009 22:56]

Report message to a moderator

Re: doubt with case [message #384980 is a reply to message #384933] Fri, 06 February 2009 00:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No idea what your parameter 1 has to do with this, nor how your 'C%' in your SQL relates to '%c%' in your description, but here's another option that shows how you can quite easily write SQL by taking your requirements and translate them one by one.

select *
from   tbl_L li
,      tbl_I ii
,      tbl_o oi 
where  ii.i_c = li.i_c
and    li.ln = oi.ln
and    oi.ln = 45
and    (  nvl(:param2, 0) not in (5, 6)
       or oi.p not like 'C%' 
       )
and    (  (   :param2 = 5
          and ii.r_id not like '%F%'
          )
       or (   :param2 = 6
          and ii.r_id like '%F%'
          )
       or nvl(:param2, 0) not in (5, 6)
       )
Re: doubt with case [message #384997 is a reply to message #384933] Fri, 06 February 2009 00:42 Go to previous messageGo to next message
st33chen
Messages: 11
Registered: January 2009
Junior Member
i'm sorry that i mixed up the :param1 and :param2 in my
previous reply.
and i'm sorry that i followed the thinking of using 'CASE' in sql statement that complicatified the solution.
i think, we can do a simpler work :

SELECT FLD1, FLD2, ...
  FROM tbl_L li, tbl_I ii,tbl_o oi 
 WHERE ii.i_c = li.i_c
   AND li.LN  = oi.LN
   AND oi.ln  = :PARAM1
   AND (
       :PARAM1 NOT IN (5,6)
       OR (:PARAM1=5 AND ii.r_id NOT LIKE '%F%' AND oi.p NOT LIKE '%C%') 
       OR (:PARAM1=6 AND ii.r_id     LIKE '%F%' AND oi.p NOT LIKE '%C%')
       )
Re: doubt with case [message #385906 is a reply to message #384933] Wed, 11 February 2009 17:41 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
thanks all..

@Frank

select *
from   tbl_L li
,      tbl_I ii
,      tbl_o oi 
where  ii.i_c = li.i_c
and    li.ln = oi.ln
and    oi.ln = 45
and    (  nvl(:param2, 0) [B]not in [/B](5, 6)
       or oi.p not like 'C%' 
       )
and    (  (   :param2 = 5
          and ii.r_id not like '%F%'
          )
       or (   :param2 = 6
          and ii.r_id like '%F%'
          )
       or nvl(:param2, 0) not in (5, 6)
       )




dont you think the "not in" shown in bold has to be "in" ?
Re: doubt with case [message #385914 is a reply to message #384933] Wed, 11 February 2009 20:07 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member

select *
from   tbl_L li
,      tbl_I ii
,      tbl_o oi 
where  ii.i_c = li.i_c
and    li.ln = oi.ln
and    oi.ln = 45
and    (  nvl(:param2, 0) [B]not in [/B](5, 6)
       or oi.p not like 'C%' 
       )
and    (  (   :param2 = 5
          and ii.r_id not like '%F%'
          )
       or (   :param2 = 6
          and ii.r_id like '%F%'
          )
       
       )



or should the query look ike this ?

there is no need for or nvl(:param2, 0) not in (5, 6)

but my requirement says changes should occur only to parameters
5 and 6...(there can be other parameters passed as param2)

please advice

Re: doubt with case [message #385926 is a reply to message #384933] Wed, 11 February 2009 22:59 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
>please advice
Why do you expect advice?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you


You have table & provided us no DDL for it.
You have data & provided us no DML for test data.


You have not shown us via CUT & PASTE what you actually did or how Oracle responded.

You have not provided any expected output.
Re: doubt with case [message #385952 is a reply to message #385906] Thu, 12 February 2009 00:15 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ora1980 wrote on Thu, 12 February 2009 00:41
thanks all..

@Frank

select *
from   tbl_L li
,      tbl_I ii
,      tbl_o oi 
where  ii.i_c = li.i_c
and    li.ln = oi.ln
and    oi.ln = 45
and    (  nvl(:param2, 0) [B]not in [/B](5, 6)
       or oi.p not like 'C%' 
       )
and    (  (   :param2 = 5
          and ii.r_id not like '%F%'
          )
       or (   :param2 = 6
          and ii.r_id like '%F%'
          )
       or nvl(:param2, 0) not in (5, 6)
       )




dont you think the "not in" shown in bold has to be "in" ?

No.
You state that
Quote:
if parameter is 5 -- exclude all rows with r_id like '%F%' and exclude rows like oi.p '%c%'
if parameter is 6 -- include all rows with r_id like '%F%' and exclude rows like oi.p '%c%'

In other words: exclude rows where oi.p like '%c%' if param2 is 5 or 6.

I translated that to:
Either param2 is not 5 or 6, or exclude where oi.p like '%c%'

From your original post, I did not understand that changes should only take place if param2 equals 5 or 6. However, that's an easy adjustment.
Previous Topic: group the data on month and sort it on date
Next Topic: ORA-12052 fast refresh materialized view, complete works
Goto Forum:
  


Current Time: Fri Dec 09 09:56:38 CST 2016

Total time taken to generate the page: 0.09186 seconds