Elke Schmidtke wrote: > > Hello all, > > In our production system we use version 7.5.0.19. > Same days ago I made an update of my development system to 7.6.0.32. > Now I got a problem with a procedure. > The procedure returns a cursor. > If no data are returned on 7.5.0.19 I get the message "No result", > on 7.6.0.32 "General error;-4024 POS(1) " is produced. > > I can avoid the error by making a better (safer) code as shown in the > following text. > But it is a hard work tosearch through all procedures and triggers for > a similar problem. > > Elke > > CREATE DBPROC PROC_DBV_SCHULEN (IN PNR INT) RETURNS CURSOR AS > VAR BEG1 DATE; END1 DATE; ID1 INT; BEG2 DATE; END2 DATE; ID2 INT; > > TRY > DELETE FROM TEMP.DBV_SCHULEN; > CATCH > CREATE TABLE TEMP.DBV_SCHULEN (BEGINN DATE, ENDE DATE, SCHUL_ID > INT); > > /* If temporary table is empty after this select, the error *4024 is > produced > To avoid it I added try-cath */ > TRY > INSERT INTO TEMP.DBV_SCHULEN > SELECT BEGINN, ENDE, SCHUL_ID FROM DBA.T_DBV > WHERE PERS_NR = :PNR; > CATCH > DECLARE :$CURSOR CURSOR FOR > SELECT 'keine' AS Schule FROM SYSDBA.DUAL; > > DECLARE CC CURSOR > FOR SELECT * FROM TEMP.DBV_SCHULEN > ORDER BY BEGINN FOR REUSE; > > FETCH CC INTO :BEG1, :END1, :ID1; > WHILE $RC = 0 DO > BEGIN > FETCH CC INTO :BEG2, :END2, :ID2; > IF $RC = 100 THEN > BREAK; > > IF ID1 = ID2 AND ADDDATE(END1,1) = BEG2 THEN > BEGIN > UPDATE TEMP.DBV_SCHULEN > SET ENDE = :END2 WHERE SCHUL_ID = :ID1 AND ENDE = > :END1; > DELETE FROM TEMP.DBV_SCHULEN > WHERE SCHUL_ID = :ID1 AND BEGINN = :BEG2; > END; > SET BEG1 = BEG2; > SET END1= END2; > SET ID1=ID2; > END; > > DECLARE :$CURSOR CURSOR FOR > SELECT BEGINN, ENDE, CHR(SCHUL_NR,6)&', '&SCHULNAME&' '&ORT& ' ' & > DECODE (CASE WHEN SCHLIESSDATUM ='2099-12-31' THEN NULL > ELSE SCHLIESSDATUM END , NULL , ' ', > '(Schließung:'&CHR(DAY(SCHLIESSDATUM > ))&'.'&CHR(MONTH(SCHLIESSDATUM ))&'.'&CHR(YEAR(SCHLIESSDATUM ))&')') > AS SCHULE > FROM TEMP.DBV_SCHULEN, DBA.T_SCHULE > WHERE TEMP.DBV_SCHULEN.SCHUL_ID = T_SCHULE.SCHUL_ID > ORDER BY BEGINN DESC; >
After some analysis it was found out, that at Elke Schmidtke's side it was forgotten that few schools may have schul_nr with more than 6 digits causing chr (schul_nr,6) to fail --> causing the last select to fail, causing the problem mentioned. --> fortunately no db-problem and chr(schul_nr, 7) makes things work Elke SAP Labs Berlin > > > > -- > 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]
