How to not return result if one value is null?

There is a top level weight for a shipment but within that there is several weights for handling units. If one of these handling units has no value then I want the shipment to not show up in my query.

Here is my query –

`SELECT DISTINCT S.SHIPMENT_ID,
                            S.STATE,
                            S.CONTRACT,
                            CASE
                                WHEN H.MANUAL_GROSS_WEIGHT <> '0' THEN
                                 SUM(H.MANUAL_GROSS_WEIGHT)
                                ELSE
                                 NULL
                            END MANUAL_GROSS_WEIGHT
FROM   SHIPMENT S
JOIN   HANDLING_UNIT_SHIPMENT H
ON     H.SHIPMENT_ID = S.SHIPMENT_ID
WHERE  S.STATE = 'Preliminary'
         AND S.CONTRACT = 'BVL10'
         AND H.MANUAL_GROSS_WEIGHT <> '0'
GROUP  BY S.SHIPMENT_ID,
                S.STATE,
                S.CONTRACT,
                H.MANUAL_GROSS_WEIGHT`

This returns 123 as there is currently two boxs, one with a manual weight of 123 and one with 0.

It should not return this shipment at all as one of the manual weights is 0.

Answer

I would not use LEFT JOIN for this. I would simply use a HAVING clause. Assuming the weights are never negative:

SELECT S.SHIPMENT_ID, S.STATE, S.CONTRACT,
       SUM(H.MANUAL_GROSS_WEIGHT) as MANUAL_GROSS_WEIGHT
FROM SHIPMENT S JOIN
     HANDLING_UNIT_SHIPMENT H
     ON H.SHIPMENT_ID = S.SHIPMENT_ID 
WHERE S.STATE = 'Preliminary' AND S.CONTRACT = 'BVL10'       
GROUP BY S.SHIPMENT_ID, S.STATE, S.CONTRACT
HAVING MIN(H.MANUAL_GROSS_WEIGHT) > 0;

If the weight could be 0 and you have negative values, then:

HAVING SUM(CASE WHEN H.MANUAL_GROSS_WEIGHT = 0 THEN 1 ELSE 0 END) = 0

Note that SELECT DISTINCT is almost never used with GROUP BY. In this query, it is not needed.

EDIT:

If you want to filter out any shipment that has a NULL weight:

HAVING COUNT(H.MANUAL_GROSS_WEIGHT) = COUNT(*)

This requires that all be non-NULL.