Oracle select query with dynamic in clause

Question

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.

APC · Accepted Answer · 2018-03-03 14:25:34Z

4

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.

edited Mar 3, 2018 at 14:25

answered Jul 26, 2013 at 8:26

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sam Over a year ago

Thanks a lot for the answer. i got the point. will work on it. appreciate your help.

Alex Poole · Accepted Answer · 2013-07-26 08:19:47Z

1

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.

answered Jul 26, 2013 at 8:19

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

2 Comments

Sam Over a year ago

Thank you very much for the clarification. i will work on your approach. appreciate your help.

Alex Poole Over a year ago

@sam - I think APC's approach is likely to be closer to what you need (and better, of course, since it only touched table2 once), but I'll leave this here for the explanation part.

Collectives™ on Stack Overflow

Oracle select query with dynamic in clause

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related