How to have some null and empty values in a Group By-CodePudding

Part of the answer is [here] (Find all Boxes containing files where all the files of the box have expiration date inferior to a DATE) and it works :

"To check that the maximum file date for each box code is less than 2022-01-01, you can use:

SELECT b.code,
   MAX(b.EXPIRATION_DATE) AS expiration_date
FROM   box b
   join COMPONENT_A cpp on cpp.b_Id=b.Id
   join COMPONENT_Z cpt on cpt.A_Id=cpp.Id
   join "FILE" f on f.Z_Id =cpt.Id
WHERE  b.EXPIRATION_DATE < DATE '2022-01-01'
GROUP BY b.code
HAVING MAX(f.EXPIRATION_DATE) < DATE '2022-01-01';

BUT it also takes boxes where files (or boxes) do not have a date. How to avoid all boxes where either one file has no date or the box itself ? I tried adding a fake exterior date :

HAVING MAX(f.EXPIRATION_DATE) < '01/01/2022';
AND NVL(min(f.EXPIRATION_DATE), to_date('01/01/1011','DD/MM/YY'))<>TO_DATE('01/01/1011','DD/MM/YY')

But

min(f.EXPIRATION_DATE)

does not work since "min" takes out the null/empty, it does not considers null/empty as "min". I am in the group by I have to use an aggregate expression.

CodePudding user response：

You can COUNT the number of files with NULL expiration dates and make sure there are zero and, assuming that there will be one box per group and the box's expiration date will be identical for all rows then you can use:

SELECT b.code,
   MAX(b.EXPIRATION_DATE) AS expiration_date
FROM   box b
   join COMPONENT_A cpp on cpp.b_Id=b.Id
   join COMPONENT_Z cpt on cpt.A_Id=cpp.Id
   join "FILE" f on f.Z_Id =cpt.Id
WHERE  b.EXPIRATION_DATE < DATE '2022-01-01'
GROUP BY b.code
HAVING MAX(f.EXPIRATION_DATE) < DATE '2022-01-01'
AND    COUNT(CASE WHEN f.EXPIRATION_DATE IS NULL THEN 1 END) = 0;

If there can be multiple box rows with different expiration dates in a group then you can use:

SELECT b.code,
   MAX(b.EXPIRATION_DATE) AS expiration_date
FROM   box b
   join COMPONENT_A cpp on cpp.b_Id=b.Id
   join COMPONENT_Z cpt on cpt.A_Id=cpp.Id
   join "FILE" f on f.Z_Id =cpt.Id
WHERE  b.EXPIRATION_DATE < DATE '2022-01-01'
OR     b.expiration_date IS NULL
GROUP BY b.code
HAVING MAX(f.EXPIRATION_DATE) < DATE '2022-01-01'
AND    COUNT(CASE WHEN b.EXPIRATION_DATE IS NULL THEN 1 END) = 0
AND    COUNT(CASE WHEN f.EXPIRATION_DATE IS NULL THEN 1 END) = 0;

db<>fiddle here