Re: Is this query done easier?

From: Mtek <mtek_at_mtekusa.com>
Date: Tue, 17 Feb 2009 09:10:39 -0800 (PST)
Message-ID: <a05574f5-cd43-485f-8b23-79f30cc503c6_at_v31g2000vbb.googlegroups.com>

On Feb 17, 11:09 am, Mtek <m..._at_mtekusa.com> wrote:
> We have this query:
>
> SELECT ticker, category_name, fund_name, class_rank, total_return_1yr,
> expense_ratio, market_value_avg, mgr_start_date
> FROM (SELECT pr.ticker, pr.category_name, mf.fund_name,
> fc.class_rank,  pr.total_return_1yr, fm.expense_ratio,
> market_value_avg, mgr_start_date, ROW_NUMBER()
>       OVER (PARTITION BY category_name ORDER BY class_rank) cnt
>       FROM mutual_fund mf, fund_classes fc, performance_rank pr,
> fund_master fm
>       WHERE pr.category_name = 'Foreign'
>         AND pr.ticker = mf.ticker
>         AND mf.ticker = fc.ticker
>         AND mf.m_fund_id = fm.fund_id)
> WHERE cnt <= 4
> ORDER BY class_rank, total_return_1yr DESC, expense_ratio,
> mgr_start_date DESC;
>
> What I need to do is add the information from the data also passed
> in.  something like this:
>
> SELECT ticker, category_name, fund_name, class_rank, total_return_1yr,
> expense_ratio, market_value_avg, mgr_start_date
> FROM (SELECT pr.ticker, pr.category_name, mf.fund_name,
> fc.class_rank,  pr.total_return_1yr, fm.expense_ratio,
> market_value_avg, mgr_start_date, ROW_NUMBER()
>       OVER (PARTITION BY category_name ORDER BY class_rank) cnt
>       FROM mutual_fund mf, fund_classes fc, performance_rank pr,
> fund_master fm
>       WHERE pr.category_name = 'Foreign'
>         AND pr.ticker = mf.ticker
>         AND mf.ticker = fc.ticker
>         AND mf.m_fund_id = fm.fund_id)
> WHERE cnt <= 4
> UNION
> SELECT 'PRMSX', category_name, fund_name, class_rank,
> total_return_1yr, expense_ratio, market_value_avg, mgr_start_date
> FROM (SELECT pr.ticker, pr.category_name, mf.fund_name,
> fc.class_rank,  pr.total_return_1yr, fm.expense_ratio,
> market_value_avg, mgr_start_date, ROW_NUMBER()
>       OVER (PARTITION BY category_name ORDER BY class_rank) cnt
>       FROM mutual_fund mf, fund_classes fc, performance_rank pr,
> fund_master fm
>       WHERE pr.category_name = 'Foreign'
>         AND pr.ticker = mf.ticker
>         AND mf.ticker = fc.ticker
>         AND mf.m_fund_id = fm.fund_id)
> WHERE cnt <= 1
> ORDER BY class_rank, total_return_1yr DESC, expense_ratio,
> mgr_start_date DESC;
>
> Is there an easier way to do this without the UNION?  Something
> shorter?  It is the ORDER BY that seems to cause issues.  It removes
> the passed in data because of the values in the query.  We want 4
> records, PLUS the record passed in.....
>
> Possible in a shorter query?  We're still playing right as we post
> this.
>
> Regards,
>
> Jim

Sorry, just noticed. The category name, 'Foreign', is actually a parameter passed in: ''' || p_desc ||| '''

Thanks! Received on Tue Feb 17 2009 - 11:10:39 CST