Re: The Query From Hell
From: Mtek <mtek_at_mtekusa.com>
Date: Mon, 9 Feb 2009 10:24:10 -0800 (PST)
Message-ID: <ce8f7cb4-8b2f-43e3-bc58-0880d1765f2e_at_v18g2000pro.googlegroups.com>
On Feb 9, 11:30 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Feb 9, 10:36 am, Mtek <m..._at_mtekusa.com> 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......
>
> > 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://URLHERE?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://URLHERE?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://URLHERE?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";
>
> You make no mention of there being a valid output or performance
> issues with the code so if the code in fact produces the correct
> output and does so in a timely fashion why would want to change it?
>
> The fact that the query seems complicated may have more to do with the
> required result set and how the data is stored and the desire to
> return the data via a single set of queries rather than represent any
> flaw in the design of the SQL itself.
>
> IMHO -- Mark D Powell --
Date: Mon, 9 Feb 2009 10:24:10 -0800 (PST)
Message-ID: <ce8f7cb4-8b2f-43e3-bc58-0880d1765f2e_at_v18g2000pro.googlegroups.com>
On Feb 9, 11:30 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Feb 9, 10:36 am, Mtek <m..._at_mtekusa.com> 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......
>
> > 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://URLHERE?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://URLHERE?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://URLHERE?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";
>
> You make no mention of there being a valid output or performance
> issues with the code so if the code in fact produces the correct
> output and does so in a timely fashion why would want to change it?
>
> The fact that the query seems complicated may have more to do with the
> required result set and how the data is stored and the desire to
> return the data via a single set of queries rather than represent any
> flaw in the design of the SQL itself.
>
> IMHO -- Mark D Powell --
Biggest reason is that the place has a 'no hard coding of queries in PHP' rule. So, this needs to come out of PHP and go into a stored procedure..... Received on Mon Feb 09 2009 - 12:24:10 CST