| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL puzzle - using where (a, b, c) in select (a, b, c from...
Jacques,
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.
Regards
Naveen
-----Original Message-----
Sent: Wednesday, November 20, 2002 1:34 AM
To: Multiple recipients of list ORACLE-L
from...
Thank you for your answer.
Your answer would solve my problem, except for the fact that i want a general purpose solution that would work in any table. What if the three columns in the table were varchar2?
-----Original Message-----
Sent: lundi, 18. novembre 2002 21:59
To: Multiple recipients of list ORACLE-L
from...)
an
Since Id, Cost and Sell are all NUMBERs, so they cannot contain CHARs, which makes a perfect case for decode. You can use CHARs to substitute for NULLs in DECODE. Following is the query I wrote:
DELETE FROM Widgets_Copy a
WHERE (DECODE(a.Id, NULL, 'X', a.Id), DECODE(a.Cost, NULL, 'X', a.Cost),
DECODE(a.Sell, NULL, 'X', a.Sell)) IN
(
SELECT DECODE(b.Id, NULL, 'X', b.Id), DECODE(b.Cost, NULL, 'X', b.Cost), DECODE(b.Sell, NULL, 'X', b.Sell)
FROM Widgets b
)
AND a.Rowid != (
SELECT MIN(c.Rowid) FROM Widgets_Copy c
WHERE ( DECODE(c.Id, NULL, 'X', c.Id),
DECODE(c.Cost, NULL, 'X', c.Cost),
DECODE(c.Sell, NULL, 'X', c.Sell)
) IN
( SELECT DECODE(d.Id, NULL, 'X', d.Id),
DECODE(d.Cost, NULL, 'X', d.Cost),
DECODE(d.Sell, NULL, 'X', d.Sell)
FROM Widgets d)
AND DECODE(c.Id, NULL, 'X', c.Id) = DECODE(a.Id, NULL,
'X', a.Id)
AND DECODE(c.Cost, NULL, 'X', c.Cost) = DECODE(a.Cost,
NULL, 'X', a.Cost)
AND DECODE(c.Sell, NULL, 'X', c.Sell) = DECODE(a.Sell,
NULL, 'X', a.Sell)
);
SQL> select id, cost, sell from widgets order by 1, 2, 3 ;
ID COST SELL
---------- ---------- ----------
1 10 20
1 10
1
SQL> select 'ROW' || to_char (rownum) as row_num,
2 id, cost, sell
ROW_NUM ID COST SELL
------------------------------------------- ---------- ---------- ----------
ROW3 1 10 20 ROW6 1 10 20 ROW7 1 10 30 ROW8 1 10 30 ROW2 1 10 ROW5 1 10 ROW1 1 ROW4 1
8 rows selected.
SQL> DELETE FROM Widgets_Copy a
2 WHERE (DECODE(a.Id, NULL, 'X', a.Id), DECODE(a.Cost, NULL, 'X', a.Cost),
DECODE(a.Sell, NULL, 'X', a.Sell)) IN
3 ( 4 SELECT DECODE(b.Id, NULL, 'X', b.Id), DECODE(b.Cost, NULL, 'X',b.Cost), DECODE(b.Sell, NULL, 'X', b.Sell)
5 FROM Widgets b 6 ) 7 AND a.Rowid != ( 8 SELECT MIN(c.Rowid) FROM Widgets_Copy c 9 WHERE ( DECODE(c.Id, NULL, 'X', c.Id), 10 DECODE(c.Cost, NULL, 'X', c.Cost), 11 DECODE(c.Sell, NULL, 'X', c.Sell) 12 ) IN 13 ( SELECT DECODE(d.Id, NULL, 'X', d.Id), 14 DECODE(d.Cost, NULL, 'X', d.Cost), 15 DECODE(d.Sell, NULL, 'X', d.Sell) FROM Widgets d) 16 AND DECODE(c.Id, NULL, 'X', c.Id) = DECODE(a.Id, NULL, 'X', a.Id) 17 AND DECODE(c.Cost, NULL, 'X', c.Cost) = DECODE(a.Cost, NULL, 'X', a.Cost) 18 AND DECODE(c.Sell, NULL, 'X', c.Sell) = DECODE(a.Sell, NULL, 'X', a.Sell) 19 );
3 rows deleted.
SQL> select 'ROW' || to_char (rownum) as row_num,
2 id, cost, sell
3 from widgets_copy
4 order by 2, 3, 4
5 /
ROW_NUM ID COST SELL
------------------------------------------- ---------- ---------- ----------
ROW3 1 10 20 ROW4 1 10 30 ROW5 1 10 30 ROW2 1 10 ROW1 1
SQL>
Regards
Naveen
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: naveen_nahata_at_mindtree.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesReceived on Wed Nov 20 2002 - 05:58:27 CST
---------------------------------------------------------------------
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).
 |
 |