Re: The Query From Hell

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Mon, 9 Feb 2009 09:30:25 -0800 (PST)
Message-ID: <18064193-8160-4312-afbb-4e546a299322_at_f3g2000yqf.googlegroups.com>

On Feb 9, 10:36 am, Mtek <m..._at_mtekusa.com> wrote:
> Hi,
>
> Well, I just thought I'd share this junk with everyone.  We hired a
> consulting firm to assist on a project.  And, well, this is one of the
> queries they came up with, which we intend to re-write, once we figure
> out what the hell it is doing......
>
> if($compareType=='c') {
>   $inQuery="'".$strTicker1."', '".$strTicker2."', '".$strTicker3."',
> '".$strTicker4."', '".$strTicker5."'";
>   $getQuery="
>      select my_left_table.fund_name,
>             my_left_table.LINK,
>             DECODE(fund_rank.curr_rank,NULL,'N/A',fund_rank.curr_rank)
> AS fund_rank,
>             my_left_table.nav,
>             my_left_table.TOTAL_RETURN_1YR,
>             my_left_table.EXPENSE_RATIO,
>             my_left_table.MARKET_VALUE_AVG,
>             my_left_table.MGR_START_DATE
>      from (
>         SELECT MUTUAL_FUND.FUND_NAME,
>                MUTUAL_FUND.TICKER,
>                to_char( decode( FUND_DAILY_PRICES.NAV,to_char
> (-9999),'N/A',FUND_DAILY_PRICES.NAV ) ) AS NAV,
>                to_char( decode( FUND_MASTER.TOTAL_RETURN_1YR,to_char
> (-9999),'N/A',FUND_MASTER.TOTAL_RETURN_1YR ) ) AS TOTAL_RETURN_1YR,
>                to_char( decode( FUND_MASTER.EXPENSE_RATIO,to_char
> (-9999),'N/A',FUND_MASTER.EXPENSE_RATIO ) ) AS EXPENSE_RATIO,
>                to_char( decode( FUND_MASTER.MARKET_VALUE_AVG,to_char
> (-9999),'N/A',FUND_MASTER.MARKET_VALUE_AVG ) ) AS MARKET_VALUE_AVG,
>                CASE WHEN fund_master.mgr_start_date IS NULL THEN NULL
> ELSE TO_DATE( TO_CHAR( '01' || '/'||fund_master.mgr_start_date),'dd-mm-
> RRRR' ) END AS MGR_START_DATE,
>               ('<a href=\"http://URLHERE?t='||
> MUTUAL_FUND.TICKER||'&type=main\">'||MUTUAL_FUND.TICKER||'</a>') AS
> LINK
>         FROM MUTUAL_FUND, FUND_MASTER, FUND_DAILY_PRICES
>         where MUTUAL_FUND.TICKER = FUND_DAILY_PRICES.TICKER
>           AND MUTUAL_FUND.M_FUND_ID = FUND_MASTER.FUND_ID
>           AND MUTUAL_FUND.TICKER IN( ".$inQuery." ))
>         my_left_table left join fund_rank
>        on my_left_table.ticker = fund_rank.ticker";} else {
>
>   // Ashis :           "$getQuery" problem in to_date(to_char('01' ||
> '-'||FUND_MASTER.MGR_START_DATE),'dd-mm-RRRR') function. when
> FUND_MASTER.MGR_START_DATE value is blank then query not execute. This
> causes we are added NVL function.
>   $getQuery="
>      select my_left_table.fund_name,
>             my_left_table.LINK,
>             DECODE(fund_rank.curr_rank,NULL,'N/A',fund_rank.curr_rank)
> AS fund_rank,
>             my_left_table.nav,
>             my_left_table.TOTAL_RETURN_1YR,
>             my_left_table.EXPENSE_RATIO,
>             my_left_table.MARKET_VALUE_AVG,
>             my_left_table.MGR_START_DATE
>      from (
>         SELECT MUTUAL_FUND.FUND_NAME,
>                MUTUAL_FUND.TICKER,
>                to_char( decode( FUND_DAILY_PRICES.NAV,to_char
> (-9999),'N/A',FUND_DAILY_PRICES.NAV ) ) AS NAV,
>                to_char( decode( FUND_MASTER.TOTAL_RETURN_1YR,to_char
> (-9999),'N/A',FUND_MASTER.TOTAL_RETURN_1YR ) ) AS  TOTAL_RETURN_1YR,
>                to_char( decode( FUND_MASTER.EXPENSE_RATIO,to_char
> (-9999),'N/A',FUND_MASTER.EXPENSE_RATIO ) ) AS   EXPENSE_RATIO,
>                to_char( decode( FUND_MASTER.MARKET_VALUE_AVG,to_char
> (-9999),'N/A',FUND_MASTER.MARKET_VALUE_AVG ) ) AS   MARKET_VALUE_AVG,
>                CASE WHEN fund_master.mgr_start_date IS NULL THEN NULL
> ELSE TO_DATE( TO_CHAR( '01' || '/'||fund_master.mgr_start_date),'dd-mm-
> RRRR' ) END AS MGR_START_DATE,
>                ('<a href=\"http://URLHERE?t='||
> MUTUAL_FUND.TICKER||'&type=main\">'||MUTUAL_FUND.TICKER||'</a>') AS
> LINK
>         FROM MUTUAL_FUND,
>              FUND_MASTER,
>              FUND_DAILY_PRICES
>         where MUTUAL_FUND.TICKER = FUND_DAILY_PRICES.TICKER
>           AND MUTUAL_FUND.M_FUND_ID = FUND_MASTER.FUND_ID
>           AND MUTUAL_FUND.TICKER = '".$strTicker1."')
>         my_left_table left join fund_rank
>         on my_left_table.ticker = fund_rank.ticker
>      UNION ALL
>      SELECT FUND_NAME,
>             ('<a href=\"http://URLHERE?t='||TICKER||'&type=main\">'||
> TICKER||'</a>'),
>             DECODE(CURR_RANK,NULL,'N/A',CURR_RANK) AS fund,
>             to_char(NAV),
>             to_char(TOTAL_RETURN_1YR),
>             to_char(EXPENSE_RATIO),
>             to_char(MARKET_VALUE_AVG),
>             m
>      FROM (
>         SELECT YTD_TICKERS.*,
>                FUND_MASTER.TOTAL_RETURN_YTD,
>                row_number() over( order by
> FUND_MASTER.TOTAL_RETURN_YTD desc ) as ytd_desc
>         FROM (
>           SELECT a.*,
>                  row_number() over( order by to_date(m,'dd-mm-RRRR') )
> as m_Asc
>           from (
>              SELECT MGR_TICKERS.*,
>                     to_date(to_char('01' || '-'||NVL
> (FUND_MASTER.MGR_START_DATE, '05/08')),'dd-mm-RRRR') as m
>              FROM (
>                SELECT *
>                FROM (
>                  SELECT MUTUAL_FUND.FUND_NAME,
>                         MUTUAL_FUND.TICKER,
>                         FUND_RANK.CURR_RANK,
>                         decode(FUND_DAILY_PRICES.NAV,to_char(-9999),'N/
> A',FUND_DAILY_PRICES.NAV) AS
> NAV,
> decode(FUND_MASTER.MARKET_VALUE_AVG,to_char(-9999),'N/
> A',FUND_MASTER.MARKET_VALUE_AVG) AS
> MARKET_VALUE_AVG,
> decode(FUND_MASTER.TOTAL_RETURN_1MT,to_char(-9999),'N/
> A',FUND_MASTER.TOTAL_RETURN_1MT) AS TOTAL_RETURN_1MT,
>                         decode(FUND_MASTER.TOTAL_RETURN_3MT,to_char
> (-9999),'N/A',FUND_MASTER.TOTAL_RETURN_3MT) AS TOTAL_RETURN_3MT,
>                         decode(FUND_MASTER.TOTAL_RETURN_1YR,to_char
> (-9999),'N/A',FUND_MASTER.TOTAL_RETURN_1YR) AS TOTAL_RETURN_1YR,
>                         decode(FUND_MASTER.TOTAL_RETURN_3YR,to_char
> (-9999),'N/A',FUND_MASTER.TOTAL_RETURN_3YR) AS TOTAL_RETURN_3YR,
>                         decode(FUND_MASTER.TOTAL_RETURN_5YR,to_char
> (-9999),'N/A',FUND_MASTER.TOTAL_RETURN_5YR) AS TOTAL_RETURN_5YR,
>                         decode(FUND_MASTER.TOTAL_RETURN_10YR,to_char
> (-9999),'N/A',FUND_MASTER.TOTAL_RETURN_10YR) AS TOTAL_RETURN_10YR,
>                         decode(FUND_MASTER.STD_DEV_3YR,to_char
> (-9999),'N/A',FUND_MASTER.STD_DEV_3YR) AS STD_DEV_3YR,
>                         decode(FUND_MASTER.SHARPE_3YR,to_char
> (-9999),'N/A',FUND_MASTER.SHARPE_3YR) AS SHARPE_3YR,
>                         decode(FUND_MASTER.R_SQUARE_3YR,to_char
> (-9999),'N/A',FUND_MASTER.R_SQUARE_3YR) AS R_SQUARE_3YR,
>                         decode(FUND_MASTER.BETA_3YR,to_char(-9999),'N/
> A',FUND_MASTER.BETA_3YR) AS BETA_3YR,
>                         decode(FUND_MASTER.ALPHA_3YR,to_char(-9999),'N/
> A',FUND_MASTER.ALPHA_3YR) AS ALPHA_3YR,
>                         decode(FUND_MASTER.FEE_12B1_PERC,to_char
> (-9999),'N/A',FUND_MASTER.FEE_12B1_PERC) AS FEE_12B1_PERC,
>                         decode(FUND_MASTER.MIN_INIT_INVEST,to_char
> (-9999),'N/A',FUND_MASTER.MIN_INIT_INVEST) AS MIN_INIT_INVEST,
>                         decode(FUND_MASTER.MIN_SUBS_INVEST,to_char
> (-9999),'N/A',FUND_MASTER.MIN_SUBS_INVEST) AS MIN_SUBS_INVEST,
>                         decode(FUND_MASTER.PORTFOLIO_TURNOVER,to_char
> (-9999),'N/A',FUND_MASTER.PORTFOLIO_TURNOVER) AS PORTFOLIO_TURNOVER ,
>                         decode(FUND_MASTER.LARGE_GROWTH,to_char
> (-9999),'N/A',FUND_MASTER.LARGE_GROWTH) AS LARGE_GROWTH,
>                         decode(FUND_MASTER.LARGE_VALUE,to_char
> (-9999),'N/A',FUND_MASTER.LARGE_VALUE) AS LARGE_VALUE,
>                         decode(FUND_MASTER.SMALL_GROWTH,to_char
> (-9999),'N/A',FUND_MASTER.SMALL_GROWTH) AS SMALL_GROWTH,
>                         decode(FUND_MASTER.SMALL_VALUE,to_char
> (-9999),'N/A',FUND_MASTER.SMALL_VALUE) AS SMALL_VALUE,
>                         FUND_MASTER.EXPENSE_RATIO,
>                         row_number() over(order by EXPENSE_RATIO) as
> r_exp
>                  FROM MUTUAL_FUND,
>                       FUND_MASTER,
>                       FUND_RANK,
>                       FUND_DAILY_PRICES
>                  WHERE FUND_RANK.CURR_RANK in ".$rank."
>                    AND FUND_MASTER.OBJ_DESCR = '".$arrgetcat[0][0]."'
>                    AND MUTUAL_FUND.TICKER = FUND_RANK.TICKER
>                    AND MUTUAL_FUND.TICKER = FUND_DAILY_PRICES.TICKER
>                    AND MUTUAL_FUND.M_FUND_ID = FUND_MASTER.FUND_ID
>                    AND FUND_MASTER.MGR_START_DATE <> '-9999'
>                    AND FUND_MASTER.EXPENSE_RATIO <> -9999
>                    AND MUTUAL_FUND.TICKER <> '".$strTicker1."')
> EXP_TICKERS
>                WHERE r_exp <= 20) MGR_TICKERS,
>                                   MUTUAL_FUND,
>                                   FUND_MASTER
>              WHERE MGR_TICKERS.TICKER = MUTUAL_FUND.TICKER
>                AND MUTUAL_FUND.M_FUND_ID = FUND_MASTER.FUND_ID) a)
>           YTD_TICKERS,
>           MUTUAL_FUND,
>           FUND_MASTER
>         WHERE YTD_TICKERS.TICKER = MUTUAL_FUND.TICKER
>           AND MUTUAL_FUND.M_FUND_ID = FUND_MASTER.FUND_ID
>           AND m_asc <= 10) top4tickers
>      WHERE ytd_desc <= 4";

You make no mention of there being a valid output or performance issues with the code so if the code in fact produces the correct output and does so in a timely fashion why would want to change it?

The fact that the query seems complicated may have more to do with the required result set and how the data is stored and the desire to return the data via a single set of queries rather than represent any flaw in the design of the SQL itself.

IMHO -- Mark D Powell -- Received on Mon Feb 09 2009 - 11:30:25 CST