FW: nested while loops and concatenating variables

[Anhaus, Thomas]

Filip Sergeys wrote :

>Hello,

>2 questions again:

>1) Are nested while loops possible or not?
>I have followed the recommendation in this mail
>(http://archives.neohapsis.com/archives/dev/sapdb/2003-q3/0743.html) and
>named each select (see 'code snipped' below) but I have the same problem
>as the poster it iterates once (1 time) and then exits.
>I also tried with IF $RC=0 THEN BEGIN... just behind the FETCH.
>What are the possibilities for nested while loops?

We have no indication that nested loop are not possible. 
If the inner loop in your example terminates, error 100
is thrown by the fetch statement and the catch handler
is executed. This means that the outer loop is also terminated.
You therefore should do without a TRY/CATCH block   
Please try the following code : 

DECLARE REQID CURSOR FOR SELECT DISTINCT(A.REQUESTID) AS REQUESTID
FROM DELTA.T_REQUEST A, DELTA.T_REQUEST B, COMMON.T_OFFICE B1,
COMMON.T_EMPLOYEE B2, DELTA.T_REQUEST C
WHERE B.REQUESTID=A.REQUESTID
AND B1.OFFICEID=A.VALUE
AND B2.EMPLOYEEID=B.VALUE
AND C.REQUESTID=A.REQUESTID
AND A.KEY = 'OFFICEID'
AND A.VALUE = :officeid
AND B.KEY = 'EMPLOYEEID'
AND B.VALUE = :employeeid
AND B1.KEY = 'DESCRIPTION'
AND B2.KEY = 'NAME'
AND C.KEY = 'REQUEST_TYPE'
AND C.VALUE= :reqtype
ORDER BY REQUESTID ASC;
WHILE $RC=0 DO BEGIN
    FETCH REQID INTO :requestid;
    IF $RC <> 0 THEN BREAK;
    DECLARE GTXT CURSOR FOR SELECT TRIM(C.TXT)
        FROM DELTA.L_RQTE3GUARANTEE A, COMMON.T_E3GUARANTEE B,
COMMON.T_MESSAGELNG C
        WHERE A.IDE3=B.IDE3
        AND B.SEQMSGTEXT = C.SEQMSG
        AND A.REQUESTID = :requestid
        AND C.SEQLNG = :language;
        WHILE $RC=0 DO BEGIN
            FETCH GTXT INTO :guarantee;
            IF $RC <> 0 THEN BREAK;
            SET GUARANTEETXT = GUARANTEETXT&' '&GUARANTEE;
            UPDATE TEMP.BOREQSELECT SET GUARANTEETXT = :guaranteetxt
            WHERE REQUESTID = :requestid;
            IF $RC <> 0
            THEN
                STOP (-31001, 'unexpected error in update ' || chr($rc));
            SET GUARANTEETXT = '0';
       END;
       IF $RC = 100 THEN $RC = 0;
END;
IF $RC<>100 THEN STOP($RC, 'THE GUARANTEE COMPOSITION FAILED');

>2) concatenating 2 variables in a dbproc
>e.g.: 
> FETCH GTXT INTO :guarantee;
> SET GUARANTEETXT = GUARANTEETXT&' '&GUARANTEE;

>I tried it the like in a select (select column1&' '&column2 from ...)
>but it returns a `?` instead of the concatenated string.
>I also tried it like described in string functions
>http://www.mysql.com/documentation/maxdb/ed/0df209d90e11d5994500508b6b8b11/content.htm
>SET GUARANTEETXT = GUARANTEETXT & GUARANTEE;
>but also not correct.
>What is the correct way to obtain a concatenated string in a dbproc?

Probably you did not initialize the variable GUARANTEETXT. In this case
GUARANTEETXT is null and the concatanation yields null also.

Regards,

Thomas


===code snipped============
TRY
DECLARE REQID CURSOR FOR SELECT DISTINCT(A.REQUESTID) AS REQUESTID
FROM DELTA.T_REQUEST A, DELTA.T_REQUEST B, COMMON.T_OFFICE B1,
COMMON.T_EMPLOYEE B2, DELTA.T_REQUEST C
WHERE B.REQUESTID=A.REQUESTID
AND B1.OFFICEID=A.VALUE
AND B2.EMPLOYEEID=B.VALUE
AND C.REQUESTID=A.REQUESTID
AND A.KEY = 'OFFICEID'
AND A.VALUE = :officeid
AND B.KEY = 'EMPLOYEEID'
AND B.VALUE = :employeeid
AND B1.KEY = 'DESCRIPTION'
AND B2.KEY = 'NAME'
AND C.KEY = 'REQUEST_TYPE'
AND C.VALUE= :reqtype
ORDER BY REQUESTID ASC;
WHILE $RC=0 DO BEGIN
    FETCH REQID INTO :requestid;
        DECLARE GTXT CURSOR FOR SELECT TRIM(C.TXT)
        FROM DELTA.L_RQTE3GUARANTEE A, COMMON.T_E3GUARANTEE B,
COMMON.T_MESSAGELNG C
        WHERE A.IDE3=B.IDE3
        AND B.SEQMSGTEXT = C.SEQMSG
        AND A.REQUESTID = :requestid
        AND C.SEQLNG = :language;
        WHILE $RC=0 DO BEGIN
            FETCH GTXT INTO :guarantee;
            SET GUARANTEETXT = GUARANTEETXT&' '&GUARANTEE;
            UPDATE TEMP.BOREQSELECT SET GUARANTEETXT = :guaranteetxt
WHERE REQUESTID = :requestid;
            SET GUARANTEETXT = '0';
       END;
END;
CATCH
IF $RC<>100 THEN STOP($RC, 'THE GUARANTEE COMPOSITION FAILED');
===code snipped===

-- 
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
* System Engineer, Verzekeringen NV *
* www.verzekeringen.be              *
* Oostkaai 23 B-2170 Merksem        *
* 03/6416673 - 0477/340942          *
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*


-- 
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

-- 
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]