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........

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

Original text of this message