CODEWITHSUNDEEP
sqloracle-database
janatuerlich
janatuerlich

Reputation: 173

Oracle SQL IN-clause over multiple columns

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.

Upvotes: 2

Views: 3447

Answers (1)

Patrick Hofman
Patrick Hofman

Reputation: 156938

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

Upvotes: 4

Related Questions