Oracle SQL IN-clause over multiple columns

Question

I am stumbling over a problem in oracle SQL which I can't solve on my own, so I hope you guys can help me out. I am trying to filter for multiple IDs in multiple columns, each ID can occur in at least 1 column. This is what I have achieved so far:

select name, ident1, ident2, ident3, ident4, ident5, ident6, last_modified
from ident_history
where ((ident1 in ('1SP563', 'T6R840', 'FF764P', 'T753RM', '54T56A', '4386HA', '667R44')
or ident2 in ('1SP563', 'T6R840', 'FF764P', 'T753RM', '54T56A', '4386HA', '667R44')
or ident3 in ('1SP563', 'T6R840', 'FF764P', 'T753RM', '54T56A', '4386HA', '667R44')
or ident4 in ('1SP563', 'T6R840', 'FF764P', 'T753RM', '54T56A', '4386HA', '667R44')
or ident5 in ('1SP563', 'T6R840', 'FF764P', 'T753RM', '54T56A', '4386HA', '667R44')
or ident6 in ('1SP563', 'T6R840', 'FF764P', 'T753RM', '54T56A', '4386HA', '667R44'))
order by last_modified desc

Well, this works, but I'd wish for a less complicated way around, because I have about 20 columns containing IDs and about 100 IDs to compare to them, which makes the code pretty confusing. I thought about something like this:

where (ident1, ident2, ident3, ident4, ident5, ident6) in ('1SP563', 'T6R840', 'FF764P', 'T753RM', '54T56A', '4386HA', '667R44')

But this does not work. If you have any idea, it would be appreciated.

Create a table var or temp table for ('1SP563', 'T6R840', 'FF764P', 'T753RM', '54T56A', '4386HA', '667R44'). Then just use left joins or an exists statement for each ident column. — Tjasun
– Tjasun, Commented Aug 4, 2015 at 6:51

Patrick Hofman · Accepted Answer · 2015-08-04 06:55:03Z

4

That is not possible. An alternative would be this:

with t
as
( select '1SP563' v from dual
  union all
  select 'T6R840' v from dual
  union all
  select 'FF764P' v from dual
  union all
  select 'T753RM' v from dual
  union all
  select '54T56A' v from dual
  union all
  select '4386HA' v from dual
  union all
  select '667R44' v from dual
)
select name, ident1, ident2, ident3, ident4, ident5, ident6, last_modified
from   tomis_rep.t_fact_fertigungen_comp fc
join   t
on     t.v in (ident1, ident2, ident3, ident4, ident5, ident6)
order
by     last_modified desc

Instead you can also join with a (temporary) table, which is somewhat the same.

It prevents some of the repetition in your previous query, but is isn't perfect. The reason is that I think you have not normalized your model enough. This is the result.

Or maybe a little hackish:

select name, ident1, ident2, ident3, ident4, ident5, ident6, last_modified
from   tomis_rep.t_fact_fertigungen_comp fc
join   ( select column_value v
         from   table(sys.dbms_debug_vc2coll('1SP563', 'T6R840', 'FF764P', 'T753RM', '54T56A', '4386HA', '667R44'))
       ) t
on     t.v in (ident1, ident2, ident3, ident4, ident5, ident6)
order
by     last_modified desc

edited Aug 4, 2015 at 6:55

answered Aug 4, 2015 at 6:52

Patrick Hofman

158k23 gold badges270 silver badges343 bronze badges

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

2 Comments

janatuerlich Over a year ago

Thank you! Unfortunately, I have not set up the tables and have no permission to do so. So I have to use what I have :)

Patrick Hofman Over a year ago

@janatuerlich I shortened the samples.

Collectives™ on Stack Overflow

Oracle SQL IN-clause over multiple columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related