Run a query based on the output of a condition in Oracle sql

Question

I have this table with column name as "IdentificationNo", "InformationType", "datefield" and "vauleAmount". Now based on the value of these fields, I want to run another query in the same table as explained below:

Let's say if for a given IdentificationNo (123), (informationType = 'abc' and datefield is not empty) AND, for the same IdentificationNo (123) there is a row with (informationType = 'xyz' and valueAmount=0) present too then I want to ignore such rows from the returned resulset.

1. So, if there is 1 row present with 'abc' informationType with identificationNo '123' and (datefield is not empty) and and 1 row with 'xyz' for the same identificationNo and ( valueAmount=0) then returned resultset returned should be empty.

AND,

2. If informationType is not 'abc' then don't look further for 'xyz' informationType and return null. i.e 0 rows

AND, 3. If there is just 'abc' informationType for a give identificationNo is present, return it in the resultset.

There is only table involved here and can have different informationType. Here, I am just concerned for 'abc' and 'xyz'. One identificationNo can have multiple entries with different informationTypes.

As per this sample data: Since IdenticationNo '123' has both 'abc' and 'xyz' information present and they fullfil the 1st condition too , so, the query should not return any row.

  SELECT '123', 'abc', DATE '1970-01-01', 100 FROM DUAL UNION ALL
  SELECT '123, 'xyz', DATE '1970-01-02',   0 FROM DUAL UNION ALL
  SELECT '123', 'xyz', DATE '1970-01-03', 200 FROM DUAL UNION ALL
  SELECT '123', 'def', DATE '1970-01-04', 300 FROM DUAL;

Can it be achieved using a query? I cannot use PL/SQL.

Answer 1

Use conditional aggregation within an analytic function to count the number of 'abc' rows with a non-NULL datefield and then filter to only include the 'xyz' rows if there is also an 'abc' row:

SELECT informationType, dateField, valueAmount
FROM   (
  SELECT informationType, dateField, valueAmount,
         COUNT(
           CASE
           WHEN informationType = 'abc' AND dateField IS NOT NULL
           THEN 1
           END
         ) OVER () AS num_abc
  FROM   table_name
  WHERE  informationType = 'abc'
  OR     (informationType = 'xyz' AND valueAmount = 0)
)
WHERE informationType = 'abc'
OR    num_abc > 0;

Which, for the sample data:

CREATE TABLE table_name (informationType, dateField, valueAmount) AS
  SELECT 'abc', DATE '1970-01-01', 100 FROM DUAL UNION ALL
  SELECT 'xyz', DATE '1970-01-02',   0 FROM DUAL UNION ALL
  SELECT 'xyz', DATE '1970-01-03', 200 FROM DUAL UNION ALL
  SELECT 'def', DATE '1970-01-04', 300 FROM DUAL;

Outputs:

INFORMATIONTYPE	DATEFIELD	VALUEAMOUNT
abc	1970-01-01 00:00:00	100
xyz	1970-01-02 00:00:00	0

If you update the abc row to have a NULL datefield then the query outputs:

INFORMATIONTYPE	DATEFIELD	VALUEAMOUNT
abc	null	100

If you delete all the 'abc' rows then the query outputs zero rows.

fiddle