CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle11g
Sam
Sam

Reputation: 2065

Oracle select query with dynamic in clause

I have the following query

    select * from table_1 
    where Conditions in 
         (select case when check_condition = 'Y' then 'Condition_1' 
                 else 'N/A' end as Check_condition 
          from table_2 
          WHERE id = 1122)

where table_1 contains the values in column Conditions as follows. Condition_1,Condition_2

This works fine and return me the results.

i want to use multiple select statements inside the in clause and I did it as below.

    select * from table_1
    where Conditions in (
         select ''''||
             (select case when check_condition = 'Y' then 'Condition_1' 
               else 'N/A' end as Check_condition 
               from table_2 
               WHERE id = 1122)||''''||','''||
                  (select case when check_condition = 'Y' then 'Condition_2'
                        else 'N/A' end as Check_condition 
                  from table_2 WHERE id = 1122)||''''
                 from dual
                )

inner query ( inside the in clause) giving me the correct results as expected - 

'Condition_1','Condition_2'

and when I copy paste it to the parent query it works fine and show the results.

select * from table_1 where Conditions in ('Condition_1','Condition_2')

My issue is, it is not giving any results when I used the second query. I know that sub query will return the results that should match the rows in the outer query. But it shows me empty result set.

I am using oracle 11g

Can anyone please help me out.. Thank you all in advance.

Upvotes: 0

Views: 1811

Answers (2)

APC
APC

Reputation: 146209

The question is a bit unclear regarding requirements. What I think you want is to select records from table1 only when:

  • rows match 'Condition_1' or 'Condition_2'
  • check_condition = 'Y'
  • there is a row in table2 with an ID = 1122

It's not clear from your question whether check_condition is a column or a variable, and if it is a column to which table it belongs. Consequently this solution may be wrong but it illustrates the principle. 

select * from table1 t1
where t1.conditions in ('Condition_1','Condition_2')
and t1.check_condition = 'Y'
and exists
        ( select null from table2 t2
          where t2.id = 1122 )

If this doesn't provide the solution you need please revise your question so it states the business logic you need to implement, and also includes relevant tables descriptions.

Upvotes: 4

Alex Poole
Alex Poole

Reputation: 191245

You aren't ending up with two values passed into the in clause like when you do it manually:

select * from table_1 where Conditions in ('Condition_1','Condition_2')

You're passing a single value which is the concatenation of the values:

select * from table_1 where Conditions in ('''Condition_1'',''Condition_2''')

And no condition matches that concatenated value, so you get no results. You could do something like:

select * from table_1 where Conditions in (
  select case when check_condition = 'Y' then 'Condition_1' else 'N/A' end
  from table_2 WHERE id = 1122
  union all
  select case when check_condition = 'Y' then 'Condition_2' else 'N/A' end
  from table_2 WHERE id = 1122
)

Or possibly, if I follow what you're doing (which is doubtful as I'm not sure I understand your data model!):

select * from table_1 where check_condition != 'Y' or Conditions in (
  select 'Condition_1' from table_2 WHERE id = 1122
  union all
  select 'Condition_2' from table_2 WHERE id = 1122
)

It seems like you should be able to do this more cleanly, with joins, but I think we'd need to see the structures and sample data to understand what's going on a bit more.

Upvotes: 1

Related Questions