Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2012
    Posts
    7

    Unanswered: Dynamic SQL rowcount conversion problem

    Hello guys,

    I have a big problem and no clue how to solve it. The following stored procedure always throws an error if the variable V_MANDANT is a char/varchar. I already tried casting the rowcount into char and then into varchar, but I still get the following error-msg:

    Error Code: -206, SQL State: 42703] "G12198800" not valid in the context where it is used. SQLSTATE=42703

    Code:
    CREATE OR REPLACE PROCEDURE "ADDON21C"."STP_SENDUNGSID_SET_TEMP"
    (
            IN @FACHTABELLE Varchar(100), -- Fachtabelle bzw. Fachview
            IN @MANDANT SMALLINT
    )
    LANGUAGE SQL
    SPECIFIC SQL120511110539422
    BEGIN
      DECLARE V_ROWS_UPDATED INTEGER;
      DECLARE V_SQLUPDATE VARCHAR(1000); -- Variable für Updatestatement des SendungsIdcounts (Dynamic SQL) 
      DECLARE V_SQLMERGE VARCHAR(1000); -- Variable für Merge Into Statement für das Erstellen der SendungsIds (Dynamic SQL)
      DECLARE V_JAHR VARCHAR(2);
      DECLARE V_TAG VARCHAR(5) ;
      DECLARE V_MANDANT VARCHAR(1);
      DECLARE V_SENDUNGSID VARCHAR(15);
      DECLARE V_DATE DATE; 
      DECLARE V_JOBMED VARCHAR(3);
      
      SET V_DATE = CURRENT DATE;  
      SET V_JAHR = RIGHT(CAST(YEAR(V_DATE) AS VARCHAR(4)), 2); -- lediglich die ersten zwei Zeichen des Jahres
      SET V_TAG = '00' || CAST(DAYOFYEAR(CURRENT DATE) AS VARCHAR(5)); -- Tag mit vorangehender 0
      SET V_TAG = RIGHT(V_TAG, 3);
      SET V_JOBMED = '800';
      
      -- Prüfen, ob heute schon SendungsIds gesetzt wurden
      IF (SELECT COUNT(DATUM) FROM SENDUNGSIDCOUNT WHERE DATUM = current date) = 0
            THEN
            UPDATE SENDUNGSIDCOUNT
            SET DATUM = NULL, SENDUNGSIDCOUNT = 0;
      END IF;
       -- Festlegen der Kassennummer anhand des mitgegebenen Mandanten
      CASE @MANDANT
            WHEN '1'
            THEN SET V_MANDANT = 'G';
            WHEN '3'
            THEN SET V_MANDANT = 'C';
      END CASE;
      
      -- Zusammenfügen der SendungsId und der Auftrags- bzw. JobId (Counter wird in V_SQLMERGE eingesetzt) 
      SET V_SENDUNGSID = V_MANDANT || V_JAHR || V_TAG || V_JOBMED;
      
        -- SendungsIds werden in Fachtabelle eingetragen       
      SET V_SQLMERGE = 
      'MERGE INTO ' ||
       @FACHTABELLE || ' AS A 
        USING (
         SELECT 
          ID, row_number() over(ORDER BY ID) AS ROWCOUNT
           FROM ' ||
            @FACHTABELLE ||
             ' WHERE KEIN_VERSAND IS NULL AND SENDUNGSID IS NULL) AS B 
              ON 
               (A.ID = B.ID) 
                WHEN MATCHED 
                 THEN 
                  UPDATE SET A.SENDUNGSID = ' || V_SENDUNGSID || '|| RIGHT(''000000'' || ROWCOUNT, 6)';
      EXECUTE IMMEDIATE V_SQLMERGE;
      GET DIAGNOSTICS V_ROWS_UPDATED = ROW_COUNT;
    The error always happens where I update A.SENDUNGSID. I do know that this is some kind of conversion problem as it works fine as long as I insert a number instead of a char, but I don't know how to solve this.

    I'd really appretiate if if somebody could help me out here.

    P.S. "G12198800" is the String without the rowcount

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What are the datatype of column(s) SENDUNGSID in table(s) @FACHTABELLE(actual value(s)/name(s) which you passed to the procedure)?


    By the way,
    you passed table name as a parameter, but column name was fixed.
    So, tables to be passed must be limited. How many tables which will be passed to the procedure are there?

  3. #3
    Join Date
    Nov 2011
    Posts
    334
    Is the value of V_SENDUNGSID a column name ?
    if not , Maybe you need try this :
    UPDATE SET A.SENDUNGSID = ''' || V_SENDUNGSID || '''|| RIGHT(''000000'' || ROWCOUNT, 6)

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    fengsun2,
    you must be right!

    I want to see response of db2do.

  5. #5
    Join Date
    Jul 2012
    Posts
    7
    Quote Originally Posted by fengsun2 View Post
    Is the value of V_SENDUNGSID a column name ?
    if not , Maybe you need try this :
    UPDATE SET A.SENDUNGSID = ''' || V_SENDUNGSID || '''|| RIGHT(''000000'' || ROWCOUNT, 6)
    No, V_SENDUNGSID has the value "G12198800" and it runs into an error if I try to add the Rowcount to it. Probably, because there is some conversion problem .. for whatever reason. As I've mentioned before it works fine if the "G" in V_SENDUNGSID is a number.

    Also the column has the datatype varchar. That was basically the first thing I checked.

    The update statement has too many 's in it ^^

    [EDIT] Oh .. forgot the ' at the end of the RIGHT-function. Still get the same error-message, though. :/

    [EDIT2] Now the cast works .. what the hell?! Man .. I'll never get this. Finished and working procedure:

    Code:
    CREATE OR REPLACE PROCEDURE "ADDON21C"."STP_SENDUNGSID_SET_TEMP"
    (
            IN @FACHTABELLE Varchar(100), -- Fachtabelle bzw. Fachview
            IN @MANDANT SMALLINT
    )
    LANGUAGE SQL
    SPECIFIC SQL120511110539422
    BEGIN
      DECLARE V_ROWS_UPDATED INTEGER;
      DECLARE V_SQLUPDATE VARCHAR(1000); -- Variable für Updatestatement des SendungsIdcounts (Dynamic SQL) 
      DECLARE V_SQLMERGE VARCHAR(1000); -- Variable für Merge Into Statement für das Erstellen der SendungsIds (Dynamic SQL)
      DECLARE V_JAHR VARCHAR(2);
      DECLARE V_TAG VARCHAR(5) ;
      DECLARE V_MANDANT VARCHAR(1);
      DECLARE V_SENDUNGSID VARCHAR(15);
      DECLARE V_DATE DATE; 
      DECLARE V_JOBMED VARCHAR(3);
      
      SET V_DATE = CURRENT DATE;  
      SET V_JAHR = RIGHT(CAST(YEAR(V_DATE) AS VARCHAR(4)), 2); -- lediglich die ersten zwei Zeichen des Jahres
      SET V_TAG = '00' || CAST(DAYOFYEAR(CURRENT DATE) AS VARCHAR(5)); -- Tag mit vorangehender 0
      SET V_TAG = RIGHT(V_TAG, 3);
      SET V_JOBMED = '800';
      
      -- Prüfen, ob heute schon SendungsIds gesetzt wurden
      IF (SELECT COUNT(DATUM) FROM SENDUNGSIDCOUNT WHERE DATUM = current date) = 0
            THEN
            UPDATE SENDUNGSIDCOUNT
            SET DATUM = NULL, SENDUNGSIDCOUNT = 0;
      END IF;
       -- Festlegen der Kassennummer anhand des mitgegebenen Mandanten
      CASE @MANDANT
            WHEN '1'
            THEN SET V_MANDANT = 'G';
            WHEN '3'
            THEN SET V_MANDANT = 'C';
      END CASE;
      
      -- Zusammenfügen der SendungsId und der Auftrags- bzw. JobId (Counter wird in V_SQLMERGE eingesetzt) 
      SET V_SENDUNGSID = V_MANDANT || V_JAHR || V_TAG || V_JOBMED;
      
        -- SendungsIds werden in Fachtabelle eingetragen       
      SET V_SQLMERGE = 
      'MERGE INTO ' ||
       @FACHTABELLE || ' AS A 
        USING (
         SELECT 
          ID, row_number() over(ORDER BY ID) AS ROWCOUNT
           FROM ' ||
            @FACHTABELLE ||
             ' WHERE KEIN_VERSAND IS NULL AND SENDUNGSID IS NULL) AS B 
              ON 
               (A.ID = B.ID) 
                WHEN MATCHED 
                 THEN 
                  UPDATE SET A.SENDUNGSID = ''' || V_SENDUNGSID || '''|| RIGHT(''000000'' || CAST(ROWCOUNT AS VARCHAR(15)), 6)';
      EXECUTE IMMEDIATE V_SQLMERGE;
      GET DIAGNOSTICS V_ROWS_UPDATED = ROW_COUNT;
      
        
        -- Counter in Tabelle Sendungsidcount wird geupdatet, wenn über das Merge Into Statement neue SendungsIds angelegt wurden
      IF V_ROWS_UPDATED != 0
            THEN
            SET V_SQLUPDATE =
            'UPDATE SENDUNGSIDCOUNT
            SET SENDUNGSIDCOUNT = SENDUNGSIDCOUNT + (SELECT COUNT(*) FROM ' || @FACHTABELLE || ' WHERE SendungsId LIKE ' || V_SENDUNGSID || ' || ''%''), DATUM = CURRENT DATE';
            EXECUTE IMMEDIATE V_SQLUPDATE;
      END IF;
    END
    Thanks for the help guys. Still don't know why it works now, though ^^

    [EDIT3] Ok .. somehow it doesn't work anymore. I might be going crazy.
    Last edited by db2do; 07-16-12 at 06:40.

  6. #6
    Join Date
    Jul 2012
    Posts
    7
    Ok, this is really weird. The procedure throws the same error, but updates at least one row with the right string. I don't get it ..
    Last edited by db2do; 07-16-12 at 06:54.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •