ChrisM@Work (1/11/2013)
I think this better fits the spec:
SELECT SUM(teu) AS TEU
FROM NCV_BL ncv
JOIN MG_VSLVOY_HEADER vh
ON ncv.saisan_VESSEL_CD = vh.VESSEL_CD
AND ncv.saisan_VOYAGE_CD = vh.VOYAGE_NUM
AND ncv.saisan_LEG_CD = vh.LEG_CD
JOIN MG_VSLVOY_PORT_CONTROL vpc
ON vh.VSLVOY_HEADER_ID = vpc.VSLVOY_HEADER_ID
WHERE NOT EXISTS (
SELECT 1
FROM MG_VSLVOY_SCHEDULE vs
WHERE vs.VSLVOY_SCHEDULE_ID = vpc.VSLVOY_SCHEDULE_ID
AND vs.PORT_CD NOT IN (ncv.POL_LOCATION_CD, ncv.POD_LOCATION_CD)
)
AND BL_ID = '17231410'
Thanks for your reply.
Your query shows the SUM of TEU as NULL, it should be 4. Am I missing something ?