RE: SQL puzzle - using where (a, b, c) in select (a, b, c from...

-----Original Message-----
>From: Naveen Nahata [mailto:naveen_nahata_at_mindtree.com]>Sent: mercredi, 20. novembre 2002 03:58>>Why not use some suffieintly random and crap value> lets say '____~~~CRAP~~~____' to replace nulls in NVL> or DECODE for the query to work with Varchar columns?> I think one can be reasonably sure that such a value> will never be inserted into the column :-)> >Otherwise, I don't think there is any other solution.

Thank you Naveen. The problem is that this solution will be part of a
commercial product, and I would hate to have to include in the Release Notes
something like "This product is not supported if your columns contain the
value '____~~~CRAP~~~____' ". :)

Instead, I will suggest to the developer that we code a long statement
allowing for null columns. e.g.
delete from widgets_copy
where rowid in
(
select min (rowid) from widgets_copy x
where x.cost is null and x.sell is null

and x.id in
(select y.id from widgets y where y.cost is null and y.sell is null)
group by x.id
union
select min (rowid) from widgets_copy x
where x.sell is null

and (x.id, x.cost) in
(select y.id, y.cost from widgets y where y.sell is null)
group by x.id, x.cost
union
select min (rowid) from widgets_copy x
where x.cost is null

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jacques Kilchoer
INET: Jacques.Kilchoer_at_quest.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).