Not sure how to use Decode, NVL, and/or isNull (or something else?) in this situation
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 any开发者_Go百科one explain how I can do this in SQL?
Thanks in advance!
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;
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
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'.
精彩评论