With "Show Pseudo-Code"

SELECT price.col1 AS ...
FROM   table_1 t1 ...
WHERE  col1 = ...

UNION

SELECT   price.col1 AS ...
FROM
         (SELECT store.column1 ...
         FROM
                 (SELECT library.column1 ...
                 FROM
                         (SELECT INTEGER ...
                         FROM
                                 (SELECT  alllibrarysales.column1 ...
                                 FROM
                                          (SELECT libraryprod.column1 ...
                                          FROM
                                                  (SELECT  tv.column1 ...
                                                  FROM     db1.v_table1 tv ...
                                                  WHERE    tv.column1 <> ...
                                                  GROUP BY tv.column1 , ...
                                                  ) AS libraryprod ...
                                          WHERE   th.column1 = ...
                                          ) AS alllibrarysales ...
                                 GROUP BY alllibrarysales.column1 , ...
                                 ) AS onelibrarysales ...
                         ) AS library ...
                         LEFT OUTER JOIN db1.v_table3 librarystat ...
                 ) AS x ...
         ) AS price ...
WHERE    price.column1 < ...
GROUP BY price.column1 , ...

Without "Show Pseudo-Code"

SELECT price.col1       AS col1
     , price.col2       AS col2
     , price.col3       AS col3
     , MAX (price.col4) AS col4
     , MAX (price.col5) AS col5
     , MAX (price.col6) AS col6
     , MAX (price.col7) AS col7
FROM   table_1 t1
     , table_2 t2
WHERE  col1         = col2
   AND column_1     = small_column
   AND column_3411 <= column_12_sup
   AND col1         = 'Test Run'
   AND column_4532  = c1.dert

UNION

SELECT   price.col1       AS col1
       , price.col2       AS col2
       , price.col3       AS col3
       , MAX (price.col4) AS col4
       , MAX (price.col5) AS col5
       , MAX (price.col6) AS col6
       , MAX (price.col7) AS col7
FROM
         (SELECT store.column1
               , CAST (store.column2 AS INTEGER) AS column2
               , store.columnwe34r3              AS column3
               , store.column4_prod              AS column4
               , store.column5_pre_prod_first    AS column5
               , SUBSTR (store.column6,11,1)     AS column6
               , store.column7                   AS column7
         FROM
                 (SELECT library.column1
                       , library.column2
                       , library.column3
                       , CASE library.column4
                                 WHEN cheap
                                 THEN digits (library.column27) concat library.column28
                                 ELSE 123456
                         END AS column4
                       , CASE library.column5
                                 WHEN expensive
                                 THEN digits (library.column27) concat library.column28
                                 ELSE 123456
                         END AS library.column6
                       , CASE column7
                                 WHEN free
                                 THEN digits (library.column27) concat library.column28
                                 ELSE 123456
                         END AS column7
                       ,
                 FROM
                         (SELECT INTEGER (SUBSTR (onelibrarysales.column1,11,10)) AS column1
                               , SUBSTR (onelibrarysales.column2,21,10)           AS column2
                               , onelibrarysales.column3
                               , onelibrarysales.column4
                               , SUBSTR (onelibrarysales.column5,31,6) AS column5
                               , SUBSTR (onelibrarysales.column6,37,2) AS column6
                               , SUBSTR (onelibrarysales.column7,39,6) AS column7
                               ,
                         FROM
                                 (SELECT  alllibrarysales.column1
                                        , alllibrarysales.column2
                                        , MAX (alllibrarysales.column3)                                                                                                                               AS alllibrarysales.column3
                                        , MAX (CHAR (alllibrarysales.column4,iso) concat CHAR (alllibrarysales.column5,iso) concat digits (alllibrarysales.column6) concat (alllibrarysales.column7)) AS column5
                                 FROM
                                          (SELECT libraryprod.column1
                                                , libraryprod.column2
                                                , libraryprod.column3
                                                , libraryprod.column4
                                                , libraryprod.column5
                                                , libraryprod.column6
                                                , libraryprod.column7
                                          FROM
                                                  (SELECT  tv.column1
                                                         , tv.column2
                                                         , MAX (digits (tv.column3) concat digits (tv.column4)) AS librarymax
                                                  FROM     db1.v_table1 tv
                                                  WHERE    tv.column1 <> 'Y'
                                                       AND tv.column1 IN ('a'
                                                                        , '1'
                                                                        , '12'
                                                                        , '123'
                                                                        , ' 1234'
                                                                        , '12345'
                                                                        , '123456'
                                                                        , '1234567'
                                                                        , '12345678'
                                                                        , '123456789'
                                                                        , '1234567890'
                                                                        , '1 12 123 1234 12345 123456 1234567 12345678'
                                                                        , 'b'
                                                                        , 'c')
                                                       AND tv.column2 >= DATE (tv.column4)
                                                       AND tv.column3  < DATE (tv.column15)
                                                  GROUP BY tv.column1
                                                         , tv.column2
                                                  ) AS libraryprod
                                                , db1.table2 th
                                          WHERE   th.column1 = libraryprod.column1
                                              AND th.column2 = libraryprod.column2
                                          ) AS alllibrarysales
                                 GROUP BY alllibrarysales.column1
                                        , alllibrarysales.column2
                                 ) AS onelibrarysales
                         ) AS library
                         LEFT OUTER JOIN db1.v_table3 librarystat
                         ON      librarystat.column1 = library.column1
                             AND librarystat.column2 = library.column2
                              OR
                                 (
                                         librarystat.column4 = library.column4
                                     AND librarystat.column5 = library.column5
                                 )
                             AND
                                 (
                                         librarystat.column5 = 'I'
                                      OR librarystat.column4 = 'Gold'
                                      OR librarystat.column5 = 'Bold'
                                 )
                             AND librarystat.column6 <= 'Z74'
                 ) AS x
         ) AS price
WHERE    price.column1 < 'R45'
      OR
         (
                  price.column2 = 'R46'
              AND price.column3 = 6
         )
GROUP BY price.column1
       , price.column2
       , price.column3
       , price.column4
       , price.column5
       , price.column6
       , price.column7

Edited 1 time(s). Last edit at 03/11/2009 08:43AM by guidomarcel.