Re: Interesting SQL Statement Problem...
Date: 2000/07/26
Message-ID: <8lnd2v$cdc1_at_biko.telecom.ups.com>#1/1
SELECT MAX(DECODE(COL1,1,COL2,NULL)),
MAX(DECODE(COL1,3,COL2,NULL)) FROM tab1;
"ONE" NULL
SELECT MAX(DECODE(COL1,1,COL2,NULL)),
MAX(DECODE(COL1,2,COL2,NULL)), MAX(DECODE(COL1,3,COL2,NULL)), MAX(DECODE(COL1,4,COL2,NULL)) FROM tab1;
The results of the above query sould be:
"ONE" "TWO" NULL "FOUR"
Marc Nichol wrote in message
<397a8bd1.4117120_at_news1.on.sympatico.ca>...
Everyone
Assume the following table exists:
CREATE TABLE tab1
(
col1 NUMBER PRIMARY KEY,
col2 VARCHAR2(40) NOT NULL
);
And assume the table contains the following data:
COL1 COL2
1 "ONE"
2 "TWO"
4 "FOUR"
I would like to select multiple records from the same table using only
one select statement and returning only one row by doing something
similar to this:
SELECT T1.col2,
T2.col2
FROM tab1 T1,
tab1 T2
WHERE T1.col1 = 1
AND T2.col1 = 2;
The above query will successfully return only one row ("ONE" and
"TWO") since both conditions of the WHERE clause evaluate to TRUE.
However, my problem is that I would still like to return one row even
if only one part of the WHERE clause is TRUE.
For example, the following select statement returns no rows because
"T2.col1 = 3" evaluates to FALSE:
SELECT T1.col2,
T2.col2
FROM tab1 T1,
tab1 T2
WHERE T1.col1 = 1
AND T2.col1 = 3;
What I am trying to return is one row with "ONE" and NULL. Any thoughts would be greatly appreciated.
Thanks! Received on Wed Jul 26 2000 - 00:00:00 CEST