The Query From Hell
From: Mtek <mtek_at_mtekusa.com>
Date: Mon, 9 Feb 2009 07:36:10 -0800 (PST)
Message-ID: <2f960ae8-82f4-48da-9afd-9ec6fc9bd771_at_t39g2000prh.googlegroups.com>
('<a href=\"http://URL HERE?t='|| MUTUAL_FUND.TICKER||'&type=main\">'||MUTUAL_FUND.TICKER||'</a>') AS LINK
$getQuery="
('<a href=\"http://URL HERE?t='|| MUTUAL_FUND.TICKER||'&type=main\">'||MUTUAL_FUND.TICKER||'</a>') AS LINK
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,
Date: Mon, 9 Feb 2009 07:36:10 -0800 (PST)
Message-ID: <2f960ae8-82f4-48da-9afd-9ec6fc9bd771_at_t39g2000prh.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_charELSE TO_DATE( TO_CHAR( '01' || '/'||fund_master.mgr_start_date),'dd-mm- RRRR' ) END AS MGR_START_DATE,
(-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
('<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_charELSE TO_DATE( TO_CHAR( '01' || '/'||fund_master.mgr_start_date),'dd-mm- RRRR' ) END AS MGR_START_DATE,
(-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
('<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_charReceived on Mon Feb 09 2009 - 09:36:10 CST
(-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";