OraFAQ Forum: SQL & PL/SQL » Run query based on parameter

Home » SQL & PL/SQL » SQL & PL/SQL » Run query based on parameter (Oracele)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Run query based on parameter [message #396737]

Tue, 07 April 2009 22:19

smittal17
Messages: 7
Registered: March 2009

Junior Member

Hi,

I have to run query in which select parameters are same but only one condition differs which is based on parameter.If the parameter is "ALL" then I have to run query for all values but if it's a particular "Name" then run query based on that.

So is there any way of identifying parameter in query,Something like case can help achieving this ?Plz help.

My Schema log is

ID NUMBER(2),
NAME VARCHAR2(60),
ALARMRAISED DATE,
ALARMCLEARED DATE

SO,I have to run query,if it is "ALL"

select * from log ;

if it is some name then

selct * from log where name='abc';

How should I achieve this in one ?
Shilpa

Report message to a moderator

Re: Run query based on parameter [message #396738 is a reply to message #396737]

Tue, 07 April 2009 22:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I see many words; all of which I recognize.
However I don't understand what they mean.

>I have to run query in which select parameters are same but only one condition differs which is based on parameter.

It is not clear from where the parameter originates or how it gets included to the SQL.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

Report message to a moderator

Re: Run query based on parameter [message #396744 is a reply to message #396737]

Tue, 07 April 2009 22:57

smittal17
Messages: 7
Registered: March 2009

Junior Member

The value of parameter is selected by USER ,it can be "All" or any name .The query is any simple query only the search criteria is different.The point is that both of the things can be done through one query?

Report message to a moderator

Re: Run query based on parameter [message #396745 is a reply to message #396737]

Tue, 07 April 2009 23:10

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>The value of parameter is selected by USER ,it can be "All" or any name .
Is this parameter found in a closet, under a rock, stored in an environmental variable, stored in a file or floating down from the sky?

You've offered no foundation to understand your environment.

>The query is any simple query only the search criteria is different.
So you need to decide how you wish to solve this challenge.

>The point is that both of the things can be done through one query?
Yes, but not the same SQL.
You either need to dynamically create the desired SQL;
which does not scale well at all.
Or it can be done via CASE.

Pick your poison.

Report message to a moderator

Re: Run query based on parameter [message #397272 is a reply to message #396744]

Thu, 09 April 2009 14:04

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

Here is an example using the dept table and passing a parameter through a procedure. You can also do the same thing with a SQL*Plus substitution variable and just the select statement.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE your_procedure
  2    (p_name_parameter IN  VARCHAR2,
  3  	p_refcursor	 OUT SYS_REFCURSOR)
  4  AS
  5  BEGIN
  6    OPEN p_refcursor FOR
  7    SELECT * FROM dept
  8    WHERE  dname = DECODE (p_name_parameter, 'ALL', dname, p_name_parameter);
  9  END your_procedure;
 10  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> VARIABLE g_refcursor REFCURSOR
SCOTT@orcl_11g> SET AUTOPRINT ON
SCOTT@orcl_11g> EXEC your_procedure ('SALES', :g_refcursor)

PL/SQL procedure successfully completed.


    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

SCOTT@orcl_11g> EXEC your_procedure ('ALL', :g_refcursor)

PL/SQL procedure successfully completed.


    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SCOTT@orcl_11g>

Report message to a moderator

Re: Run query based on parameter [message #402204 is a reply to message #397272]

Fri, 08 May 2009 01:35

smittal17
Messages: 7
Registered: March 2009

Junior Member

Thnaks Barbara,

Sorry for so late reply.

The query helped a lot.Thanks again.

Shilpa

Report message to a moderator

Re: Run query based on parameter [message #402391 is a reply to message #402204]

Sat, 09 May 2009 16:16

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Also consider the manually partitioned version. It may perform better when something other than "ALL" is selected.

select 'ALL' QUERY_KEY,dept.*
from dept
where 'ALL' = :parameter
union all
select dept.deptname query_key,dept.*
from dept
where dept.deptname = :parameter
/

Clearly this is not idea.

Quote:

1) it is more complex because of the union all
2) it is a less well know technique
3) it changes the record format because of the added QUERY_KEY

BUT...

Quote:

when a deptname of 'ABC' is selected rather than all, this query will can use an index on deptnam.

So, for some applications this can be a life saver.

It is known as the "MANUALLY PARTITIONED VIEW" technique and hails back to Oracle 7 days. It works because during query execution, oracle sees a CONSTANT=CONSTANT test in the where clause of one of the queries. It does this test before the query actually executes and sees that if the parameter = 'ABC' then the first query will never return any data and thus it prunes the first part of the query away. This is particularly effective when there are many possible paths a query can take but from which only one will ever be executed during any query run.

I have not checked the query above for syntax, I leave that to you if you are interested. I give you the idea, you run (a maybe fall down) with it.

Good luck, Kevin

Report message to a moderator

Re: Run query based on parameter [message #402412 is a reply to message #402391]

Sun, 10 May 2009 00:42

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

To be a real partitioned view each (sub)query should have an exclusive condition:

select 'ALL' QUERY_KEY,dept.*
from dept
where 'ALL' = :parameter
union all
select dept.deptname query_key,dept.*
from dept
where dept.deptname = :parameter
  and 'ALL' != :parameter
/

Then one and only one (sub)query is executed.

Regards
Michel

Report message to a moderator

Re: Run query based on parameter [message #402462 is a reply to message #402412]

Sun, 10 May 2009 19:59

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Thanks for clarifying. I just took it for granted that there would not be a department_name of ALL.

Also, I admit to using the term MANUALLY PARTITIONED a bit loosely here. The key from a performance perspective is that the queries of the view have WHERE clauses that offer Oracle some way to prune parts of the query away before doing its actual execution. This pruning does not have to be symmetrical or exclusive.

Kevin

[Updated on: Sun, 10 May 2009 20:02]

Report message to a moderator

Previous Topic:	parameter for creating materialized view
Next Topic:	Passing array from oracle procedure to javascript function

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Dec 05 06:50:24 CST 2024