Another rotten query

From: Mtek <mtek_at_mtekusa.com>
Date: Thu, 19 Feb 2009 12:55:24 -0800 (PST)
Message-ID: <212b5914-58c0-4eaa-bfc2-e53bdc38dd05_at_w1g2000prm.googlegroups.com>


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........ Received on Thu Feb 19 2009 - 14:55:24 CST

Original text of this message