Re: Is this query done easier?
From: Mtek <mtek_at_mtekusa.com>
Date: Wed, 18 Feb 2009 08:43:42 -0800 (PST)
Message-ID: <f221ab7a-6383-406b-aa3f-77c029af7ffa_at_n10g2000vbl.googlegroups.com>
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?
Date: Wed, 18 Feb 2009 08:43:42 -0800 (PST)
Message-ID: <f221ab7a-6383-406b-aa3f-77c029af7ffa_at_n10g2000vbl.googlegroups.com>
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? Received on Wed Feb 18 2009 - 10:43:42 CST