Sybase Procedure

[Former Member]

Hy,

I try to make a Procedure in Sybase Central.

It doesn't work. Some idea ???

CREATE PROCEDURE "PBS"."YBWI_PROC"() as
 BEGIN
    -- 1. Declare the "error not found" exception
    DECLARE err_notfound
    EXCEPTION FOR SQLSTATE '02000';
    -- 2.   Variable erstellen für den Werttyp
    DECLARE V_IK NUMERIC(9);
    DECLARE V_KEY NUMERIC(1,0);
    DECLARE V_DATEA NUMERIC(6,0);    
    DECLARE V_WERTTYP VARCHAR(4);
    DECLARE @sqlcmd varchar(999)
    -- 3.   Cursor für die Auslese definieren
    DECLARE YBWIIRW CURSOR FOR 
    SELECT IK, KEY, DATEA
    FROM "PBS"."YBWIIRW"
    group by IK, KEY, DATEA;
    -- 4. Cursor öffnen
    OPEN YBWIIRW;
    -- 5. Loop über alle Ergebnisse
    YBWIIRWLoop:
    WHILE 
       FETCH NEXT YBWIIRW
          INTO V_IK, V_KEY,V_DATE;
       IF EOF THEN
          LEAVE YBWIIRWLoop;
       END IF;

    select @sqlcmd =
    'select max(TYP) as max_typ from "PBS"."YBWIIRW" where ' +
    'IK = ' + V_IK + ' and KEY = ' + V_KEY + ' and DATEA = ' + V_DATEA;
    execute (@sqlcmd)

    select @sqlcmd =
    'update "PBS"."YBWIIRW" a set a.PLANWERT= b.planwert from "DBA"."GDB_S_PLANWERTE" b where a."IK" = ' + V_IK + ' and a."KEY" = ' + V_KEY +
    ' and a."DATEA" = ' + V_DATEA + ' and a.TYP = ' + max_typ
    execute (@sqlcmd)

    END LOOP YBWIIRWLoop;
    -- 7. Close the cursor
    CLOSE YBWIIRW;
 END

[VolkerBarth]

Can you please be more specific than

"It doesn't work."

An error message (if the procedure cannot be created) or a description of differences between the desired and the actual effects would surely be helpful.

[chris_keating]

You are mixing Watcom and TSQL dialects. You must remove the AS for the exception handler to be understood. Also, you should be using SET rather than SELECT variable = to assign values for Watcom dialect procedures. You may want to use EXECUTE IMMEDIATE rather than execute(variable) for that dialect also.

There are other issues with this procedure including the use of keywords such as "KEY" which may need to be quoted depending on your quoted_identifiers setting. Here is a rewrite that has no syntax errors. Other problems include missing statement separatators and a bad LOOP construct which mixed the LOOP ... END LOOP syntax with a bad WHILE contruct.

 CREATE PROCEDURE "YBWI_PROC"()
 BEGIN

DECLARE err_notfound
    EXCEPTION FOR SQLSTATE '02000';

DECLARE V_IK NUMERIC(9);
    DECLARE V_KEY NUMERIC(1,0);
    DECLARE V_DATEA NUMERIC(6,0);
    DECLARE V_WERTTYP VARCHAR(4);
    DECLARE @sqlcmd varchar(999);

DECLARE YBWIIRW CURSOR FOR
    SELECT IK, "KEY", DATEA
    FROM "PBS"."YBWIIRW"
    group by IK, "KEY", DATEA;

OPEN YBWIIRW;

YBWIIRWLoop:
    LOOP
           FETCH NEXT YBWIIRW
          INTO V_IK, V_KEY,V_DATE;
       IF SQLCODE <> 0 THEN
          LEAVE YBWIIRWLoop;
       END IF;

set  @sqlcmd =
    'select max(TYP) as max_typ from "PBS"."YBWIIRW" where ' +
    'IK = ' + V_IK + ' and KEY = ' + V_KEY + ' and DATEA = ' + V_DATEA;
    execute (@sqlcmd);

set @sqlcmd =
    'update "PBS"."YBWIIRW" a set a.PLANWERT= b.planwert from "DBA"."GDB_S_PLANWERTE" b where a."IK" = ' + V_IK + ' and a."KEY" = ' + V_KEY +
    ' and a."DATEA" = ' + V_DATEA + ' and a.TYP = ' + max_typ;
    execute (@sqlcmd);

END LOOP YBWIIRWLoop;

CLOSE YBWIIRW;
 END

[VolkerBarth]

Besides my comment on the question, some hints:

• You do not need dynamic SQL (aka an EXECUTE statement) to build a SQL statement based on SQL variables - they are known within the procedure's body, so a simple

  where IK = V_IK and KEY = V_KEY and DATEA = V_DATEA

should do, and the SELECT/UPDATE statement can be run "normally". (However, "KEY" may be a reserved word and might need quoting.)

• An UPDATE based on a max value can be done without a cursor loop - you could just put the "max selection" into the UPDATE's join condition. That would let you omit all the variable and exception handling stuff.

• If you do prefer a cursor loop, you may take a look at the FOR statement, which combines several cursor handling "steps" into a very handy form - again omitting the check for the "row not found" warning (which is not an error, BTW).

[VolkerBarth]

Just to add: If you would explain on the relationships of the two tables YBWIIRW and GDB_S_PLANWERTE (and what their respective PKs are), we might give a simpler answer just omitting the cursor loop.

I'd tried to formulate such a query (with a single UPDATE statement), however I noticed that I don't really know enough details, and I was somewhat irritated that your cursor loop seems to filter more strictly on table YBWIIRW than the UPDATE statement within does - at least that was my impression...

[Former Member]

Thx for the code.

I got only one problem now.

When i execute the procedure the field Typ is normaly a varchar(4) field. He try to convert this to numeric(126,38). Why or where can i told the procedure that the max_typ field is a varchar field. The exactly error code is:

Could not execute statement.
Cannot convert select max(TYP) as max_typ to a numeric(126,38)
SQLCODE=-157, ODBC 3 STATE ="07006"

[thomas_duemesnil]

look at CAST( as int) in the Doku.

[Former Member]

I try set @sqlcmd='select max(cast(WERTTYP as varchar(4))) as max_type from "PBS"."PBS_KRH_YBWIIRW" where '+'IK_KRH = '+ V_IK_KRH+' and GELTUNGSBEREICH = '+V_GELTUNGSBEREICH+' and KAL_JAHR_MONAT = '+V_KAL_JAHR_MONAT;

and

set @sqlcmd='select max(WERTTYP) as cast(max_type as varchar(4)) from "PBS"."PBS_KRH_YBWIIRW" where '+'IK_KRH = '+ V_IK_KRH+' and GELTUNGSBEREICH = '+V_GELTUNGSBEREICH+' and KAL_JAHR_MONAT = '+V_KAL_JAHR_MONAT;

Nothing works, where should i insert the CAST ???

[Breck_Carter]

Show us all the code.

The error message is saying it can't convert "select max(TYP) as max_typ" which is very strange.

[chris_keating]

I am confused. You state that you want to convert to numeric(126,38) yet the code that you are trying is converting it to a varchar(4) - which you claim was the original datatype. Can you clarify?

I assume that the MAX(col_name) works correctly and you simply want to then cast the result to numeric(126,38), correct?

Can you post the current procedure code highlighting the statement that is causing this error?

[graeme_perrow]

Please don't add answers that don't actually answer the original question. If you need to add more information, you can edit the original question. If someone asks you a question in a comment, answer that question in a comment.

[Former Member]

OK sorry, here is the answer.

Here ist the complete Code:

ALTER PROCEDURE "PBS"."PBS_KRH_YBWIIRW"()
begin
  declare err_notfound exception for sqlstate value '02000';
  declare V_IK_KRH numeric(9);
  declare V_GELTUNGSBEREICH numeric(1);
  declare V_KAL_JAHR_MONAT numeric(6);
  declare V_WERTTYP varchar(4);
  declare v_max_typ varchar(4);
  declare @sqlcmd varchar(999);
  declare YBWIIRW dynamic scroll cursor for select IK_KRH,GELTUNGSBEREICH,KAL_JAHR_MONAT from
      PBS.PBS_KRH_YBWIIRW
      group by IK_KRH,GELTUNGSBEREICH,KAL_JAHR_MONAT;
  open YBWIIRW;
  YBWIIRWLoop: loop
    fetch next YBWIIRW into V_IK_KRH,
      V_GELTUNGSBEREICH,V_KAL_JAHR_MONAT;
    if sqlcode <> 0 then
      leave YBWIIRWLoop
    end if;
    set @sqlcmd='**select max(WERTTYP) as max_type** from "PBS"."PBS_KRH_YBWIIRW" where '+'IK_KRH = '+
      V_IK_KRH+' and GELTUNGSBEREICH = '+V_GELTUNGSBEREICH+' and KAL_JAHR_MONAT = '+V_KAL_JAHR_MONAT;
    execute immediate @sqlcmd;
    set @sqlcmd='update "PBS"."PBS_KRH_YBWIIRW" a set a.PLANWERT= b.planwert from "DBA"."GDB_S_PLANWERTE" b where a."IK_KRH" = '+
      V_IK_KRH+' and a."GELTUNGSBEREICH" = '+V_GELTUNGSBEREICH+' and a."KAL_JAHR_MONAT" = '+
      V_KAL_JAHR_MONAT+' and a.WERTTYP = '+ max_type;
    execute immediate @sqlcmd
  end loop YBWIIRWLoop;
  close YBWIIRW
end

Sorry about the confusion. 🐵 Only in the error message he told me the numeric(126,38) field, the field is varchar(4), also on the Database. I don't want to convert it to a numeric field. I need this Field only for the Update statement. And i don't understand the Error, cause the Field is always varchar(4).

[chris_keating]

And I made an error in my rewrite. Instead of using execute or execute immediate, you should execute those directly as per Volker's post. Here is some additional changes

Add a new variable to hold the max value as in: declare max_type varchar(4);

and change the @sqlcmd to SQL statements as in the following:

select max(WERTTYP) into max_type 
  from "PBS"."PBS_KRH_YBWIIRW" 
 where IK_KRH = V_IK_KRH
       and GELTUNGSBEREICH = V_GELTUNGSBEREICH 
       and KAL_JAHR_MONAT = V_KAL_JAHR_MONAT;

update "PBS"."PBS_KRH_YBWIIRW" a set a.PLANWERT= b.planwert 
  from "DBA"."GDB_S_PLANWERTE" b 
 where a."IK_KRH" = V_IK_KRH
    and a."GELTUNGSBEREICH" = V_GELTUNGSBEREICH
    and a."KAL_JAHR_MONAT" = V_KAL_JAHR_MONAT
    and a.WERTTYP =  max_type;

[Former Member]

Hy Chris,

thanks a lot. It works at this moment. I got one problem now, nothing happen in the database. IS there a chance that i can use something like "MSGBOX" in the statement, to show the value of the variables like V_KRH_IK. I use the Sybase Central and the procedure runs in the "Interactive SQL" Console. And i want to see the value in the Result Box.

Is there a chance ???

[chris_keating]

There is a debugger in Sybase Central. Give it a spin as it works great. You can use the MESSAGE statement to handle messages and it can redirect to the engine console or the client.