Re: Is this query done easier?

From: Jimbo1 <jamestheboarder_at_googlemail.com>
Date: Wed, 18 Feb 2009 17:54:52 -0800 (PST)
Message-ID: <4f6dd807-da05-402f-8937-886375527e21_at_h20g2000yqn.googlegroups.com>

On Feb 18, 4:43 pm, Mtek <m..._at_mtekusa.com> wrote:
> On Feb 17, 5:31 pm, Jimbo1 <jamestheboar..._at_googlemail.com> wrote:
>
>
>
> > 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:2...
>
> > Also, if you're using the above query in a PL/SQL program, is there
> > any reason why it shouldn't be static?
>
> I thought that bind variables meant you were using the EXECUTE
> IMMEDIATE?  This procedure ends with:  OPEN v_line FOR v_query.  From
> there our PHP scripts read the record sets.
>
> Can I still use bind variables there?

Sorry, just re-read you post and realised I missed somthing, i.e.

> This procedure ends with: OPEN v_line FOR v_query. From
> there our PHP scripts read the record sets.

So, am I right in thinking you're passing the query string to a PL/SQL stored procedure, where it is then dynamically executed and returns the results set?

If so, that procedure really needs a minor rewrite:

1. place the string ' WHERE pr.category_name = :p_desc' in your query, rather than concatenate the actual value of p_desc.
2. Modify the PL/SQL procedure to accept an additional parameter that you can use to pass the p_desc value to it. Name the parameter "p_desc" or something like that. ;)
3. Use the following code in your stored procedure:

OPEN v_line FOR v_query USING p_desc; -- where p_desc is the name of the new parameter in the PL/SQL stored procedure.

If you can't make the code change, whoever does your PL/SQL development really NEEDs to do this; that is if you want to avoid potential performance and security issues all rolled into one..... Received on Wed Feb 18 2009 - 19:54:52 CST