Home » SQL & PL/SQL » SQL & PL/SQL » How to handle where clause of a procedure dynamically (merged)
How to handle where clause of a procedure dynamically (merged) [message #314915] Fri, 18 April 2008 04:42 Go to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi all,

I have to write a procedure which will get a plcy_nbr and plcy_status_in as input parameters.
Based on plcy_status_in ,i have to include plcy_id in it's where clause to filter the data futher.
like..
select * from plcy_nbr where plcy_no =PLCY_NBR_IN
and plcy_status = status_in
and plcy_id in '21'and '23' ;

In the above query depending upon the status_in value plcy_id varies

e.g.,
if status_in ='C'
then plcy_id in '21'and '23' ;
final query is 

select * from plcy_nbr where plcy_no =PLCY_NBR_IN
and plcy_status = status_in
and plcy_id in '21'and '23' ;
 
if  status_in ='D'
then plcy_id ='17' ;

select * from plcy_nbr where plcy_no =PLCY_NBR_IN
and plcy_status = status_in
and plcy_id = '17';
and so on..............

1)One Solution is to check the plcy_status_in every time like
if status_in ='C' then
select * from plcy_nbr where plcy_no =PLCY_NBR_IN
and plcy_status = status_in
and plcy_id in '21'and '23' ;
elsif
...................
end if;
But,Is there any other better way to handle this situation ?As
select * from plcy_nbr where plcy_no =PLCY_NBR_IN this part of the query is common for all the plcy_status_in ??
Not sure ,that dynamic sql will serve my purpose

Please Advice,
Re: How to handle where clause of a procedure dynamically [message #314917 is a reply to message #314915] Fri, 18 April 2008 04:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use DECODE or CASE.

Regards
Michel
Re: How to handle where clause of a procedure dynamically [message #314936 is a reply to message #314915] Fri, 18 April 2008 05:49 Go to previous messageGo to next message
xrkra
Messages: 2
Registered: April 2008
Location: Hyderabad
Junior Member
Recommend you to use the dynamic sql and then execute it.
Re: How to handle where clause of a procedure dynamically [message #314937 is a reply to message #314936] Fri, 18 April 2008 05:53 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Then that would be a bad recommendation.
Re: How to handle where clause of a procedure dynamically [message #314988 is a reply to message #314915] Fri, 18 April 2008 08:19 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
ramanajv1968 wrote on Fri, 18 April 2008 05:42

and plcy_id in '21'and '23' ;




You know that this is invalid syntax, right?
problem with execute immediate [message #314992 is a reply to message #314915] Fri, 18 April 2008 08:36 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Hi All,

what is wrong with the below code.

SQL> desc b
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER(10)
 C                                                  VARCHAR2(10)


declare
l_dept varchar2(20):='first';
 l_nam     varchar2(20);
 l_loc     varchar2(20);
begin

if l_dept = 'first'then
   l_dept  := '''first' and c = '4''';
--   l_dept  := '''first' and c = '4''';
 execute immediate 'select A,C from B where c = :1'
   into l_nam
   using l_dept ;
   dbms_output.put_line('output is '||nvl(sql%rowcount,1));
   end if;
end;


l_dept := '''first' and c = '4''';
*
RROR at line 8:
RA-06550: line 8, column 15:
LS-00382: expression is of wrong type
RA-06550: line 8, column 4:
L/SQL: Statement ignored

Thanks,
Re: problem with execute immediate [message #314993 is a reply to message #314992] Fri, 18 April 2008 08:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I recommend you to read again your PL/SQL guide. This:
Quote:
if l_dept = 'first'then
l_dept := '''first' and c = '4''';

is completly outside the PL/SQL syntax.

Regards
Michel
Re: problem with execute immediate [message #314996 is a reply to message #314992] Fri, 18 April 2008 08:48 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
So,

can any one suggest how to rewrite the above query
if l_dept = 'first'then
in dynamic sql i want to add one more filter condition on the same column.
like......
if l_dept = 'first'then 
select A,C from B where c ='first' or c= '4'


elsif l_dept = 'second'then

like select A,C from B where c ='second' or c= '5'
Re: problem with execute immediate [message #314997 is a reply to message #314992] Fri, 18 April 2008 08:58 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>in dynamic sql i want to add one more filter condition on the same column.
It does NOT need to be dynamic SQL.
Directly execute which ever statement is the desired SELECT
Re: problem with execute immediate [message #314998 is a reply to message #314992] Fri, 18 April 2008 09:05 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
The above code is the sample code of a procedure which gets
l_dept as an input.
so,based on the value of that input the select querie's where clause may include some more filter conditions.
Re: problem with execute immediate [message #315001 is a reply to message #314998] Fri, 18 April 2008 09:28 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Have you tried what Michael had suggested in your previous post ?

http://www.orafaq.com/forum/t/101023/94420/

Regards

Raj
Re: problem with execute immediate [message #315002 is a reply to message #314992] Fri, 18 April 2008 09:35 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Given link is refer to my previous post only.
Yes,I tried .But that doesn't materialized .
so,is it possible to handle the way which i mentioned in above?


[Updated on: Fri, 18 April 2008 09:36]

Report message to a moderator

Re: problem with execute immediate [message #315003 is a reply to message #315002] Fri, 18 April 2008 09:36 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
Have you tried what Michael had suggested in your previous post ?



Regards

Raj
Re: problem with execute immediate [message #315005 is a reply to message #314992] Fri, 18 April 2008 09:44 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
By using Decode and case ?

I tried that.

But,It doesn't serve my requirements

I am looking some thing like

if l_dept = 'first'then
l_dept := '''first' and c = '4''';
execute immediate 'select A,C from B where c = :1'

After the query formation it should look like 
 execute immediate 'select A,C from B where c ='first' and c=4'

I am looking the above sort of thing.
Re: problem with execute immediate [message #315006 is a reply to message #315005] Fri, 18 April 2008 09:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
One advise: Stay away from dynamic sql (= execute immediate).
It serves no purpose here, it does not make sense here and you probably don't understand it a whole lot.
The only thing in favour of using dynamic sql is some vague advise you got in your other post, whereas you chose to ignore multiple hints towards a very sound advise.
Re: problem with execute immediate [message #315010 is a reply to message #314992] Fri, 18 April 2008 10:20 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
Thanks for the suggestion.

But ,which is the best..

Dynamic sql or using multiple If statements

like If l_dept = 'first'then

select * from B where l_dept = 'first' or l_dept =4;

elsif l_dept = 'two'then
select * from B where l_dept = 'two' or l_dept =7;

.................

In my scenario which is the advicable?



Re: problem with execute immediate [message #315012 is a reply to message #315010] Fri, 18 April 2008 10:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Time to merge the 2 topics.

Regards
Michel



Done.

[Updated on: Fri, 18 April 2008 10:28]

Report message to a moderator

Re: How to handle where clause of a procedure dynamically (merged) [message #315013 is a reply to message #314915] Fri, 18 April 2008 10:34 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
but,which is the good approach ??

Thanks
Re: problem with execute immediate [message #315027 is a reply to message #315005] Fri, 18 April 2008 12:51 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
ramanajv1968 wrote on Fri, 18 April 2008 10:44

After the query formation it should look like 
 execute immediate 'select A,C from B where c ='first' and c=4'





C can never be both 'first' and 4 for the two following reasons

1. a string cannot equal a number
2. even if they were both strings, they are still not equal.
Re: problem with execute immediate [message #315104 is a reply to message #315010] Sat, 19 April 2008 02:00 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ramanajv1968 wrote on Fri, 18 April 2008 17:20
Thanks for the suggestion.

But ,which is the best..

Dynamic sql or using multiple If statements

like If l_dept = 'first'then

select * from B where l_dept = 'first' or l_dept =4;

elsif l_dept = 'two'then
select * from B where l_dept = 'two' or l_dept =7;

.................

In my scenario which is the advicable?



Since you create your dynamic where clause in just as many if-branches, I don't see the benefit of dynamic sql. (Unless your query totally differs from the example given)

Your query could be written as a cursor, with the variables as , well, cursor variables:

create or replace procedure my_procedure
( p_dept in varchar2
) as
  cursor c_cur
  ( b_dept_string in varchar2
  , b_dept_num    in number
  ) is
    select *
    from   b
    where  b.l_dept_string_column = b_dept_string
    or     b.l_dept_number_column = b_dept_num
  ;
  l_dept_number number;
begin
  -- This could also be done by a CASE
  if p_dept = 'first'
  then
    l_dept_number := 4;
  elsif p_dept = 'two'
  then
    l_dept_number := 7;
  end if;
  --
  for r_cur in c_cur(p_dept, l_dept_number)
  loop
    ...
    do your thing
    ...
  end loop;
end;
/


another way would be to use case directly in the query:
create or replace procedure my_procedure
( p_dept in varchar2
) as
begin
  for r_rec in (select *
                from   b
                where  b.l_dept_string_column = p_dept
                and    b.l_dept_number_column = 
                          case when p_dept = 'first' then 4
                               when p_dept = 'two' then 7
                          end
  loop
    ...
    do your thing
    ...
  end loop;
end;
/

Previous Topic: Run shell script from PL/SQL
Next Topic: Oracle Log files
Goto Forum:
  


Current Time: Sun Dec 11 02:39:25 CST 2016

Total time taken to generate the page: 0.05149 seconds