CODEWITHSUNDEEP
sqloracle-databasewhere-clauseclause
Francis John
Francis John

Reputation: 191

where condition scenario in oracle

i have requirement in where condition

if my id is in 1 then it should check id 4,5,6,7 or it should check value which is in id this id i will pass as parameter to query

select * from table_a  where id

Help me in this

Upvotes: 0

Views: 76

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

where ip.prs_nationality_id =case when :p_prs_nat in( 219) then it shud check (231,259,343) else :p_prs_nat end how to achieve this functionality

You cannot directly use IN while returning the result in the THEN clause of CASE expression. However, you could first check the condition itself using AND operator and return TRUE whenever it matches.

For example,

SQL> WITH DATA AS
  2    (
  3    SELECT 1 ID, 'X' STR FROM DUAL UNION ALL
  4    SELECT 2 ID, 'A' STR FROM DUAL UNION ALL
  5    SELECT 3 ID ,'P' STR FROM DUAL UNION ALL
  6    SELECT 4 ID ,'Q' STR FROM DUAL
  7    )
  8  SELECT *
  9  FROM DATA
 10  WHERE (
 11    CASE
 12      WHEN ID  = 1
 13      AND str IN ('A','Y','Z')
 14      THEN 1
 15      WHEN ID <> 1
 16      THEN 1
 17    END ) =1
 18  /

        ID S
---------- -
         2 A
         3 P
         4 Q

SQL>

So, you did not get the row with ID = 1,since it did not match the condition AND str IN ('A','Y','Z').

If it would match, it will return those matching rows too:

SQL> WITH DATA AS
  2    (
  3    SELECT 1 ID, 'X' STR FROM DUAL UNION ALL
  4    SELECT 2 ID, 'A' STR FROM DUAL UNION ALL
  5    SELECT 3 ID ,'P' STR FROM DUAL UNION ALL
  6    SELECT 4 ID ,'Q' STR FROM DUAL
  7    )
  8  SELECT *
  9  FROM DATA
 10  WHERE (
 11    CASE
 12      WHEN ID  = 1
 13      AND str IN ('X','Y','Z')
 14      THEN 1
 15      WHEN ID <> 1
 16      THEN 1
 17    END ) =1
 18  /

        ID S
---------- -
         1 X
         2 A
         3 P
         4 Q

SQL>

Upvotes: 0

psaraj12
psaraj12

Reputation: 5072

You can use the below 

select * from individual ip 
 where ( 
    ( :p_prs_nat  = 219 and ip.prs_nationality_id in (231,259,343) ) 
     or (:p_prs_nat <> 219 and :p_prs_nat=ip.prs_nationality_id
   ))

Upvotes: 2

Related Questions