where condition scenario in oracle

Question

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

Answer 1

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
   ))

Answer 2

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>