Home » SQL & PL/SQL » SQL & PL/SQL » Run query based on parameter (Oracele)
Run query based on parameter [message #396737] Tue, 07 April 2009 22:19 Go to next message
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
Re: Run query based on parameter [message #396738 is a reply to message #396737] Tue, 07 April 2009 22:29 Go to previous messageGo to next message
BlackSwan
Messages: 25049
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.

Re: Run query based on parameter [message #396744 is a reply to message #396737] Tue, 07 April 2009 22:57 Go to previous messageGo to next message
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?
Re: Run query based on parameter [message #396745 is a reply to message #396737] Tue, 07 April 2009 23:10 Go to previous messageGo to next message
BlackSwan
Messages: 25049
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.


Re: Run query based on parameter [message #397272 is a reply to message #396744] Thu, 09 April 2009 14:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
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> 

Re: Run query based on parameter [message #402204 is a reply to message #397272] Fri, 08 May 2009 01:35 Go to previous messageGo to next message
smittal17
Messages: 7
Registered: March 2009
Junior Member
Thnaks Barbara,

Sorry for so late reply.

The query helped a lot.Thanks again.

Shilpa
Re: Run query based on parameter [message #402391 is a reply to message #402204] Sat, 09 May 2009 16:16 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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
Re: Run query based on parameter [message #402412 is a reply to message #402391] Sun, 10 May 2009 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Run query based on parameter [message #402462 is a reply to message #402412] Sun, 10 May 2009 19:59 Go to previous message
Kevin Meade
Messages: 2101
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:
  


Current Time: Fri Dec 09 11:54:56 CST 2016

Total time taken to generate the page: 0.05296 seconds