Dynamic Procedure giving Error

Discussion:

(too old to reply)

Aditya Tiwari

2007-10-10 09:55:14 UTC

Hi,

I have created my dynamic procedure in which values are passed at the run time and on that basis query is executed. But i am getting error.

Please help me out.
Error :>
ERROR [42884] [IBM][DB2/NT] SQL0440N No authorized routine named "||" of type "FUNCTION" having compatible arguments was found. LINE NUMBER=19. SQLSTATE=42884

++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++
CREATE PROCEDURE DB2ADMIN.Dup_Data_1 (
IN name_business_in varchar(100),
IN corp_id_in integer )
P1: BEGIN
DECLARE chk_val INT DEFAULT 0;
DECLARE v_name_business varchar(100);
DECLARE v_corp_id integer;
DECLARE v_stmt varchar(2000);

DECLARE sr CURSOR WITH RETURN TO CLIENT FOR s1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET chk_val = 1;

SET v_stmt = ' n.NAME_BUSINESS,
c.CORP_ID
FROM
CLI_CLIENT c
JOIN CLI_CLNT_NAME_V n ON c.CLIENT_ID = n.CLIENT_ID WHERE ';

IF name_business_in is not null then
set v_stmt = v_stmt || 'name_business = ' || '''' || name_business_in || '''';
END IF;
IF corp_id_in is not null then
set v_stmt = v_stmt || 'corp_id = ' || '''' || corp_id_in || '''';
END IF;

PREPARE s1 FROM v_stmt;
open sr;
FETCH sr INTO
v_client_id,v_name_business,v_corp_id;

IF chk_val = 1 THEN
INSERT INTO cli_clnt_duplicate_test
VALUES(v_name_business,v_corp_id);
END IF;

CLOSE sr;
END P1
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Andreas Kannegiesser

2007-10-10 10:27:15 UTC

Permalink

Hi,

Post by Aditya Tiwari
Hi,
I have created my dynamic procedure in which values
are passed at the run time and on that basis query is
executed. But i am getting error.
Please help me out.
Error :>
ERROR [42884] [IBM][DB2/NT] SQL0440N No authorized
routine named "||" of type "FUNCTION" having
compatible arguments was found. LINE NUMBER=19.
SQLSTATE=42884
++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++++
CREATE PROCEDURE DB2ADMIN.Dup_Data_1 (
IN name_business_in varchar(100),
IN corp_id_in integer )
P1: BEGIN
DECLARE chk_val INT DEFAULT 0;
DECLARE v_name_business varchar(100);
DECLARE v_corp_id integer;
DECLARE v_stmt varchar(2000);
DECLARE sr CURSOR WITH RETURN TO CLIENT FOR s1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET chk_val =
1;
SET v_stmt = ' n.NAME_BUSINESS,
c.CORP_ID
FROM
CLI_CLIENT c
JOIN CLI_CLNT_NAME_V n ON c.CLIENT_ID = n.CLIENT_ID
WHERE ';
IF name_business_in is not null then
set v_stmt = v_stmt || 'name_business = ' || '''' ||
name_business_in || '''';
END IF;
IF corp_id_in is not null then
set v_stmt = v_stmt || 'corp_id = ' || '''' ||
corp_id_in || '''';

v_stmt is of character type, corp_id_in is integer. You cannot concatenate character data with numeric data.
You may cast corp_id_in to char like so:

set v_stmt = v_stmt || 'corp_id = ' || char(corp_id_in)

(ssuming that corp_id is also integer type in your table

Post by Aditya Tiwari
END IF;
PREPARE s1 FROM v_stmt;
open sr;
FETCH sr INTO
v_client_id,v_name_business,v_corp_id;
IF chk_val = 1 THEN
INSERT INTO cli_clnt_duplicate_test
VALUES(v_name_business,v_corp_id);
END IF;
CLOSE sr;
END P1
++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++++++++

Good luck,
aka.

Aditya Tiwari

2007-10-10 10:46:48 UTC

Permalink

Thank you very much.... I have changed it and now i am getting this error.

ERROR [42802] [IBM][DB2/NT] SQL0117N The number of values assigned is not the same as the number of specified or implied columns. LINE NUMBER=30. SQLSTATE=42802

Any help on this....I have already checked it and everything looks fine to me.

Andreas Kannegiesser

2007-10-10 13:25:13 UTC

Permalink

Post by Aditya Tiwari
Thank you very much.... I have changed it and now i
am getting this error.
ERROR [42802] [IBM][DB2/NT] SQL0117N The number of
values assigned is not the same as the number of
specified or implied columns. LINE NUMBER=30.
SQLSTATE=42802
Any help on this....I have already checked it and
everything looks fine to me.

Hi,

I think your posted procedure code can not be complete, because it has some more errors...I don't see a SELECT at all in your variable v_stmt, there would be a missing AND in your WHERE clause if both input parameters were present and also I see only two columns selected in your statement...

If this is the case, then you try to fetch four values, which leads to the error.

Please post your complete statement.

Good luck,
aka.

Aditya Tiwari

2007-10-10 14:22:40 UTC

Permalink

I have taken care of select statement in my procedure but when i am puting and in where clause it's giving me some warnings and error as

ERROR [42601] [IBM][DB2/NT] SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END P1". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

So can u please let me know what to do. I have to execute this query and take i/p at run time.

New procedure is
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE PROCEDURE DB2ADMIN.Dup_Data_Client (
IN name_business_in varchar(100),
IN corp_id_in varchar(100) )
P1: BEGIN
DECLARE chk_val INT DEFAULT 0;
DECLARE v_name_business varchar(100);
DECLARE v_corp_id varchar(100);
DECLARE v_stmt varchar(2000);

DECLARE sr CURSOR WITH RETURN TO CLIENT FOR s1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET chk_val = 1;

SET v_stmt = '
select
n.NAME_BUSINESS ,
c.CORP_ID
FROM
CLI_CLIENT c
JOIN CLI_CLNT_NAME_V n ON c.CLIENT_ID = n.CLIENT_ID WHERE ';
IF name_business_in is not null then
set v_stmt = v_stmt || 'name_business = ' || '''' || name_business_in || '''';
END IF;
AND
IF corp_id_in is not null then
set v_stmt = v_stmt || 'corp_id = ' || '''' || corp_id_in || '''';
END IF;

PREPARE s1 FROM v_stmt;
open sr;
FETCH sr INTO
v_name_business,v_corp_id;

IF chk_val = 1 THEN
INSERT INTO cli_clnt_duplicate_test(name_business,corp_id)
VALUES(v_name_business,v_corp_id);
END IF;

CLOSE sr;
END P1
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Andreas Kannegiesser

2007-10-10 15:36:24 UTC

Permalink

Hi,

there are still some problems with your code...
Are you using an IDE to create the procedure, or do you have in in a file and execute it with db2 command?

Post by Aditya Tiwari
I have taken care of select statement in my procedure
but when i am puting and in where clause it's giving
me some warnings and error as
ERROR [42601] [IBM][DB2/NT] SQL0104N An unexpected
token "END-OF-STATEMENT" was found following "END
P1". Expected tokens may include: "JOIN
<joined_table>". SQLSTATE=42601
So can u please let me know what to do. I have to
execute this query and take i/p at run time.
New procedure is
++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++
CREATE PROCEDURE DB2ADMIN.Dup_Data_Client (
IN name_business_in varchar(100),
IN corp_id_in varchar(100) )
P1: BEGIN
DECLARE chk_val INT DEFAULT 0;
DECLARE v_name_business varchar(100);
DECLARE v_corp_id varchar(100);
DECLARE v_stmt varchar(2000);
DECLARE sr CURSOR WITH RETURN TO CLIENT FOR s1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET
UND SET chk_val = 1;
SET v_stmt = '
select
n.NAME_BUSINESS ,
c.CORP_ID
FROM
CLI_CLIENT c
JOIN CLI_CLNT_NAME_V n ON c.CLIENT_ID =
IENT_ID = n.CLIENT_ID WHERE ';

There is a problem here, because you can not build your variable containing your sql statement in this way. You have to put it all in one line, or do something to continue the line like so:

set v_stmt = 'select ' ||
'n.NAME_BUSINESS , ' ||
'c.CORP_ID ' ||
..

It's similar to building such a string in Java, where you would have to write:

String s = "select " +
"n.NAME_BUSINESS, " +
..

Can you see the difference?

Post by Aditya Tiwari
IF name_business_in is not null then
set v_stmt = v_stmt || 'name_business = ' || ''''
'' || name_business_in || '''';
END IF;
AND
IF corp_id_in is not null then
set v_stmt = v_stmt || 'corp_id = ' || '''' ||
'''' || corp_id_in || '''';
END IF;
PREPARE s1 FROM v_stmt;

After preparing the statement you should also EXECUTE it like so:

EXECUTE s1;

Post by Aditya Tiwari
open sr;
FETCH sr INTO
v_name_business,v_corp_id;
IF chk_val = 1 THEN
INSERT INTO
cli_clnt_duplicate_test(name_business,corp_id)
VALUES(v_name_business,v_corp_id);
END IF;
CLOSE sr;
END P1
++++++++++++++++++++++++++++++++++++++++++++++++++++++
++++++++++++++++++

Hope this helps,
aka.

Aditya Tiwari

2007-10-11 07:38:04 UTC

Permalink

Thank you for youe help....
I have changed my procedure as you have suggested but i am still getting errors
as

ERROR [42603] [IBM][DB2/NT] SQL0010N The string constant beginning with "' || '''' || postal_code_in || ''''; END IF; PREPARE" does not have an ending string delimiter. LINE NUMBER=54. SQLSTATE=42603

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE PROCEDURE DB2ADMIN.SP_DUP_DATA (
IN NAME_BUSINESS_IN varchar(255),
IN CORP_ID_IN INTEGER,
IN HOUSE_NBR_IN CHARACTER(10),
IN ADDRESS1_IN VARCHAR(40),
IN ADDRESS2_IN VARCHAR(40),
IN ADDRESS3_IN VARCHAR(40),
IN ADDRESS4_IN VARCHAR(40),
IN CITY_IN VARCHAR(120),
IN STATE_PROV_CD_IN CHARACTER(2),
IN POSTAL_CODE_IN CHARACTER(11)
)

DYNAMIC RESULT SETS 1
LANGUAGE SQL
MODIFIES SQL DATA
CALLED ON NULL INPUT

P1: BEGIN
DECLARE chk_val INT DEFAULT 0;
DECLARE v_name_business varchar(255);
DECLARE v_corp_id integer;
DECLARE v_house_nbr character(10);
DECLARE v_address1 varchar(40);
DECLARE v_address2 varchar(40);
DECLARE v_address3 varchar(40);
DECLARE v_address4 varchar(40);
DECLARE v_city varchar(120);
DECLARE v_state_prov_cd character(2);
DECLARE v_postal_code character(11);
DECLARE v_stmt varchar(2000);

DECLARE sr CURSOR WITH RETURN TO CLIENT FOR s1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET chk_val = 1;

SET v_stmt = 'SELECT '||
'n.CLIENT_ID',||
'n.NAME_BUSINESS',||
'c.CORP_ID',||
'a.HOUSE_NBR',||
'a.ADDRESS1',||
'a.ADDRESS2',||
'a.ADDRESS3',||
'a.ADDRESS4',||
'a.CITY',||
'a.STATE_PROV_CD',||
'a.POSTAL_CODE'||

FROM
CLI_CLIENT c
JOIN CLI_CLNT_NAME_V n ON c.CLIENT_ID = n.CLIENT_ID
LEFT OUTER JOIN CLI_CLNT_ADDR_V ca ON n.CLIENT_ID = ca.CLIENT_ID
LEFT OUTER JOIN CLI_ADDRESS_V a ON ca.ADDRESS_ID = a.ADDRESS_ID
WHERE ';
IF name_business_in is not null then
set v_stmt = v_stmt || 'name_business = ' || '''' || name_business_in || '''';
END IF;
IF corp_id_in is not null then
set v_stmt = v_stmt || 'corp_id = ' || '''' || RTRIM(CHAR(corp_id_in || '''';
END IF;
IF house_nbr_in is not null then
set v_stmt = v_stmt || 'house_nbr = ' || '''' || house_nbr_in || '''';
END IF;
IF address1_in is not null then
set v_stmt = v_stmt || 'address1 = ' || '''' || address1_in || '''';
END IF;
IF address2_in is not null then
set v_stmt = v_stmt || 'address2 = ' || '''' || address2_in || '''';
END IF;
IF address3_in is not null then
set v_stmt = v_stmt || 'address3 = ' || '''' || address3_in || '''';
END IF;
IF address4_in is not null then
set v_stmt = v_stmt || 'address4 = ' || '''' || address4_in || '''';
END IF;
IF city_in is not null then
set v_stmt = v_stmt || 'city = ' || '''' || city_in || '''';
END IF;
IF state_prov_cd_in is not null then
set v_stmt = v_stmt || 'state_prov_cd = ' || '''' || state_prov_cd_in || '''';
END IF;
IF postal_code_in is not null then
set v_stmt = v_stmt || 'postal_code = ' || '''' || postal_code_in || '''';
END IF;

PREPARE s1 FROM v_stmt;
Execute s1;
open sr;

FETCH sr INTO
v_name_business,
v_corp_id,
v_house_nbr,
v_address1,
v_address2,
v_address3,
v_address4,
v_city,
v_state_prov_cd,
v_postal_code;

IF chk_val = 1 THEN
INSERT INTO cli_clnt_duplicate_data(
name_business,
corp_id,
house_nbr,
address1,
address2,
address3,
address4,
city,
state_prov_cd,
postal_code)

VALUES(
v_name_business,
v_corp_id,
v_house_nbr,
v_address1,
v_address2,
v_address3,
v_address4,
v_city,
v_state_prov_cd,
v_postal_code);
END IF;
commit;

CLOSE sr;
END P1

Knut Stolze

2007-10-11 12:18:30 UTC

Permalink

Post by Aditya Tiwari
Thank you for youe help....
I have changed my procedure as you have suggested but i am still getting
errors as
ERROR [42603] [IBM][DB2/NT] SQL0010N The string constant beginning with
"' || '''' || postal_code_in || ''''; END IF; PREPARE"
does not have an ending string delimiter. LINE NUMBER=54. SQLSTATE=42603

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Post by Aditya Tiwari
CREATE PROCEDURE DB2ADMIN.SP_DUP_DATA (
IN NAME_BUSINESS_IN varchar(255),
IN CORP_ID_IN INTEGER,
IN HOUSE_NBR_IN CHARACTER(10),
IN ADDRESS1_IN VARCHAR(40),
IN ADDRESS2_IN VARCHAR(40),
IN ADDRESS3_IN VARCHAR(40),
IN ADDRESS4_IN VARCHAR(40),
IN CITY_IN VARCHAR(120),
IN STATE_PROV_CD_IN CHARACTER(2),
IN POSTAL_CODE_IN CHARACTER(11)
)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
MODIFIES SQL DATA
CALLED ON NULL INPUT
P1: BEGIN
DECLARE chk_val INT DEFAULT 0;
DECLARE v_name_business varchar(255);
DECLARE v_corp_id integer;
DECLARE v_house_nbr character(10);
DECLARE v_address1 varchar(40);
DECLARE v_address2 varchar(40);
DECLARE v_address3 varchar(40);
DECLARE v_address4 varchar(40);
DECLARE v_city varchar(120);
DECLARE v_state_prov_cd character(2);
DECLARE v_postal_code character(11);
DECLARE v_stmt varchar(2000);
DECLARE sr CURSOR WITH RETURN TO CLIENT FOR s1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET chk_val = 1;
SET v_stmt = 'SELECT '||
'n.CLIENT_ID',||

I guess you want to place the commas inside the string and not outside?

Post by Aditya Tiwari
'n.NAME_BUSINESS',||
'c.CORP_ID',||
'a.HOUSE_NBR',||
'a.ADDRESS1',||
'a.ADDRESS2',||
'a.ADDRESS3',||
'a.ADDRESS4',||
'a.CITY',||
'a.STATE_PROV_CD',||
'a.POSTAL_CODE'||
FROM
CLI_CLIENT c
JOIN CLI_CLNT_NAME_V n ON c.CLIENT_ID = n.CLIENT_ID
LEFT OUTER JOIN CLI_CLNT_ADDR_V ca ON n.CLIENT_ID = ca.CLIENT_ID
LEFT OUTER JOIN CLI_ADDRESS_V a ON ca.ADDRESS_ID = a.ADDRESS_ID
WHERE ';
IF name_business_in is not null then
set v_stmt = v_stmt || 'name_business = ' || '''' || name_business_in ||
''''; END IF;
IF corp_id_in is not null then
set v_stmt = v_stmt || 'corp_id = ' || '''' || RTRIM(CHAR(corp_id_in ||
''''; END IF;

You will get an invalid SQL statement if 2 (or more) parameters are not NULL
because the constructed string is:

SELECT ...
FROM ...
WHERE name_business = '...'corp_id = '...'

There is an AND missing between the predicates.

Post by Aditya Tiwari
IF house_nbr_in is not null then
set v_stmt = v_stmt || 'house_nbr = ' || '''' || house_nbr_in || '''';
END IF;
IF address1_in is not null then
set v_stmt = v_stmt || 'address1 = ' || '''' || address1_in || '''';
END IF;
IF address2_in is not null then
set v_stmt = v_stmt || 'address2 = ' || '''' || address2_in || '''';
END IF;
IF address3_in is not null then
set v_stmt = v_stmt || 'address3 = ' || '''' || address3_in || '''';
END IF;
IF address4_in is not null then
set v_stmt = v_stmt || 'address4 = ' || '''' || address4_in || '''';
END IF;
IF city_in is not null then
set v_stmt = v_stmt || 'city = ' || '''' || city_in || '''';
END IF;
IF state_prov_cd_in is not null then
set v_stmt = v_stmt || 'state_prov_cd = ' || '''' || state_prov_cd_in ||
''''; END IF;
IF postal_code_in is not null then
set v_stmt = v_stmt || 'postal_code = ' || '''' || postal_code_in || '''';
END IF;
PREPARE s1 FROM v_stmt;
Execute s1;
open sr;
FETCH sr INTO

You declared the cursor as being WITH RETURN TO CLIENT - but here you fetch
from the cursor (and even close it later). So what's the point of the WITH
RETURN?

Post by Aditya Tiwari
v_name_business,
v_corp_id,
v_house_nbr,
v_address1,
v_address2,
v_address3,
v_address4,
v_city,
v_state_prov_cd,
v_postal_code;
IF chk_val = 1 THEN
INSERT INTO cli_clnt_duplicate_data(
name_business,
corp_id,
house_nbr,
address1,
address2,
address3,
address4,
city,
state_prov_cd,
postal_code)
VALUES(
v_name_business,
v_corp_id,
v_house_nbr,
v_address1,
v_address2,
v_address3,
v_address4,
v_city,
v_state_prov_cd,
v_postal_code);
END IF;
commit;

Are you sure that you want to COMMIT here? It is very rare that a stored
procedure issues COMMIT/ROLLBACK statements because that would directly
interfere with the client's transaction.

Also, if you don't declare a cursor as being WITH HOLD, the cursor will be
closed upon COMMIT. (All cursors will be closed upon ROLLBACK.)

Post by Aditya Tiwari
CLOSE sr;
END P1

I would recommend that you strip down the procedure to the part that
constructs the SELECT statement dynamically. Return that string as OUT
parameter from the procedure and verify with different tests that you will
always get a correct and valid SQL statement for the different situations.
Once you have that, you can add the other pieces to open the cursor and
process the data.

Furthermore, since you fetch data and insert something into another table,
you should use the MERGE statement or an INSERT INTO ... SELECT ... to
streamline this. And you should also consider if you really need dynamic
SQL or can simply handle it with a single static SQL statement. (The DB2
manuals contain an extensive discussion on the pros and cons of dynamic vs.
static SQL.)

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Aditya Tiwari

2007-10-12 09:58:22 UTC

Permalink

Hi Knut,

I have modified my procedure as you had suggested.
I have debugged it and i know that this portion is giving me error at run time.
+++++++++++++++++++++++++++++++++++++++
IF name_business_in is not null then
set v_stmt = v_stmt || 'name_business = ' || '''' || name_business_in || '''';
END IF;
++++++++++++++++++++++++++++++++++++++++
Error i am getting is
SQLSTATE = 42601, SQLCODE = -104
++++++++++++++++++++++++++++++++++++++++

Furthurmore can you please explain a bit more this paragraph which you had written earlier.
*************************************************
I would recommend that you strip down the procedure to the part that
constructs the SELECT statement dynamically. Return that string as OUT
parameter from the procedure and verify with different tests that you will
always get a correct and valid SQL statement for the different situations.
Once you have that, you can add the other pieces to open the cursor and
process the data.
**************************************************

Please help me out in this.

SET SCHEMA = DB2ADMIN;
Drop procedure DB2ADMIN.SP_DUP_DATA;
CREATE PROCEDURE DB2ADMIN.SP_DUP_DATA (
IN NAME_BUSINESS_IN VARCHAR(255),
IN CORP_ID_IN INTEGER,
IN HOUSE_NBR_IN CHARACTER(10),
IN ADDRESS1_IN VARCHAR(40),
IN ADDRESS2_IN VARCHAR(40),
IN ADDRESS3_IN VARCHAR(40),
IN ADDRESS4_IN VARCHAR(40),
IN CITY_IN VARCHAR(120),
IN STATE_PROV_CD_IN CHARACTER(2),
IN POSTAL_CODE_IN CHARACTER(11),
OUT SQLSTATE_OUT CHARACTER(5),
out SQLCODE_OUT INT)

SPECIFIC SQL071011150107500
DYNAMIC RESULT SETS 1
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
MODIFIES SQL DATA
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS
P1: BEGIN

DECLARE SQLSTATE CHARACTER(5) DEFAULT '00000';
DECLARE SQLCODE INT DEFAULT 0;
DECLARE chk_val INT DEFAULT 0;
DECLARE v_name_business varchar(255);
DECLARE v_corp_id integer;
DECLARE v_house_nbr character(10);
DECLARE v_address1 varchar(40);
DECLARE v_address2 varchar(40);
DECLARE v_address3 varchar(40);
DECLARE v_address4 varchar(40);
DECLARE v_city varchar(120);
DECLARE v_state_prov_cd character(2);
DECLARE v_postal_code character(11);
DECLARE v_stmt varchar(2000);

DECLARE sr CURSOR FOR s1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
select sqlstate,sqlcode
into
sqlstate_out,sqlcode_out
from sysibm.sysdummy1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET chk_val = 1;

SET v_stmt = '
SELECT
n.CLIENT_ID,
n.NAME_BUSINESS,
c.CORP_ID,
a.HOUSE_NBR,
a.ADDRESS1,
a.ADDRESS2,
a.ADDRESS3,
a.ADDRESS4,
a.CITY,
a.STATE_PROV_CD,
a.POSTAL_CODE

FROM
CLI_CLIENT c
JOIN CLI_CLNT_NAME_V n ON c.CLIENT_ID = n.CLIENT_ID
LEFT OUTER JOIN CLI_CLNT_ADDR_V ca ON n.CLIENT_ID = ca.CLIENT_ID
LEFT OUTER JOIN CLI_ADDRESS_V a ON ca.ADDRESS_ID = a.ADDRESS_ID
WHERE ';
IF name_business_in is not null then
set v_stmt = v_stmt || 'name_business = ' || '''' || name_business_in || '''';
END IF;
/*
AND
IF corp_id_in is not null then
set v_stmt = v_stmt || 'corp_id = ' || '''' || RTRIM(CHAR(corp_id_in)) || '''';
END IF;
AND
IF house_nbr_in is not null then
set v_stmt = v_stmt || 'house_nbr = ' || '''' || house_nbr_in || '''';
END IF;
AND
IF address1_in is not null then
set v_stmt = v_stmt || 'address1 = ' || '''' || address1_in || '''';
END IF;
AND
IF address2_in is not null then
set v_stmt = v_stmt || 'address2 = ' || '''' || address2_in || '''';
END IF;
AND
IF address3_in is not null then
set v_stmt = v_stmt || 'address3 = ' || '''' || address3_in || '''';
END IF;
AND
IF address4_in is not null then
set v_stmt = v_stmt || 'address4 = ' || '''' || address4_in || '''';
END IF;
AND
IF city_in is not null then
set v_stmt = v_stmt || 'city = ' || '''' || city_in || '''';
END IF;
AND
IF state_prov_cd_in is not null then
set v_stmt = v_stmt || 'state_prov_cd = ' || '''' || state_prov_cd_in || '''';
END IF;
AND
IF postal_code_in is not null then
set v_stmt = v_stmt || 'postal_code = ' || '''' || postal_code_in || '''';
END IF;

*/
PREPARE s1 FROM v_stmt;
open sr;

FETCH sr INTO
v_name_business,
v_corp_id,
v_house_nbr,
v_address1,
v_address2,
v_address3,
v_address4,
v_city,
v_state_prov_cd,
v_postal_code;

IF chk_val = 1 THEN
INSERT INTO cli_clnt_duplicate_data(
name_business,
corp_id,
house_nbr,
address1,
address2,
address3,
address4,
city,
state_prov_cd,
postal_code)

VALUES(
v_name_business,
v_corp_id,
v_house_nbr,
v_address1,
v_address2,
v_address3,
v_address4,
v_city,
v_state_prov_cd,
v_postal_code);
END IF;
CLOSE sr;

END P1

Knut Stolze

2007-10-12 15:53:35 UTC

Permalink

Post by Aditya Tiwari
Hi Knut,
I have modified my procedure as you had suggested.
I have debugged it and i know that this portion is giving me error at run time.
+++++++++++++++++++++++++++++++++++++++
IF name_business_in is not null then
set v_stmt = v_stmt || 'name_business = ' || '''' || name_business_in ||
''''; END IF;
++++++++++++++++++++++++++++++++++++++++
Error i am getting is
SQLSTATE = 42601, SQLCODE = -104
++++++++++++++++++++++++++++++++++++++++

SQL0104 (-104) at runtime means that the statement you constructed in the
string has an invalid syntax.

Post by Aditya Tiwari
Furthurmore can you please explain a bit more this paragraph which you had written earlier.
*************************************************
I would recommend that you strip down the procedure to the part that
constructs the SELECT statement dynamically. Return that string as OUT
parameter from the procedure and verify with different tests that you will
always get a correct and valid SQL statement for the different situations.
Once you have that, you can add the other pieces to open the cursor and
process the data.
**************************************************

I meant that you should verify the generated SQL statement for various input
parameter combinations to insure that only valid statements are created.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Aditya Tiwari

2007-10-12 09:58:33 UTC

Permalink