Home » SQL & PL/SQL » SQL & PL/SQL » Concatenation of Conditions in Where Clause in Procedure
Concatenation of Conditions in Where Clause in Procedure [message #183859] Mon, 24 July 2006 03:22 Go to next message
anil_sapra
Messages: 35
Registered: May 2006
Location: DELHI
Member

Hi Gurus,

I have a problem in writing a procedure.
Here are table structures and data...

create table CLIENTS
(par_code varchar2(6), par_name varchar2(40), branchind varchar2(6), groupclient varchar2(6));
/
insert into clients values ('CA001','CLIENT CA001','HO','A1')
/
insert into clients values ('CA002','CLIENT CA002','HO','A1')
/
insert into clients values ('CA003','CLIENT CA003','AGRA','A2')
/
insert into clients values ('CA004','CLIENT CA004','DELHI','A3')
/
create table MY_TABLE
(wdate date, trn_type varchar2(1), narr varchar2(70), party_cd varchar2(6), sbillno varchar2(6))
/
insert into my_table values ('01-jul-2006', 'I', 'TEST','CA001','CA001')
/
insert into my_table values ('02-jul-2006','I', 'TEST 2','CA002','CA002')
/
insert into my_table values ('03-jul-2006', 'B', 'TEST 3','CA003',NULL)
/
insert into my_table values ('04-jul-2006','B', 'TEST 4','CA004',NULL)
/

create or replace PROCEDURE delete_posting(
vpostdt my_table.wdate%type,
vnarr my_table.narr%type,
vnarr1 my_table.narr%type,
vbranch CLIENTS.branchind%type,
vcontrolacc my_table.party_cd%type,
vgroupcd CLIENTS.groupclient%type) is
sql1 varchar2(70) := null;
sql2 varchar2(70) := null;
begin
If vbranch != 'All' and vbranch != null then
sql1 := ' and branchind=vbranch and party_cd <> vcontrolacc ';
end if;
If vgroupcd != null then
sql2 := ' and groupclient=vgroupcd and party_cd <> vcontrolacc ';
end if;
EXECUTE IMMEDIATE
'delete from my_table where wdate= vpostdt and trn_type= "I" and
(trim(narr)= vnarr or trim(narr)= vnarr1) and
(party_cd in (select par_code from CLIENTS
where par_code is not null ' ||sql1||' '||sql2||
' )
or sbillno in
(select par_code from
CLIENTS where par_code is not null '||sql1||' '||sql2||'))';
end delete_posting;
/

SQL> SELECT * FROM MY_TABLE;

WDATE T NARR PARTY_ SBILLN
--------- - ---------------------------------------------------------------------- ------ ------
01-JUL-06 I TEST CA001 CA001
02-JUL-06 I TEST 2 CA002 CA002
03-JUL-06 B TEST 3 CA003
04-JUL-06 B TEST 4 CA004

SQL> EXEC DELETE_POSTING('01-JUL-2006','TEST','TEST','HO','XXX','A1');
BEGIN DELETE_POSTING('01-JUL-2006','TEST','TEST','HO','XXX','A1'); END;

*
ERROR at line 1:
ORA-00904: invalid column name
ORA-06512: at "DEMO.DELETE_POSTING", line 17
ORA-06512: at line 1

I am trying to delete the records from MY_TABLE based
on some conditions that are defined in "sql" & "sql2"

Pl. Help

With regards,

Anil Sapra
Re: Concatenation of Conditions in Where Clause in Procedure [message #183864 is a reply to message #183859] Mon, 24 July 2006 03:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The idea is sound - it looks like youve got a typo in the dynamic sql somewhere.

Modify your code to dump out the SQL statement that you've built, and you should be able to track down the problem.
Re: Concatenation of Conditions in Where Clause in Procedure [message #183870 is a reply to message #183864] Mon, 24 July 2006 03:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, you did give us create table statments and inserts, so I really ought to put in a bit more effort than that.

The problem is that your dynamic query is referenceing the variables VPOSTDT, VNARR and VNARR1 and these have no meaning to the dynamic sql.

You were also using " instead of ' to specify the string 'I' withm and that was confusing things too.

I've edited your code to dump the sql to dbms_output, and changed it to use bind variables for those 3 variables.
I don't know if it works, but it doesn't error any more.

create or replace PROCEDURE delete_posting(
vpostdt     my_table.wdate%type,
vnarr       my_table.narr%type,
vnarr1      my_table.narr%type,
vbranch     CLIENTS.branchind%type,
vcontrolacc my_table.party_cd%type,
vgroupcd    CLIENTS.groupclient%type) is
sql1        varchar2(70) := null;
sql2        varchar2(70) := null;
v_sql       varchar2(32767);
begin
If vbranch != 'All' and vbranch != null then
  sql1 := ' and branchind=vbranch and party_cd <> vcontrolacc ';
end if;
If vgroupcd != null then
  sql2 := ' and groupclient=vgroupcd and party_cd <> vcontrolacc ';
end if;

v_sql := 
'delete from my_table where wdate= :b1 and trn_type= ''I'' and
(trim(narr)= :b2 or trim(narr)= :b3) and
(party_cd in (select par_code from CLIENTS
where par_code is not null ' ||sql1||' '||sql2||
' )
or sbillno in
(select par_code from
CLIENTS where par_code is not null '||sql1||' '||sql2||'))';

for i in 1.. ceil(length(v_sql)/255) loop
  dbms_output.put_line(substr(v_sql,1+(i-1)*255,255));
end loop;

execute immediate v_sql using vpostdt,vnarr,vnarr1;
end delete_posting;
Re: Concatenation of Conditions in Where Clause in Procedure [message #183871 is a reply to message #183859] Mon, 24 July 2006 03:50 Go to previous messageGo to next message
lzulueta
Messages: 10
Registered: July 2006
Location: Philippines
Junior Member
you need to code the execute immediate statement variables like vpostdt and others similar to the way you coded sql1 and sql2.

ex. for the first line of code:
EXECUTE IMMEDIATE
'delete from my_table where wdate= '''||vpostdt||''' and trn_type= ''I'' and

hope this helped
Re: Concatenation of Conditions in Where Clause in Procedure [message #183873 is a reply to message #183871] Mon, 24 July 2006 03:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Mmmmmmm
Bind variable free Dynamic SQL with dynamically hardcoded parameters in it....

The surefire way to give your CPU some more excercise.

Repeat after me 1000 times : "Bind veriables are good. I will use bind variables in situations like this"

Re: Concatenation of Conditions in Where Clause in Procedure [message #183874 is a reply to message #183859] Mon, 24 July 2006 03:54 Go to previous messageGo to next message
anil_sapra
Messages: 35
Registered: May 2006
Location: DELHI
Member


Thank you very much.

It's working fine...

I really appreciate your help.

Regards,

Anil Sapra
Re: Concatenation of Conditions in Where Clause in Procedure [message #183922 is a reply to message #183859] Mon, 24 July 2006 07:00 Go to previous messageGo to next message
anil_sapra
Messages: 35
Registered: May 2006
Location: DELHI
Member

Hi Gurus,

My procedure is running but facing a problem in
executing the condtions. which I am defining in "sql1" & "sql2"
Actually these statements are not being executed at all.

Pl. help how can I bind variables being used in "sql1" & "sql2"
when the procedure is run, it deletes all the records but
I want to delete based on conditions defined in "sql1" & "sql2"
variables...

The prblem is in -
- EXECUTE IMMEDIATE USING vpostdt, vnarr, vnarr1;
"to be executed when vbranch is null"

- EXECUTE IMMEDIATE USING vpostdt, vnarr, vnarr1, vbranch, vcontrolacc;
" to be executed when vbranch is NOT null"

vice versa for vgroupcd...



create or replace PROCEDURE delete_posting(
vpostdt     my_table.wdate%type,
vnarr       my_table.narr%type,
vnarr1      my_table.narr%type,
vbranch     CLIENTS.branchind%type,
vcontrolacc my_table.party_cd%type,
vgroupcd    CLIENTS.groupclient%type) is
sql1        varchar2(70) := null;
sql2        varchar2(70) := null;
v_sql       varchar2(32767);
begin
If vbranch != 'All' and vbranch <> null then
  sql1 := ' and branchind= :b4 and party_cd <> :b5 ';
end if;
If vgroupcd <> null then
  sql2 := ' and groupclient=vgroupcd and party_cd <> vcontrolacc ';
end if;
v_sql := 
        'delete from my_table where wdate= :b1 and trn_type= ''I'' and
         (trim(narr)= :b2 or trim(narr)= :b3) and (party_cd in (select par_code from CLIENTS
         where par_code is not null ' ||sql1||' '||sql2|| ')
         or sbillno in (select par_code from
         CLIENTS where par_code is not null '||sql1||' '||sql2||'))';
for i in 1.. ceil(length(v_sql)/255) loop
  dbms_output.put_line(substr(v_sql,1+(i-1)*255,255));
end loop;
execute immediate v_sql using vpostdt, vnarr, vnarr1, vbranch, vcontrolacc;
end delete_posting;
/


Regards,

Anil Sapra

Re: Concatenation of Conditions in Where Clause in Procedure [message #183932 is a reply to message #183922] Mon, 24 July 2006 07:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The best solution I found was to replace sections of SQL that you didn't want with clauses that always evaluate to true, and contain the same number of bind variables as the SQL they replace.
That way you can keep the number of bind variables constant.

(untested)
create or replace PROCEDURE delete_posting(
vpostdt     my_table.wdate%type,
vnarr       my_table.narr%type,
vnarr1      my_table.narr%type,
vbranch     CLIENTS.branchind%type,
vcontrolacc my_table.party_cd%type,
vgroupcd    CLIENTS.groupclient%type) is
sql1        varchar2(70) := null;
sql2        varchar2(70) := null;
v_sql       varchar2(32767);
begin

If vbranch != 'All' and vbranch <> null then
  sql1 := ' and branchind= :b4 and party_cd <> :b5 ';
else
  sql1 := ' and (1=1 or :b4 = :b5) ';
end if;
If vgroupcd <> null then
  sql2 := ' and groupclient=vgroupcd and party_cd <> :b6 ';
else
  sql2 := ' and (1=1 or :b6 is null) ';
end if;
v_sql := 
        'delete from my_table where wdate= :b1 and trn_type= ''I'' and
         (trim(narr)= :b2 or trim(narr)= :b3) and (party_cd in (select par_code from CLIENTS
         where par_code is not null ' ||sql1||' '||sql2|| ')
         or sbillno in (select par_code from
         CLIENTS where par_code is not null '||sql1||' '||sql2||'))';
for i in 1.. ceil(length(v_sql)/255) loop
  dbms_output.put_line(substr(v_sql,1+(i-1)*255,255));
end loop;
execute immediate v_sql using vpostdt, vnarr, vnarr1, vbranch, vcontrolacc,v_controlacc, vbranch, vcontrolacc,v_controlacc;
end delete_posting;

The number of bind variables is larger because bind variables are handled by position, not by name - ie if you refer to the same bind variable 6 times in a piece of sql, then you need to have it 6 times in the USING clause.
Re: Concatenation of Conditions in Where Clause in Procedure [message #183937 is a reply to message #183859] Mon, 24 July 2006 08:17 Go to previous message
anil_sapra
Messages: 35
Registered: May 2006
Location: DELHI
Member

Thank you very much for your prompt reply.

This seems very well for the problem.

I'll test & get back... Razz

Regards,

Anil Sapra
Previous Topic: Getting a ranked element from an array
Next Topic: Dynamic Cursors
Goto Forum:
  


Current Time: Tue Dec 06 16:17:01 CST 2016

Total time taken to generate the page: 0.08781 seconds