oracle: can you use DECODE across tables?

Question

pretty new to the function, but as i understand DECODE tests the specified attribute (arg1) against another specified value (arg2) and if they match, it displays a result (arg3) otherwise if it doesn't match, the default will display (arg4)

DECODE(firstname,'John','last is doe','I don't know the last name')

Can you do this using data from another table to emulate a join? like

DECODE(publisherid, publisher.pubid, publisher.name,'unknown')

I'm trying it but it isn't working. didn't know if it was possible or maybe im just doing it wrong. Any thoughts?

Answer 1

The 11g documentation states that the search parameters can be an expression, so what you have should work. If you get ORA errors, you should let us know. Otherwise, it's likely you don't have data to match, or the publisher.name column is null...

You wouldn't use it to emulate a join -- you still want to actually JOIN (ANSI 89 or 92 syntax, but preferably 92) the tables appropriately or you'll be dealing with a lot of garbage rows from a cartesian product which the DECODE will then execute upon.

Unless you're on pre-9i (8.1.6, according to Ask Tom), I wouldn't waste your time with DECODE and use the ANSI CASE statement instead:

CASE 
  WHEN publisherid = publisher.pubid THEN publisher.name
  ELSE 'unknown'
END

Answer 2

You can use it between columns of different tables like so:

select oi.order_id, oi.product_id, oi.unit_price, p.list_price, 
       decode(oi.unit_price, p.list_price, 'No Discount',
                   p.list_price - oi.unit_price||' discount') disc
from demo_ord_items oi 
  join demo_product_info p on p.product_id = oi.product_id
where oi.order_id = 1

In theory you can use it as a join condition

select oi.order_id, oi.product_id, oi.unit_price, p.list_price
from demo_ord_items oi 
  join demo_product_info p on p.product_id = oi.product_id 
                            and decode(oi.unit_price, p.list_price,0,1) = 1
where oi.order_id = 1

but it isn't common. A condition is boolean (true/false) and a decode returns a number or character, so the decode is sort of redundant (or can probably be re-written to be clearer).

Answer 3

As far as syntax is concerned, you got it correct. If you always get 'unknown' as result output your publisherid and check if you have it in the table.

If ID-s are text fields, note that the decode is case sensitive. Also check if perhaps you have trimmed ids somewhere.

Answer 4

Use DECODE when any of the following is true:

• You want to write non-portable SQL
• You want to to write code that only Oracle developers recognize and understand
• You want to write code that is hard to read
• You want to increase the chance of introducing bugs the next time you change the code
• You want the optimizer to generate suboptimal execution plans (when used in where-clause).
• You are writing throw-away code

In short, don't use DECODE at all. There is nothing it can't do that couldn't be done with a CASE/WHEN. Yes, there will be more typing at the keyboard, and it is "worth" it.

Having said that, there is one throw-away case when I always prefer DECODE, that has to do with how it deals with NULL. The scenario is when I have two (hopefully) identical tables with the same key, and when I want to find out if there are any rows where the two tables don't agree on a column value.

For example, let's say you have rewritten a batch procedure for performance reasons, and you want to make sure that the new version produces the same results as the old version. In this situation, you would consider NULL and NULL to be the same "value". DECODE happens to behave just like that.

decode(a, b, 'Y', 'N') 

would translate to the following case/when construct

case when a = b then 'Y'
     when a is null and b is null then 'Y'
     else 'N'
 end  

Here is an example of what I mean

with old_table as(
  select 1 as id, 'A'  as old_col from dual union all
  select 2 as id, 'A'  as old_col from dual union all
  select 3 as id, 'A'  as old_col from dual union all
  select 4 as id, null as old_col from dual
)
,new_table as(
  select 1 as id, 'A'   as new_col from dual union all
  select 2 as id, 'B'   as new_col from dual union all
  select 3 as id, null  as new_col from dual union all
  select 4 as id, null  as new_col from dual
)
select id
      ,old_col
      ,new_col
      ,decode(old_col, new_col, 'Y', 'N') as has_same_val
  from old_table 
  join new_table using(id);

ID OLD_COL NEW_COL HAS_SAME_VAL
-- ------- ------- ------------
 1 A       A       Y
 2 A       B       N
 3 A       null    N            <-- Note those
 4 null    null    Y            <-- two cases

So to find the difference I would do:

where decode(old_col1, new_col1, 'same', 'diff') = 'diff'
   or decode(old_col2, new_col2, 'same', 'diff') = 'diff'
   or decode(old_col3, new_col3, 'same', 'diff') = 'diff'
   or decode(old_col4, new_col4, 'same', 'diff') = 'diff'
   or decode(old_col5, new_col5, 'same', 'diff') = 'diff'