Re: Interesting SQL Statement Problem...

Select MAX(DECODE(col1,1,col2)),MAX(DECODE(col1,col2,2),... FROM t1

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 Mon Jul 31 2000 - 00:00:00 CEST