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.
AND,
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.
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.