Hi,
I've a query that SELECTS all Carriers that have the Ancillary_name of 'REFRIGIRATION' or 'DANGEROUS_GOODS':
SELECT c.code, c.NAME, c.carrier_type_flag, c.org_id, a.csl_id,
a.service_level,
DECODE('E', 'F',NVL(a.service_name_fre,a.service_name_eng),
a.service_name_eng) service_name,
TO_CHAR (NVL (b.charge, 0), '$9,999,999.99') charge1,
DECODE (b.csl_id, NULL, 2, 1) sort, NVL(b.charge,0) charge, b.tariff_comment,
b.delivery_days, b.guaranteed, a.nmso, a.phone_number
FROM org c, (SELECT *
FROM csl
WHERE status = 'A'
AND ('N' = 'N' OR
dg_charge IS NOT NULL)) a, ctt b,
csl_ancillary csl_a
WHERE c.oty_code_org = 'CAR'
AND c.status = 'A'
AND c.transport_type = 'S'
AND c.org_id = a.org_id
AND a.csl_id = b.csl_id(+)
AND csl_a.ANCILLARY_NAME IN ('REFRIGIRATION', 'DANGEROUS GOODS')
AND a.csl_id = csl_a.CSL_ID
ORDER BY sort, charge, c.code;
But I really what the CARRIERS that HAVE BOTH Ancillary_name of 'REFRIGIRATION' AND 'DANGEROUS_GOODS'. I used an IN statement, but that implies either 'REFRIGIRATION' OR 'DANGEROUS GOODS'
How would I write the query to SELECT on carriers that have Ancillary_name of 'REFRIGIRATION' and 'DANGEROUS GOODS' ???
Thanks in advance...
Marc L