SQL Query Text Q1

Q1

SELECT /*+ ORDERED qb_name(outer) */

A0.zuchinis,

A0.brocoli,

C0.Oranges

FROM

(

SELECT /*+ NO_UNNEST */

A1.planted_date,

A1.pears,

A1.zuchinis,

A1.brocoli

FROM

foo.A A1,

(

SELECT /*+ NO_UNNEST */

zuchinis,

brocoli

FROM foo.A A2

WHERE

pears = 'M' AND

planted_date + 0 >= ADD_MONTHS ((SELECT /*+ NO_UNNEST */

MAX (planted_date)

FROM foo.B B1

WHERE

pears = 'M'

),

- 11)

GROUP BY

zuchinis,

brocoli

HAVING COUNT (*) = 12

)

i2

WHERE

A1.planted_date = (SELECT /*+ NO_UNNEST */

MAX (planted_date)

FROM foo.B B2

WHERE

pears = 'M'

) AND

A1.pears = 'M' AND

A1.zuchinis = i2.zuchinis (+) AND

A1.brocoli = i2.brocoli (+)

UNION

SELECT /*+ NO_UNNEST */

A4.planted_date,

A4.pears,

A4.zuchinis,

A4.brocoli

FROM foo.A A4

WHERE

A4.planted_date = TO_DATE ('02/10/2008',

'dd/mm/yyyy') AND

A4.pears = TRIM ('D') AND

A4.green_beans = '1'

AND NOT EXISTS (SELECT /*+ NO_UNNEST */

*

FROM foo.A A5

WHERE

pears = 'M' AND

planted_date = (SELECT /*+ NO_UNNEST */

MAX (planted_date)

FROM foo.B B3

WHERE

pears = 'M'

) AND

A4.zuchinis = A5.zuchinis AND

A4.brocoli = A5.brocoli)

)

B0,

foo.A A0,

foo.C C0,

foo.D D0,

foo.E E0

WHERE

A0.planted_date = TO_DATE ('02/10/2008', 'dd/mm/yyyy') AND

A0.pears = TRIM ('D') AND

A0.green_beans = '1' AND

A0.zuchinis = B0.zuchinis AND

A0.brocoli = B0.brocoli AND

A0.planted_date = C0.planted_date AND

A0.pears = C0.pears AND

A0.zuchinis = C0.zuchinis AND

A0.brocoli = C0.brocoli AND

A0.planted_date = D0.planted_date AND

A0.pears = D0.pears AND

A0.harvest_size = D0.harvest_size AND

C0.Oranges = D0.Oranges AND

C0.apples = D0.apples AND

(D0.lemons = 0 OR

D0.lemons IS NULL) AND

A0.planted_date = E0.planted_date AND

A0.pears = E0.pears AND

A0.harvest_size = E0.harvest_size AND

C0.Oranges = E0.Oranges AND

C0.apples = E0.apples AND

(E0.lemons = 0 OR

E0.lemons IS NULL)

ORDER BY

A0.zuchinis,

A0.brocoli