SQL developer not show results if they match certain criteria

Question

I'm trying to make a oracle sql developer query to return items which only meet certain criteria. The problem I'm facing is that one item can have multiple conditions and based on those conditions I need to filter out the results. So it needs some sort of array or select inside a select?

The data set looks like this:

ItemNo	Condition	Date
1234	A	9999-99-99
1234	B	9999-99-99
1234	B	9999-99-99
5555	A	9999-99-99
5555	B	2024-12-10

Now I need to return Items only that DO NOT have condition B assigned to the item only if the condition B date is not 9999-99-99 or more than condition A date.

so In essence item 1234 should not be returned as it has 2x condition B with date which is equal to condition A and the date is also 9999-99-99.

Item 5555 should be returned as even though it has condition B, the date is less than condition A.

Currently I have this:

select ItemNo from DataSet where Condition not in 'B' and Date between :a and :b

I was thinking of creating a query to select all the Items without checking for condition and then maybe nesting it into another select which would check if each item has multiple conflicting conditions but not sure if it's the right approach.

Answer 1

• Condition B doesn't exist for the ItemNo.
• Or, if Condition B does exist, its date (DateB) is not 9999-12-31(any other date), and is less than the date of Condition A (DateA).

Fiddle

SELECT ItemNo
FROM (
    SELECT 
        ItemNo,
        MAX(CASE WHEN Condition = 'A' THEN Date1 END) AS DateA,
        MAX(CASE WHEN Condition = 'B' THEN Date1 END) AS DateB
    FROM test
    GROUP BY ItemNo
) conditions
WHERE (
    (DateB IS NULL)  
    OR 
    (DateB != TO_DATE('9999-12-31', 'YYYY-MM-DD') AND DateB < DateA)  
);

Output