Change SELECT statement with CASE statement-CodePudding

I have data like this:

|Nbr|Type|TelNbr|PNbr|Date|
|12345|AA|001122|432|15-DEC-14|
|23456|AA|009933|567|21-SEP-01|
|99999|AA|885523|000|17-JUN-98|
|99999|BB|885523|000|21-FEB-94|
|65432|AA|112233|NULL|01-JAN-01|
|NULL|AA|333333|555|09-JUL-20|
|65432|BB|112233|888|06-MAY-08|
|01010|CC|333333|555|04-MAR-99|

For each record, I want to select the oldest row where type equals AA. However, if Nbr or PNbr is NULL, then I want to select the oldest row regardless of the type that has the missing value (Nbr or PNbr) filled.

I attempted to do something like this, but I'm not great with CASE statements:

SELECT CASE WHEN Nbr IS NULL OR PNbr IS NULL
THEN SELECT Nbr, Type, TelNbr, PNbr, MIN(Date) FROM Table GROUP BY Nbr, Type, TelNbr, PNbr
ELSE SELECT Nbr, Type, TelNbr, PNbr, MIN(Date) FROM Table WHERE Type = 'AA' GROUP BY Nbr, Type, TelNbr, PNbr
END

Whenever I run the query, Oracle gives me the following error:

ORA-00936: missing expression

Is my syntax off? I’ve tried to find the answer online, but I’m not having any luck. Any suggestions are greatly appreciated!

CodePudding user response：

Your syntax is invalid. You can use conditional aggregation in an analytic COUNT function and then filter the rows in an outer query:

SELECT *
FROM   (
  SELECT t.*,
         COUNT(CASE WHEN nbr IS NULL OR pnbr IS NULL THEN 1 END) OVER ()
           AS null_count
  FROM   table_name t
  ORDER BY "DATE" asc
)
WHERE ( null_count > 0 OR type = 'AA' )
AND   ROWNUM = 1;

CodePudding user response：

CASE statement is not used for switching of SELECT data group as you are trying to achieve. In your case, you could use UNION (or UNION ALL) for selecting desired output from 2 data groups.

-- Group 1: data which has Nbr or PNbr is null.
SELECT * 
FROM data
WHERE Nbr IS NULL OR PNbr IS NULL
      AND DateTime <= (SELECT MIN(DateTime)
                      FROM data 
                      WHERE Nbr IS NULL OR PNbr IS NULL)

-- Group 2: data which has Type = 'AA' and Nbr, PNbr is not null.
UNION ALL
SELECT * 
FROM data
WHERE Type = 'AA' AND Nbr IS NOT NULL AND PNbr IS NOT NULL
      AND DateTime <= (SELECT MIN(DateTime)
                      FROM data 
                      WHERE Type = 'AA' AND Nbr IS NOT NULL AND PNbr IS NOT NULL)

Demo: http://sqlfiddle.com/#!4/b519d/232