I have a table like this

I need to retrieve rows when only all the status values are good, if there is inprogress should not retrieve any row

Expected output:

1 row as below

CodePudding user response：

To retrieve data with the status is Good, the query is:

SELECT status, business_date, load_date FROM yourTable WHERE status ='Good';

But, if want to avoid duplicated values, your query becomes:

SELECT DISTINCT status, business_date, load_date FROM yourTable WHERE status ='Good';

CodePudding user response：

You can use:

SELECT Category,
       business_date,
       load_date,
       status
FROM   (
  SELECT t.*,
         COUNT(CASE WHEN status != 'Good' THEN 1 END) OVER () AS num_not_good
  FROM   table_name t
)
WHERE  num_not_good = 0;

Which, for your sample data:

CREATE TABLE table_name (Category, business_date, load_date, status) AS
SELECT 'A', DATE '2020-01-11', DATE '2020-01-12', 'Good' FROM DUAL UNION ALL
SELECT 'B', DATE '2020-01-11', DATE '2020-01-12', 'Good' FROM DUAL UNION ALL
SELECT 'C', DATE '2020-01-11', DATE '2020-01-12', 'Inprogress' FROM DUAL

Outputs, as expected, no rows as there is an Inprogress row.

If you then use:

UPDATE table_name SET status = 'Good';

Then run the query again then the output is:

CATEGORY	BUSINESS_DATE	LOAD_DATE	STATUS
A	11-JAN-20	12-JAN-20	Good
B	11-JAN-20	12-JAN-20	Good
C	11-JAN-20	12-JAN-20	Good

db<>fiddle here