Re: Another rotten query
From: Mtek <mtek_at_mtekusa.com>
Date: Fri, 20 Feb 2009 06:11:04 -0800 (PST)
Message-ID: <bf6c6df3-1112-4be0-9fb2-dc98893a2d08_at_k36g2000pri.googlegroups.com>
On Feb 19, 4:25 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> 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 ...
Date: Fri, 20 Feb 2009 06:11:04 -0800 (PST)
Message-ID: <bf6c6df3-1112-4be0-9fb2-dc98893a2d08_at_k36g2000pri.googlegroups.com>
On Feb 19, 4:25 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> 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 ...
Hpuxrac,
We are on 10gR2. I was thinking about some analytical queries and such.......
Or maybe I'd be better off calculating all the values first, storing them in an array or table or collection of some type, and then using that along with the dynamic query...... Received on Fri Feb 20 2009 - 08:11:04 CST