The Query From Hell

From: Mtek <mtek_at_mtekusa.com>
Date: Mon, 9 Feb 2009 07:21:16 -0800 (PST)
Message-ID: <632f7478-c009-4eca-9272-877c17069b0c_at_w1g2000prk.googlegroups.com>


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://www.zacks.com/funds/mfrank/quotes.php? 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://www.zacks.com/funds/mfrank/ quotes.php?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://www.zacks.com/funds/mfrank/quotes.php?
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";
Received on Mon Feb 09 2009 - 09:21:16 CST

Original text of this message