CODEWITHSUNDEEP
sqloracle-databasedecode
Jeremy
Jeremy

Reputation: 5435

Oracle SQL -- decode to multiple elements

I might make a join condition like:

table1.value_1 = decode(table2.encoded_value,1,'a',2,'b',3,'c')

But what if I need to do an in-type join?

e.g. How could I write a decode to accomplish the functionality of, say:

table1.value_1 in 
  decode(table2.encoded_value,
         1,
         'a',
         2,
         'b',
         3,
         'c',
         4,
         ('d','e','f')
   );

This syntax is not valid, but the idea is that in the case where table2.encoded_value is 4, the condition would become table1.value_1 in ('d','e','f').

Upvotes: 1

Views: 382

Answers (2)

Jean-François Savard
Jean-François Savard

Reputation: 21004

I don't think this is possible, you should instead change the algorithm of your query.

Instead of checking if value_1 is in a range, you could change your algorithm like this:

(table2.encoded_value <> 4 
and table1.value_1 = decode(table2.encoded_value,
                              1, 'a',
                              2, 'b',
                              3, 'c'))
or table2.encoded_value = 4 and table1.value_1 in ('d', 'e', 'f'/*Or a select of multiple values*/)

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231671

You generally wouldn't use a function. You'd just use boolean logic

WHERE (table2.encoded_value = 1 AND table1.value_1 = 'a')
   OR (table2.encoded_value = 2 AND table1.value_1 = 'b')
   OR (table2.encoded_value = 3 AND table1.value_1 = 'c')
   OR (table2.encoded_value = 4 AND table1.value_1 in( 'd', 'e', 'f'))

Upvotes: 3

Related Questions