oracle8.1.6 select s.shipment_id, s.shipment_date, s.order_id, s.carrier, case when nvl((select count(*) from ec_items where order_id=s.order_id) = 0) then nvl((select count(*) from ec_items where shipment_id=s.shipment_id) else 0 end,'Full','Partial') as full_or_partial, nvl((select count(*) from ec_items where shipment_id=s.shipment_id),0) as n_items from ec_shipments s $where_clause order by $order_by_clause