Have Geek Brain

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

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

Thanks in advance!

+2


a source to share


3 answers


You have to use a construct CASE

and it will 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;
+4


a source


I am using 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
+2


a source


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

NVL converts zeros to "p.product_num" to "X", which will compare against "X" in DECODE, generating "N"; non-zero product numbers will be a number other than "X" and will therefore generate "Y".

0


a source






More articles:


All Articles