Re: The Query From Hell
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Mon, 09 Feb 2009 20:03:51 +0100
Message-ID: <49907e1b$0$182$e4fe514c_at_news.xs4all.nl>
Mtek schreef:
> 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";
Date: Mon, 09 Feb 2009 20:03:51 +0100
Message-ID: <49907e1b$0$182$e4fe514c_at_news.xs4all.nl>
Mtek schreef:
> 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";
As far as I can see (apart from the size of this query) there is only
one small error in it (apart from some lingual errors), but I'm sure you
have found that one already!
Looks like they tried to put a complete spreadsheet in one query!
Shakespeare Received on Mon Feb 09 2009 - 13:03:51 CST