Not sure how to use Decode, NVL, and/or isNull (or something else?) in this situation

Question

I have a table of orders for particular products, and a table of products that are on sale. (It's not ideal database structure, but that's out of my control.) What I want to do is outer join the order table to the sale table via product number, but I don't want to include any particular data from the sale table, I just want a Y if the join exists or N if it doesn't in the output. Can anyone explain how I can do this in SQL?

Thanks in advance!

Answer 1

You should be able to use the CASE construct, and it would look something like this:

select
    order.foo,
    case
        when sale.argle is null then 'N'
        else 'Y'
    end
from order
left join sale on order.product_number = sale.product_number;

Answer 2

Try (untested):

SELECT O.*, DECODE(NVL(p.product_num, 'X'), 'X', 'N', 'Y')
  FROM Orders AS o LEFT JOIN Products AS p ON o.Product_Num = p.Product_Num

The NVL will translate nulls in the 'p.product_num' to 'X', which will compare equal to 'X' in the DECODE, generating 'N'; non-null product numbers will be a number, not equal to 'X', and hence will generate a 'Y'.

Answer 3

I nornally use NVL2 for this type of situation...

SELECT col_one
     , NVL2( col_one, 'Y', 'N' )   col_one_exists
     , col_two
     , NVL2( col_two, 'Y', 'N' )   col_two_exists
  FROM ( SELECT '12345'   col_one
              , NULL   col_two
           FROM dual
       )

Would return this:-

COL_ONE  COL_ONE_EXISTS  COL_TWO  COL_TWO_EXISTS
12345    Y                         N