Re: Another rotten query
From: hpuxrac <johnbhurley_at_sbcglobal.net>
Date: Thu, 19 Feb 2009 14:25:36 -0800 (PST)
Message-ID: <980927e9-1cb7-479f-a224-acec924451ad_at_w24g2000prd.googlegroups.com>
On Feb 19, 3:55 pm, Mtek <m..._at_mtekusa.com> wrote:
> I can do this, probably the long way with PLSQL code. I was just
> seeing if someone knew of a better way.
>
> Here is our query:
>
> v_select :=
> 'SELECT my_left_table.fund_name,
> my_left_table.ticker,
> DECODE(fund_rank.curr_rank,NULL,'NA',fund_rank.curr_rank)
> AS fund_rank,
> my_left_table.total_return_ytd,
> my_left_table.total_return_1mt,
> my_left_table.total_return_3mt,
> my_left_table.total_return_1yr,
> my_left_table.total_return_3yr,
> my_left_table.total_return_5yr,
> my_left_table.total_return_10yr
> FROM (SELECT mf.fund_name,
> mf.ticker,
> NVL(fm.total_return_ytd,-9999) AS total_return_ytd,
> NVL(fm.total_return_1mt,-9999) AS total_return_1mt,
> NVL(fm.total_return_3mt,-9999) AS total_return_3mt,
> NVL(fm.total_return_1yr,-9999) AS total_return_1yr,
> NVL(fm.total_return_3yr,-9999) AS total_return_3yr,
> NVL(fm.total_return_5yr,-9999) AS total_return_5yr,
> NVL(fm.total_return_10yr,-9999) AS total_return_10yr
> FROM mutual_fund mf, fund_master fm, fund_daily_prices dp
> WHERE mf.ticker = dp.ticker
> AND mf.m_fund_id = fm.fund_id
> AND mf.ticker IN ' || v_tickers || ') my_left_table ,
> fund_classes fc
> WHERE my_left_table.ticker = fc.ticker(+)
> ORDER BY class_rank, total_return_1yr DESC, expense_ratio,
> mgr_start_date DESC';
>
> Now, the v_tickers will be passed in. It will contain 1 to 5 values.
> The customer wants more now.
> For each ticker, we will look up what CATEGORY it belongs to in
> another table. We will then find the average for all the tickers in
> that category for the given column and compare it against the value in
> the query and rank it as higher or lower than the value.
>
> So, let's say I have IBM which belongs to a CATEGORY called TECH and
> has a total_return_ytd value of 100. I sum all TECH total_return_ytd
> and get the average. Then I compare it with the total_return_ytd for
> IBM and give it a 'Below' or 'Above' literal.
>
> I need to do that for each column, for each ticker. Does all that
> make any sense? If ticker #2 is XYZ and belongs to a CATEGORY of
> GROWTH and have a total_return_10yr of 1000, I need to do the same for
> all GROWTH category, find the average and compare it to the value for
> XYZ, giving it the 'Above' or 'Below'.
>
> My guess is serious PLSQL code and dynamically putting together some
> type of query........
Date: Thu, 19 Feb 2009 14:25:36 -0800 (PST)
Message-ID: <980927e9-1cb7-479f-a224-acec924451ad_at_w24g2000prd.googlegroups.com>
On Feb 19, 3:55 pm, Mtek <m..._at_mtekusa.com> wrote:
> I can do this, probably the long way with PLSQL code. I was just
> seeing if someone knew of a better way.
>
> Here is our query:
>
> v_select :=
> 'SELECT my_left_table.fund_name,
> my_left_table.ticker,
> DECODE(fund_rank.curr_rank,NULL,'NA',fund_rank.curr_rank)
> AS fund_rank,
> my_left_table.total_return_ytd,
> my_left_table.total_return_1mt,
> my_left_table.total_return_3mt,
> my_left_table.total_return_1yr,
> my_left_table.total_return_3yr,
> my_left_table.total_return_5yr,
> my_left_table.total_return_10yr
> FROM (SELECT mf.fund_name,
> mf.ticker,
> NVL(fm.total_return_ytd,-9999) AS total_return_ytd,
> NVL(fm.total_return_1mt,-9999) AS total_return_1mt,
> NVL(fm.total_return_3mt,-9999) AS total_return_3mt,
> NVL(fm.total_return_1yr,-9999) AS total_return_1yr,
> NVL(fm.total_return_3yr,-9999) AS total_return_3yr,
> NVL(fm.total_return_5yr,-9999) AS total_return_5yr,
> NVL(fm.total_return_10yr,-9999) AS total_return_10yr
> FROM mutual_fund mf, fund_master fm, fund_daily_prices dp
> WHERE mf.ticker = dp.ticker
> AND mf.m_fund_id = fm.fund_id
> AND mf.ticker IN ' || v_tickers || ') my_left_table ,
> fund_classes fc
> WHERE my_left_table.ticker = fc.ticker(+)
> ORDER BY class_rank, total_return_1yr DESC, expense_ratio,
> mgr_start_date DESC';
>
> Now, the v_tickers will be passed in. It will contain 1 to 5 values.
> The customer wants more now.
> For each ticker, we will look up what CATEGORY it belongs to in
> another table. We will then find the average for all the tickers in
> that category for the given column and compare it against the value in
> the query and rank it as higher or lower than the value.
>
> So, let's say I have IBM which belongs to a CATEGORY called TECH and
> has a total_return_ytd value of 100. I sum all TECH total_return_ytd
> and get the average. Then I compare it with the total_return_ytd for
> IBM and give it a 'Below' or 'Above' literal.
>
> I need to do that for each column, for each ticker. Does all that
> make any sense? If ticker #2 is XYZ and belongs to a CATEGORY of
> GROWTH and have a total_return_10yr of 1000, I need to do the same for
> all GROWTH category, find the average and compare it to the value for
> XYZ, giving it the 'Above' or 'Below'.
>
> My guess is serious PLSQL code and dynamically putting together some
> type of query........
Sounds doable in a single query ( at least to me ). What version of oracle exactly?
What does your new and improved single query version look like ... Received on Thu Feb 19 2009 - 16:25:36 CST