Db2 procedure error

[bikash_karan]

Hi Folks ,
I am few days old to the world of DB2 and right now i am stuck into a
problem that seems to be getting difficult for me.
Please have alook into this and provide me some help.

Below is one such procedure that i have written ,

pads@disrs16:/home/pads/temp>db2 -td@ -vf proc2.db2
CREATE PROCEDURE new_procedure1 ()
LANGUAGE SQL
BEGIN
DECLARE va_datbi VARCHAR(8);
DECLARE va_datab VARCHAR(8);
DECLARE vh_datbi VARCHAR(8);
DECLARE vh_datab VARCHAR(8);
DECLARE new_cursor cursor for
SELECT A.datbi Adatbi , A.datab Adatab , H.datbi Hdatbi, H.datab
Hdatab
FROM SAPR3.A957 A
,SAPR3.KONH H
WHERE
(A.DATBI <> H.DATBI
OR A.DATAB <> H.DATAB )
AND A.MANDT = H.MANDT
AND A.KNUMH = H.KNUMH
AND A.MANDT = '030'
for update of Hdatbi,Hdatab ;

open new_cursor;
loop
FETCH new_cursor INTO va_datbi , va_datab , vh_datbi , vh_datab;
UPDATE SAPR3.KONH H
SET H.datbi=va_datbi,
H.datab=va_datab
where CURRENT OF new_cursor
and H.knumh between '0000000000' and '0090000000'
and H.kotabnr='030' ;
end loop ;
CLOSE new_cursor;
commit;
end

When I try and execute it I get the following error .but i am unable to
rectify this , tonight is the deadline for me to submit it into
production, can anyonle please help me out with this.my mail id is
bi******@in.ibm.com

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "UPDATE SAPR3.KONH H SET H.datbi=va_"
was found
following "h_datbi , vh_datab; ". Expected tokens may include:
"<psm_return>". LINE NUMBER=27. SQLSTATE=42601

pads@disrs16:/home/pads/temp>

thanks a lot......

[Mark A]

<bi**********@hotmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

Hi Folks ,
I am few days old to the world of DB2 and right now i am stuck into a
problem that seems to be getting difficult for me.
Please have alook into this and provide me some help.

Below is one such procedure that i have written ,

pads@disrs16:/home/pads/temp>db2 -td@ -vf proc2.db2
CREATE PROCEDURE new_procedure1 ()
LANGUAGE SQL
BEGIN
DECLARE va_datbi VARCHAR(8);
DECLARE va_datab VARCHAR(8);
DECLARE vh_datbi VARCHAR(8);
DECLARE vh_datab VARCHAR(8);
DECLARE new_cursor cursor for
SELECT A.datbi Adatbi , A.datab Adatab , H.datbi Hdatbi, H.datab
Hdatab
FROM SAPR3.A957 A
,SAPR3.KONH H
WHERE
(A.DATBI <> H.DATBI
OR A.DATAB <> H.DATAB )
AND A.MANDT = H.MANDT
AND A.KNUMH = H.KNUMH
AND A.MANDT = '030'
for update of Hdatbi,Hdatab ;

open new_cursor;
loop
FETCH new_cursor INTO va_datbi , va_datab , vh_datbi , vh_datab;
UPDATE SAPR3.KONH H
SET H.datbi=va_datbi,
H.datab=va_datab
where CURRENT OF new_cursor
and H.knumh between '0000000000' and '0090000000'
and H.kotabnr='030' ;
end loop ;
CLOSE new_cursor;
commit;
end

When I try and execute it I get the following error .but i am unable to
rectify this , tonight is the deadline for me to submit it into
production, can anyonle please help me out with this.my mail id is
bi******@in.ibm.com

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "UPDATE SAPR3.KONH H SET H.datbi=va_"
was found
following "h_datbi , vh_datab; ". Expected tokens may include:
"<psm_return>". LINE NUMBER=27. SQLSTATE=42601

pads@disrs16:/home/pads/temp>

thanks a lot......

What happens when your cursor hits EOF (+100) on the fetch? There is no
current of cursor at that point. I don't know if that is directly related to
the -104 error you are receiving, but it appears to be a problem.

[bikash_karan]

Hi Mark,

Thanks afor ur response ,
I have included the
Do until sqlcode=100 as shown below :

CREATE PROCEDURE new_procedure1 ()
LANGUAGE SQL
BEGIN
DECLARE va_datbi VARCHAR(8);
DECLARE va_datab VARCHAR(8);
DECLARE vh_datbi VARCHAR(8);
DECLARE vh_datab VARCHAR(8);
DECLARE new_cursor cursor for
SELECT A.datbi Adatbi , A.datab Adatab , H.datbi Hdatbi, H.datab
Hdatab
FROM SAPR3.A957 A
,SAPR3.KONH H
WHERE
(A.DATBI <> H.DATBI
OR A.DATAB <> H.DATAB )
AND A.MANDT = H.MANDT
AND A.KNUMH = H.KNUMH
AND A.MANDT = '030'
for update of Hdatbi,Hdatab ;

OPEN new_cursor;
DO UNTIL SQLCODE = 100;
FETCH new_cursor INTO va_datbi , va_datab , vh_datbi , vh_datab;
UPDATE SAPR3.KONH H
SET H.datbi=va_datbi,
H.datab=va_datab
where CURRENT OF new_cursor
and H.knumh between '0000000000' and '0090000000'
and H.kotabnr='030' ;
end do;
CLOSE new_cursor;
commit;
end

But again i have received this error .....i am at loss.....please guide
me...
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "DO" was found following " OPEN
new_cursor; ".
Expected tokens may include: "<psm_repeat>". LINE NUMBER=21.
SQLSTATE=42601

Best Regards
Bikash

[Mark A]

<bi**********@hotmail.com> wrote in message
news:11********************@f14g2000cwb.googlegrou ps.com...

Hi Mark,

Thanks afor ur response ,
I have included the
Do until sqlcode=100 as shown below :

CREATE PROCEDURE new_procedure1 ()
LANGUAGE SQL
BEGIN
DECLARE va_datbi VARCHAR(8);
DECLARE va_datab VARCHAR(8);
DECLARE vh_datbi VARCHAR(8);
DECLARE vh_datab VARCHAR(8);
DECLARE new_cursor cursor for
SELECT A.datbi Adatbi , A.datab Adatab , H.datbi Hdatbi, H.datab
Hdatab
FROM SAPR3.A957 A
,SAPR3.KONH H
WHERE
(A.DATBI <> H.DATBI
OR A.DATAB <> H.DATAB )
AND A.MANDT = H.MANDT
AND A.KNUMH = H.KNUMH
AND A.MANDT = '030'
for update of Hdatbi,Hdatab ;

OPEN new_cursor;
DO UNTIL SQLCODE = 100;
FETCH new_cursor INTO va_datbi , va_datab , vh_datbi , vh_datab;
UPDATE SAPR3.KONH H
SET H.datbi=va_datbi,
H.datab=va_datab
where CURRENT OF new_cursor
and H.knumh between '0000000000' and '0090000000'
and H.kotabnr='030' ;
end do;
CLOSE new_cursor;
commit;
end

But again i have received this error .....i am at loss.....please guide
me...
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "DO" was found following " OPEN
new_cursor; ".
Expected tokens may include: "<psm_repeat>". LINE NUMBER=21.
SQLSTATE=42601

Best Regards
Bikash

The "do until" condition will not be checked (and will not terminate the
loop) until the last statement in the loop. You need some basic programming
lessons.

It would also be better if you used SQL condition handlers.

[Knut Stolze]

Mark A wrote:

<bi**********@hotmail.com> wrote in message
news:11********************@f14g2000cwb.googlegrou ps.com...

Hi Mark,

Thanks afor ur response ,
I have included the
Do until sqlcode=100 as shown below :

CREATE PROCEDURE new_procedure1 ()
LANGUAGE SQL
BEGIN
DECLARE va_datbi VARCHAR(8);
DECLARE va_datab VARCHAR(8);
DECLARE vh_datbi VARCHAR(8);
DECLARE vh_datab VARCHAR(8);
DECLARE new_cursor cursor for
SELECT A.datbi Adatbi , A.datab Adatab , H.datbi Hdatbi, H.datab
Hdatab
FROM SAPR3.A957 A
,SAPR3.KONH H
WHERE
(A.DATBI <> H.DATBI
OR A.DATAB <> H.DATAB )
AND A.MANDT = H.MANDT
AND A.KNUMH = H.KNUMH
AND A.MANDT = '030'
for update of Hdatbi,Hdatab ;

OPEN new_cursor;
DO UNTIL SQLCODE = 100;
FETCH new_cursor INTO va_datbi , va_datab , vh_datbi , vh_datab;
UPDATE SAPR3.KONH H
SET H.datbi=va_datbi,
H.datab=va_datab
where CURRENT OF new_cursor
and H.knumh between '0000000000' and '0090000000'
and H.kotabnr='030' ;
end do;
CLOSE new_cursor;
commit;
end

Why don't you simply use SQL to solve that problem?

UPDATE sapr3.konh
SET ( datbi, datab ) =
( SELECT a.datbi, a.datab
FROM sapr3.A957 AS a, sapr3.konh AS h
WHERE (a.datbi <> h.datbi OR
a.datab <> h.datab ) AND
a.mandt = h.mandt AND
a.knumh = h.knumh AND
a.mandt = '030' )
WHERE knumh BETWEEN '0000000000' AND '0090000000' AND
kotabnr = '030' AND
( datbi, datab ) IN
( SELECT h.datbi, h.datab
FROM sapr3.A957 AS a, sapr3.konh AS h
WHERE (a.datbi <> h.datbi OR
a.datab <> h.datab ) AND
a.mandt = h.mandt AND
a.knumh = h.knumh AND
a.mandt = '030' )

I don't know exactly what you want to do, but it might very well be possible
to further simplify the statement.

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena

[Serge Rielau]

bi**********@hotmail.com wrote:

Hi Folks ,
I am few days old to the world of DB2 and right now i am stuck into a
problem that seems to be getting difficult for me.
Please have alook into this and provide me some help.

Below is one such procedure that i have written ,

pads@disrs16:/home/pads/temp>db2 -td@ -vf proc2.db2
CREATE PROCEDURE new_procedure1 ()
LANGUAGE SQL
BEGIN
DECLARE va_datbi VARCHAR(8);
DECLARE va_datab VARCHAR(8);
DECLARE vh_datbi VARCHAR(8);
DECLARE vh_datab VARCHAR(8);
DECLARE new_cursor cursor for
SELECT A.datbi Adatbi , A.datab Adatab , H.datbi Hdatbi, H.datab
Hdatab
FROM SAPR3.A957 A
,SAPR3.KONH H
WHERE
(A.DATBI <> H.DATBI
OR A.DATAB <> H.DATAB )
AND A.MANDT = H.MANDT
AND A.KNUMH = H.KNUMH
AND A.MANDT = '030'
for update of Hdatbi,Hdatab ;

open new_cursor;
loop
FETCH new_cursor INTO va_datbi , va_datab , vh_datbi , vh_datab;
UPDATE SAPR3.KONH H
SET H.datbi=va_datbi,
H.datab=va_datab
where CURRENT OF new_cursor
and H.knumh between '0000000000' and '0090000000'
and H.kotabnr='030' ;
end loop ;
CLOSE new_cursor;
commit;
end

When I try and execute it I get the following error .but i am unable to
rectify this , tonight is the deadline for me to submit it into
production, can anyonle please help me out with this.my mail id is
bi******@in.ibm.com

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "UPDATE SAPR3.KONH H SET H.datbi=va_"
was found
following "h_datbi , vh_datab; ". Expected tokens may include:
"<psm_return>". LINE NUMBER=27. SQLSTATE=42601

pads@disrs16:/home/pads/temp>

thanks a lot......

2 things:
This:
and H.knumh between '0000000000' and '0090000000'
and H.kotabnr='030' ;
.... belongs into the cursor declaration. Don't select rows you don't
plan to work with.
If you will do different things with these rows use an IF statement to
control execute the UPDATE WHERE CURRENT OF.

Add thsi variable:
DECLARE SQLCODE INTEGER;

Name the loop:
myloop: LOOP

and test after the FETCH:
IF SQLCODE = 100 -- End of cursor?
THEN
LEAVE myloop;
END IF;

Call me or send email if you get stuck.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[bikash_karan]

Hi Knut ,

I had earlier the same query with me , but I tried the sql given by you
, the result were as per the expected line .

Here is what the output say :
pads@disrs16:/home/pads/temp>cat new_sql.log
SELECT count(1) FROM SAPR3.A957 A ,SAPR3.KONH H WHERE (A.DATBI <>
H.DATBI OR A.
DATAB <> H.DATAB ) AND A.MANDT = H.MANDT AND A.KNUMH = H.KNUMH AND
A.MANDT = '03
0'

1
-----------
215

1 record(s) selected.
UPDATE sapr3.konh SET ( datbi, datab ) = ( SELECT a.datbi, a.datab
FROM sa
pr3.A957 AS a, sapr3.konh AS h WHERE (a.datbi <> h.datbi OR a.datab <>
h.datab
) AND a.mandt = h.mandt AND a.knumh = h.knumh AND a.mandt = '030' )
WHERE knumh
BETWEEN '0000000000' AND '0090000000' AND kotabnr = '030' AND ( datbi,
datab )
IN ( SELECT h.datbi, h.datab FROM sapr3.A957 AS a, sapr3.konh AS h
WHERE (a.d
atbi <> h.datbi OR a.datab <> h.datab ) AND a.mandt = h.mandt AND
a.knumh = h.kn
umh AND a.mandt = '030' )
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0811N The result of a scalar fullselect, SELECT INTO statement, or
VALUES
INTO statement is more than one row. SQLSTATE=21000

SELECT count(1) FROM SAPR3.A957 A ,SAPR3.KONH H WHERE (A.DATBI <>
H.DATBI OR A.
DATAB <> H.DATAB ) AND A.MANDT = H.MANDT AND A.KNUMH = H.KNUMH AND
A.MANDT = '0
3 0'

1
-----------
0

1 record(s) selected.
pads@disrs16:/home/pads/temp>

To rectify the error message :
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0811N The result of a scalar fullselect, SELECT INTO statement, or
VALUES
INTO statement is more than one row. SQLSTATE=21000

I wanted to modify this sql to a PL SQL block, some how I am still
stuck into this as the PLSQL for DB2 is quiet different from the one I
had in Oracle.

Thanks

[Knut Stolze]

bi**********@hotmail.com wrote:

Hi Knut ,

I had earlier the same query with me , but I tried the sql given by you
, the result were as per the expected line .

What exactly is your UPDATE supposed to be doing? You want to set some
values in SAPR3.KONH to what? And which rows shall be modified? I've just
been guessing a lot on the semantics of your code.

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena

[Serge Rielau]

bi**********@hotmail.com wrote:

Hi Knut ,

I had earlier the same query with me , but I tried the sql given by you
, the result were as per the expected line .

Here is what the output say :
pads@disrs16:/home/pads/temp>cat new_sql.log
SELECT count(1) FROM SAPR3.A957 A ,SAPR3.KONH H WHERE (A.DATBI <>
H.DATBI OR A.
DATAB <> H.DATAB ) AND A.MANDT = H.MANDT AND A.KNUMH = H.KNUMH AND
A.MANDT = '03
0'

1
-----------
215

1 record(s) selected.
UPDATE sapr3.konh SET ( datbi, datab ) = ( SELECT a.datbi, a.datab
FROM sa
pr3.A957 AS a, sapr3.konh AS h WHERE (a.datbi <> h.datbi OR a.datab <>
h.datab
) AND a.mandt = h.mandt AND a.knumh = h.knumh AND a.mandt = '030' )
WHERE knumh
BETWEEN '0000000000' AND '0090000000' AND kotabnr = '030' AND ( datbi,
datab )
IN ( SELECT h.datbi, h.datab FROM sapr3.A957 AS a, sapr3.konh AS h
WHERE (a.d
atbi <> h.datbi OR a.datab <> h.datab ) AND a.mandt = h.mandt AND
a.knumh = h.kn
umh AND a.mandt = '030' )
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0811N The result of a scalar fullselect, SELECT INTO statement, or
VALUES
INTO statement is more than one row. SQLSTATE=21000

SELECT count(1) FROM SAPR3.A957 A ,SAPR3.KONH H WHERE (A.DATBI <>
H.DATBI OR A.
DATAB <> H.DATAB ) AND A.MANDT = H.MANDT AND A.KNUMH = H.KNUMH AND
A.MANDT = '0
3 0'

1
-----------
0

1 record(s) selected.
pads@disrs16:/home/pads/temp>

To rectify the error message :
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0811N The result of a scalar fullselect, SELECT INTO statement, or
VALUES
INTO statement is more than one row. SQLSTATE=21000

I wanted to modify this sql to a PL SQL block, some how I am still
stuck into this as the PLSQL for DB2 is quiet different from the one I
had in Oracle.

Thanks

You got confused on the SET clause. MERGE is easier to understand:

MERGE INTO sapr3.konh as h
USING sapr3.A957 AS a
ON a.mandt = h.mandt AND a.knumh = h.knumh AND a.mandt = '030'
and a.datbi <> h.datbi OR a.datab <> h.datab
and knumh BETWEEN '0000000000' AND '0090000000' AND kotabnr = '030'
WHEN MATCHED THEN UPDATE
SET ( datbi, datab ) = (a.datbi, a.datab)

The ON clause is a best guess, but I hope you'll figure it out from here

Cheers
Serge

PS: I'm serious about you calling me to help if needed.
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Tonkuma]

How about this?

UPDATE sapr3.konh h
SET ( datbi, datab ) =
( SELECT a.datbi, a.datab
FROM sapr3.A957 AS a
WHERE (a.datbi <> h.datbi OR
a.datab <> h.datab ) AND
a.mandt = h.mandt AND
a.knumh = h.knumh AND
a.mandt = '030' )
WHERE knumh BETWEEN '0000000000' AND '0090000000' AND
kotabnr = '030' AND
( datbi, datab ) IN
( SELECT h.datbi, h.datab
FROM sapr3.A957 AS a, sapr3.konh AS h
WHERE (a.datbi <> h.datbi OR
a.datab <> h.datab ) AND
a.mandt = h.mandt AND
a.knumh = h.knumh AND
a.mandt = '030' )

[Tonkuma]

There is no "DO" statement in DB2 (procedure) statements.
Please use WHILE, REPEAT, LOOP(with LEAVE) or FOR for loop logic.

[bikash_karan]

Hi Serge,

I modified the procedure a bit and executed it . It gave me an error .
I am here with attaching the Procedure also . I think there is some
issue at the update statment. .Can you help me a little further.
pads@disrs16:/home/pads/temp>db2 -td@ -vf proc3.db2 > proc3.log
pads@disrs16:/home/pads/temp>cat proc3.log
create procedure new_proc()
language sql
begin
DECLARE cnt INTEGER;
DECLARE V_DATAB VARCHAR(8);
DECLARE V_DATBI VARCHAR(8);
DECLARE AT_END INT DEFAULT 1;
DECLARE cur1 CURSOR FOR
SELECT H.DATAB
, H.DATBI
FROM SAPR3.KONH H
WHERE H.KOTABNR='957'
AND H.MANDT = '030'
AND H.KNUMH BETWEEN '0000000000' AND '0090000000'
AND exists ( SELECT 1 from SAPR3.A957 A
WHERE
H.MANDT = A.MANDT
AND H.KNUMH = A.KNUMH
AND (H.DATAB <> A.DATAB OR H.DATBI <> A.DATBI ))
FOR UPDATE;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET AT_END = 0;
SET cnt = 0;
OPEN cur1;
--myloop: LOOP
WHILE AT_END=1
DO
FETCH cur1 INTO V_DATAB, V_DATBI;
--IF SQLCODE = 100 THEN LEAVE myloop; END IF;
UPDATE SAPR3.KONH H SET (H.DATAB,
H.DATBI
) =
(SELECT A.DATAB
, A.DATBI
FROM SAPR3.A957 A
WHERE A.MANDT = H.MANDT
AND A.KNUMH = H.KNUMH)
WHERE CURRENT OF cur1;
SET cnt = cnt + 1;
IF cnt = 1000 THEN
SET cnt = 0;
COMMIT;
END IF;
--END LOOP;
END WHILE;
CLOSE cur1;
COMMIT;
end
DB20000I The SQL command completed successfully.

pads@disrs16:/home/pads/temp>
SELECT A.DATAB , A.DATBI FROM SAPR3.A957 A WHERE A.MANDT = H.MANDT
AND A.KNUMH = H.KNUMH

DB20000I The SQL command completed successfully.

pads@disrs16:/home/pads/temp>db2 "CALL new_proc()"
SQL0508N The cursor specified in the UPDATE or DELETE statement is not
position
ed on a row. SQLSTATE=24504

pads@disrs16:/home/pads/temp>db2 "CALL new_proc()"
SQL0508N The cursor specified in the UPDATE or DELETE statement is not
position
ed on a row. SQLSTATE=24504.

[Mike Gemmell]

Testing whether the cursor is at the end of the data should happen
after the fetch.

try

open...;

myloop:
loop
fetch...;
if at_end=0 then leave myloop; end;
update...;
end loop;

[p175]

Try moving the the fetch before the while statement then add a second
fetch before the end while.

FETCH cur1 INTO V_DATAB, V_DATBI;
WHILE AT_END=1
DO
--IF SQLCODE = 100 THEN LEAVE myloop; END IF;
UPDATE SAPR3.KONH H SET (H.DATAB,
H.DATBI
) =
(SELECT A.DATAB
, A.DATBI
FROM SAPR3.A957 A
WHERE A.MANDT = H.MANDT
AND A.KNUMH = H.KNUMH)
WHERE CURRENT OF cur1;
SET cnt = cnt + 1;
IF cnt = 1000 THEN
SET cnt = 0;
COMMIT;
END IF;
--END LOOP;
FETCH cur1 INTO V_DATAB, V_DATBI;
END WHILE;
CLOSE cur1;
COMMIT;

Thats the way I'd do it at least. Serge may have a better suggestion of
course.

Regards, Tim

[Knut Stolze]

p175 wrote:

Try moving the the fetch before the while statement then add a second
fetch before the end while.

FETCH cur1 INTO V_DATAB, V_DATBI;
WHILE AT_END=1
DO
--IF SQLCODE = 100 THEN LEAVE myloop; END IF;
UPDATE SAPR3.KONH H SET (H.DATAB,
H.DATBI
) =
(SELECT A.DATAB
, A.DATBI
FROM SAPR3.A957 A
WHERE A.MANDT = H.MANDT
AND A.KNUMH = H.KNUMH)
WHERE CURRENT OF cur1;
SET cnt = cnt + 1;
IF cnt = 1000 THEN
SET cnt = 0;
COMMIT;
END IF;
--END LOOP;
FETCH cur1 INTO V_DATAB, V_DATBI;
END WHILE;
CLOSE cur1;
COMMIT;

Thats the way I'd do it at least. Serge may have a better suggestion of
course.

That's a good source for errors if you have to change the FETCH sometime.
So I usually do this in C/Java:

while (1)
{
FETCH ...
if (SQLCODE == 100) {
break;
}
...
}

You can do the same in SQL/PL.

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena

[bikash_karan]

Hi Knut ,

Here is my final piece of code :

pads@disrs16:/home/pads/temp>cat sql7.log
select a.knumh , a.datab, a.datbi, h.knumh, h.datab,h.datbi FROM
SAPR3.A957 A ,
SAPR3.KONH H WHERE (A.DATBI <> H.DATBI OR A.DATAB <> H.DATAB ) AND
A.MANDT = H
MANDT AND A.KNUMH = H.KNUMH AND A.MANDT = '030'

KNUMH DATAB DATBI KNUMH DATAB DATBI
---------- -------- -------- ---------- -------- --------
0000167338 19980130 99991231 0000167338 19990824 99991231
0000169977 19981215 99991231 0000169977 19990824 99991231
0000174773 19990126 99991231 0000174773 19950405 99991231
0000178378 19950530 20010418 0000178378 19991125 99991231
0000198867 19971028 20010418 0000198867 20000426 99991231
0000200032 19970325 20010418 0000200032 19990824 99991231
0000207050 19970910 20010424 0000207050 19990824 99991231
0000228838 19970325 99991231 0000228838 19990824 99991231
0000232534 19970506 99991231 0000232534 19990824 20040614
0000232568 19970910 20010424 0000232568 19990824 20040614
0000237603 19970909 20010424 0000237603 19990824 99991231
0000240027 19961121 20010418 0000240027 19990824 99991231
0000248230 19970910 99991231 0000248230 19990824 99991231
0000249638 19990527 19991107 0000249638 19990824 99991231
0000267431 19970401 99991231 0000267431 19990824 99991231
My requirement is to make h.datab,h.datbi equal to a.datab, a.datbi
for each corresponding to the value of h.knumh.
pads@disrs16:/home/pads/temp>cat sql9.sql
SELECT H.DATAB , H.DATBI,H.KNUMH FROM SAPR3.KONH H
WHERE H.KOTABNR='956' AND H.MANDT = '030'
AND H.KNUMH BETWEEN '0000000000' AND '0900000000'
AND exists ( SELECT 1 from SAPR3.A957 A WHERE
H.MANDT = A.MANDT AND H.KNUMH = A.KNUMH
AND (H.DATAB <> A.DATAB OR H.DATBI <> A.DATBI ) );
This gave me the last 3 column as above. This select query is the same
as the above .

There after I have written the following procedure to do the same :

create procedure karan_prince()
language sql
modifies sql data
begin
DECLARE cnt INTEGER;
DECLARE SQLCODE INTEGER;
DECLARE V_ADATAB VARCHAR(8);
DECLARE V_ADATBI VARCHAR(8);
DECLARE V_AKNUMH VARCHAR(8);
DECLARE V_HDATAB VARCHAR(8);
DECLARE V_HDATBI VARCHAR(8);
DECLARE V_HKNUMH VARCHAR(8);
DECLARE AT_END INT DEFAULT 1;
DECLARE not_found CONDITION FOR SQLSTATE '02000';

DECLARE cur1 CURSOR FOR
SELECT H.DATAB , H.DATBI,H.KNUMH
FROM SAPR3.KONH H
WHERE H.KOTABNR='956' AND
H.MANDT = '030' AND H.KNUMH BETWEEN '0000000000' AND '0900000000' AND
exists (select 1 from SAPR3.A957 A WHERE H.MANDT = A.MANDT AND
H.KNUMH = A.KNUMH AND (H.DATAB <> A.datab or h.datbi <> a.datbi)) for
update ;

DECLARE CONTINUE HANDLER FOR not_found SET AT_END = 0;
SET cnt = 0;
OPEN cur1;
loop_stmt : loop
FETCH cur1 INTO v_hdatab,v_hdatbi,v_hknumh;
if sqlcode=100 then leave loop_stmt; end if;
select datab , datbi into v_adatab , v_adatbi from sapr3.a957 where
knumh=v_hknumh;
UPDATE SAPR3.KONH H SET H.DATAB =V_ADATAB, H.DATBI=V_ADATBI where
current of cur1;
SET cnt = cnt + 1;
IF cnt = 100 THEN SET cnt = 0; COMMIT;
END IF;
end loop loop_stmt;
CLOSE cur1;
COMMIT;
end @
pads@disrs16:/home/pads/temp>
But still I find something is wrong ....as while executing it hangs.

Knut Stolze wrote:

p175 wrote:

Try moving the the fetch before the while statement then add a second
fetch before the end while.

FETCH cur1 INTO V_DATAB, V_DATBI;
WHILE AT_END=1
DO
--IF SQLCODE = 100 THEN LEAVE myloop; END IF;
UPDATE SAPR3.KONH H SET (H.DATAB,
H.DATBI
) =
(SELECT A.DATAB
, A.DATBI
FROM SAPR3.A957 A
WHERE A.MANDT = H.MANDT
AND A.KNUMH = H.KNUMH)
WHERE CURRENT OF cur1;
SET cnt = cnt + 1;
IF cnt = 1000 THEN
SET cnt = 0;
COMMIT;
END IF;
--END LOOP;
FETCH cur1 INTO V_DATAB, V_DATBI;
END WHILE;
CLOSE cur1;
COMMIT;

Thats the way I'd do it at least. Serge may have a better suggestion of
course.

That's a good source for errors if you have to change the FETCH sometime.
So I usually do this in C/Java:

while (1)
{
FETCH ...
if (SQLCODE == 100) {
break;
}
...
}

You can do the same in SQL/PL.

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena

[Knut Stolze]

bi**********@hotmail.com wrote:

Hi Knut ,

Here is my final piece of code :

pads@disrs16:/home/pads/temp>cat sql7.log
select a.knumh , a.datab, a.datbi, h.knumh, h.datab,h.datbi FROM
SAPR3.A957 A ,
SAPR3.KONH H WHERE (A.DATBI <> H.DATBI OR A.DATAB <> H.DATAB ) AND
A.MANDT = H
MANDT AND A.KNUMH = H.KNUMH AND A.MANDT = '030'

KNUMH DATAB DATBI KNUMH DATAB DATBI
---------- -------- -------- ---------- -------- --------
0000167338 19980130 99991231 0000167338 19990824 99991231
0000169977 19981215 99991231 0000169977 19990824 99991231
0000174773 19990126 99991231 0000174773 19950405 99991231
0000178378 19950530 20010418 0000178378 19991125 99991231
0000198867 19971028 20010418 0000198867 20000426 99991231
0000200032 19970325 20010418 0000200032 19990824 99991231
0000207050 19970910 20010424 0000207050 19990824 99991231
0000228838 19970325 99991231 0000228838 19990824 99991231
0000232534 19970506 99991231 0000232534 19990824 20040614
0000232568 19970910 20010424 0000232568 19990824 20040614
0000237603 19970909 20010424 0000237603 19990824 99991231
0000240027 19961121 20010418 0000240027 19990824 99991231
0000248230 19970910 99991231 0000248230 19990824 99991231
0000249638 19990527 19991107 0000249638 19990824 99991231
0000267431 19970401 99991231 0000267431 19990824 99991231
My requirement is to make h.datab,h.datbi equal to a.datab, a.datbi
for each corresponding to the value of h.knumh.

You post the result of a join. But I assume you want to update the data in
just one table, i.e. table KONH, right? Also, does KNUMH uniquely identify
a row in A957 as well as in KONH? I'm asking because I don't understand
what MANDT is doing in your query. If not, you'll have to tell us how a
row in each of the tables can be identified and how to link a row in one
table with exactly one row in the other.

UPDATE sapr3.konh AS h
-- set the new values for all qualifying rows
SET ( datab, datbi ) =
( SELECT a.datab, a.datbi
FROM sapr3.a957 AS a
WHERE h.knumh = a.knumh )
-- find all rows from KONH that shall be updated
WHERE EXISTS ( SELECT 1
FROM sapr3.a957 AS a
WHERE -- find the correct/matching row
h.knumh = a.knumh AND
-- consider only rows with differing values
-- (can there be NULLs?)
( a.datbi <> h.datbi OR a.datab <> h.datab ) AND
a.mandt = '030' AND
-- whatever this is good for...
a.mandt = h.mandt )

You should verify that there is no issue with respect to handling NULLs when
checking for data changes.

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena

[p175]

Seeing as you have a commit cnt, does the cursor need to be declared
WITH HOLD ?