The Query From Hell

External Since: Apr 09, 2008 Posts: 576

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";

External Since: Jun 19, 2008 Posts: 4

On Feb 9, 3:01 pm, Madison Pruet wrote:
> Mtek 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......
>
> Were you paying by the job or paying by the number of words? Wink

Definitely php, the "//" signifies that that line is a comment, (this
code is word-wrapped).
PERL comments start with a #.