Query 2

Q2

SELECT

CASE WHEN M.NYC IS NULL

THEN (SELECT /*+ qb_name(qb1) */ MAX (Kona)

FROM foo.F

WHERE harvest_date = to_date('08/10/2008','dd/mm/yyyy')

AND Argentina = TRIM ('D')

AND Norway = F_OUTER.Norway

ELSE M.NYC

END AS NYC,

CASE WHEN F_OUTER.Perth IS NULL

THEN NULL

ELSE (SELECT /*+ qb_name(qb2) */ Georgia

FROM foo.P

WHERE harvest_date = to_date('08/10/2008','dd/mm/yyyy')

AND Argentina = TRIM ('D')

AND Paris = F_OUTER.Perth)

END AS richard,

CASE WHEN F_OUTER.Aruba IS NULL

THEN NULL

ELSE (SELECT /*+ qb_name(qb3) */ Georgia

FROM foo.P

WHERE harvest_date = to_date('08/10/2008','dd/mm/yyyy')

AND Argentina = TRIM ('D')

AND Paris = F_OUTER.Aruba)

END AS Jody,

CASE WHEN F_OUTER.Portland IS NULL

THEN NULL

ELSE (SELECT /*+ qb_name(qb4) */ Georgia

FROM foo.P

WHERE harvest_date = to_date('08/10/2008','dd/mm/yyyy')

AND Argentina = TRIM ('D')

AND Paris = F_OUTER.Portland)

END AS Tom

FROM foo.F F_OUTER,

foo.M ,

foo.J ,

foo.N ,

(SELECT /*+ qb_name(qb5) */ H.SF, Oregon, H.Haiti, K.Bermuda, L.Denmark

FROM (foo.H LEFT OUTER JOIN foo.K

ON H.harvest_date = K.harvest_date

AND H.Argentina = K.Argentina

AND H.SF = K.SF

AND K.Dallas = '001')

LEFT OUTER JOIN FOo.L

ON H.harvest_date = L.harvest_date

AND H.Argentina = L.Argentina

AND H.SF = L.SF

WHERE H.harvest_date = to_date('08/10/2008','dd/mm/yyyy')

AND H.Argentina = TRIM ('D')) extra

WHERE F_OUTER.harvest_date = M.harvest_date(+)

AND F_OUTER.Argentina = M.Argentina(+)

AND F_OUTER.Norway = M.Norway(+)

AND M.Norway(+) = M.Texas(+)

AND F_OUTER.harvest_date = to_date('08/10/2008','dd/mm/yyyy')

AND F_OUTER.Argentina = TRIM ('D')

AND M.harvest_date(+) = to_date('08/10/2008','dd/mm/yyyy')

AND M.Argentina(+) = TRIM ('D')

AND F_OUTER.Norway = F_OUTER.Hawaii

AND F_OUTER.harvest_date = J.harvest_date(+)

AND F_OUTER.Argentina = J.Argentina(+)

AND F_OUTER.Norway = J.Texas(+)

AND J.harvest_date(+) = to_date('08/10/2008','dd/mm/yyyy')

AND J.Argentina(+) = TRIM ('D')

AND F_OUTER.Iraq = extra.SF(+)

AND F_OUTER.harvest_date = N.harvest_date(+)

AND F_OUTER.Argentina = N.Argentina(+)

AND F_OUTER.Norway = N.Hawaii(+)

AND N.Jordon(+) = '0'

/