Re: The Query From Hell

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Mon, 09 Feb 2009 20:11:43 +0100
Message-ID: <49907ff2$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://URL HERE?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://URL HERE?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://URL HERE?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";
>

  And again, posted to more than one news group. Stop cross posting PLEASE.

Shakespeare Received on Mon Feb 09 2009 - 13:11:43 CST