Re: Is this query done easier?

From: Jimbo1 <jamestheboarder_at_googlemail.com>
Date: Tue, 17 Feb 2009 15:31:05 -0800 (PST)
Message-ID: <dfc18ccf-cd65-4ebc-970e-c9f475e3c255_at_j39g2000yqn.googlegroups.com>

On Feb 17, 5:10 pm, Mtek <m..._at_mtekusa.com> wrote:
> 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!

In a nutshell, if I've read your query correctly, yes there is:

SELECT CASE

          WHEN fn.cnt <= 1 THEN 'PRMSX'
          ELSE fn.cntfn.ticker
       END AS ticker,
       fn.category_name,
       fn.fund_name,
       fn.class_rank,
       fn.total_return_1yr,
       fn.expense_ratio,
       fn.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 = :p_desc
            AND pr.ticker = mf.ticker
            AND mf.ticker = fc.ticker
            AND mf.m_fund_id = fm.fund_id
       ) fn

 WHERE cnt <= 4
 ORDER BY class_rank,

          total_return_1yr DESC,
          expense_ratio,
          mgr_start_date DESC;

Just one thing though, and that is you said you were passing in the p_desc description in a manner that implied you're building this query dynamically via string concatenation, i.e. ''' || p_desc ||| '''

If you ARE doing that, that is extremely bad practice. If the value of "p_desc" changes a lot, then Oracle will have to hard-parse the query every time the p_desc value changes. If it changes often, that could start to have a negative impact upon the performance of your database.

Additionally, assembling dynamically executed queries by string concatenation will potentially leave you vulnerable to SQL Injection attacks.

If my assumption is correct, you need to take a look at how to use bind variables (such as :p_desc) in your dynamic SQL statement, and then bind the actual value to the parameter at runtime. See: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2320123769177

Also, if you're using the above query in a PL/SQL program, is there any reason why it shouldn't be static? Received on Tue Feb 17 2009 - 17:31:05 CST