Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Posts
    38

    Post Unanswered: Error using || or CONCAT in DB2 SQL procedure

    Hi,

    I am using following sql statement inside DB2(UDB 8.1 eval copy) SQL procedure :

    SET stmt =' update AsterixModel set currentidtoken = ' || p_currentidtoken || tempVar || 'where ModelID = 1' ;

    On building from DB2 Devlopment center it gives me following error :

    No authorized routine named "||" of type "FUNCTION" having compatible arguments was found. LINE NUMBER=26. SQLSTATE=42884


    Can you please tell me as what's wrong with using || (concat) .

    Secondly can I use FOR UPDATE clause in DB2 SQL statement.
    Raj Shekhar

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    concat (||) works on strings. I would suspect that
    p_currentidtoken and / or tempVar are not strings.

    Try casting them as strings (char) :

    SET stmt =' update AsterixModel set currentidtoken = ' || char(p_currentidtoken) || char(tempVar) || 'where ModelID = 1' ;

    2) yes you should be able to use "FOR UPDATE".

    Andy

  3. #3
    Join Date
    Jun 2003
    Posts
    38

    Thumbs up

    Originally posted by ARWinner
    concat (||) works on strings. I would suspect that
    p_currentidtoken and / or tempVar are not strings.

    Try casting them as strings (char) :

    SET stmt =' update AsterixModel set currentidtoken = ' || char(p_currentidtoken) || char(tempVar) || 'where ModelID = 1' ;

    2) yes you should be able to use "FOR UPDATE".

    Andy
    Thanks Andy. Your suggestion worked. But as in case of Oracle, we don't need do to do casting. So will it not affect my data finally. Actually i am porting Oracle on DB2
    Raj Shekhar

  4. #4
    Join Date
    Jun 2003
    Posts
    38

    Post

    Hi ,

    I am trying to use FOR UPDATE clause in DB2 SQL Procedure as follow:

    SELECT currentidtoken
    INTO P_CURRENTIDTOKEN
    FROM AsterixModel
    WHERE ModelID = P_MODELID FOR UPDATE ;

    But it give me erroe as

    ADMINISTRATOR.SP_GETMAXID: 18: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "FOR UPDATE" was found following "ModelID = P_MODELID". Expected tokens may include: "<space>". LINE NUMBER=18. SQLSTATE=42601


    Kindly suggest what is misisng. It works fine with Oracle.

    Thanks.
    Raj Shekhar

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You cannot use FOR UPDATE with SELECT INTO.

    I would suggest you create a cursor
    open it, fetch the data, then continue on...

    declare cursor1 cursor for
    SELECT currentidtoken
    FROM AsterixModel
    WHERE ModelID = P_MODELID FOR UPDATE

    ...

    open cursor1;
    fetch cursor1 into P_CURRENTIDTOKEN;
    ...

    Andy

    Originally posted by Raj Shekhar
    Hi ,

    I am trying to use FOR UPDATE clause in DB2 SQL Procedure as follow:

    SELECT currentidtoken
    INTO P_CURRENTIDTOKEN
    FROM AsterixModel
    WHERE ModelID = P_MODELID FOR UPDATE ;

    But it give me erroe as

    ADMINISTRATOR.SP_GETMAXID: 18: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "FOR UPDATE" was found following "ModelID = P_MODELID". Expected tokens may include: "<space>". LINE NUMBER=18. SQLSTATE=42601


    Kindly suggest what is misisng. It works fine with Oracle.

    Thanks.

Posting Permissions

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