Re: Interesting SQL Statement Problem...

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