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

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

-- 
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).