Return detail records only if all meet a condition?

[samyers]

I have a challenge with a sales database.

There are two tables: header and detail.
Header has order_number and order_status, along with various other fields.
Detail has order_number, line_number, line_status.

The application is supposed to set header.order_status = 'C' when all detail.line_status = "C". I suspect that this is not happening; that orders are showing as open (status "O") even though all detail_status values are "C".

I want to run a query that returns the order_number and detail info for all orders where order_status = "O" and all detail_status = "C"

I have this:
select
h.order_number
, h.order_status
, d.line_number
, d.line_status

from header h
, detail d

where

h.order_status = "O"
h.order_number = d.order_number
and d.line_status = "C"

However, this returns every order with any line_status = "C", so a lot of valid orders are returned with the errors. How can I select orders only if every detail line has status "C"?

 

[carp]

Something like:

select
h.order_number
, h.order_status
, d.line_number
, d.line_status
from header h
, detail d
where 
h.order_status = "O"
h.order_number = d.order_number
and NOT EXISTS (SELECT 'x' 
                  FROM detail d2
                 WHERE NVL(d2.line_status,'O') != 'C'
                   AND d2.order_number = h.order_number);

This is untested, but I believe it should work.

 

[Beilstwh]

If ultimately you want to correct the status flag then

update header x
set x.order_status = 'C'
where x.order_number in
(select
h.order_number
from header h
where
h.order_status = "O"
and NOT EXISTS (SELECT null
                  FROM detail d
                 WHERE NVL(d.line_status,'O') != 'C'
                   AND d.order_number = h.order_number));

Bill
Lead Application Developer
New York State, USA