Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2012
    Posts
    2

    Question Unanswered: Update with function call and select as the parameter trouble

    I'm trying to update an attribute in the last row inserted (a number).
    The value of the attribute is returned from a function (the function returns int).
    I would like to select the value that I want to send as the parameter in the function call.
    But I get an error message.

    Here is my code:
    UPDATE CRKA SET IDSTFK = FUNCTIONTEST(SELECT C.CRKA FROM CRKA C ORDER BY IDCRKA DESC FETCH FIRST 1 ROW ONLY WHERE IDSTFK= SELECT C.IDSTFK FROM CRKA C ORDER BY IDCRKA DESC FETCH FIRST 1 ROW ONLY

    Error:
    DB21034E The command was processed as an SQL statement because it was not a calin Comman Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "C" was found following FUNCTIONTEST(SELECT". Expected tokens may include "<space>". SQLSTATE=42601

    This is for learning purposes only, so sorry for any beginner mistakes I've made.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try to put the SELECT staements into parentheses, like...
    Code:
    UPDATE CRKA
       SET IDSTFK
         = FUNCTIONTEST(
             (SELECT C.CRKA
               FROM  CRKA C
               ORDER BY
                     IDCRKA DESC
               FETCH FIRST 1 ROW ONLY
             )
           )
     WHERE IDSTFK
           = (SELECT C.IDSTFK
               FROM  CRKA C
               ORDER BY
                     IDCRKA DESC
               FETCH FIRST 1 ROW ONLY
             )
    ;

  3. #3
    Join Date
    Aug 2012
    Posts
    2
    Yes I've tried that just before you replied and it works!
    Thank you very much!
    Could I ask you for help with this as well?
    CREATE FUNCTION FUNCTIONTEST(CRKA VARCHAR(25)) RETURNS INT BEGIN ATOMIC DECLARE ST INT; SET ST = 0 (CASE WHEN CRKA = 'A' THEN SET ST = 1; WHEN CRKA = 'B' THEN SET ST = 2; ELSE SET ST = 0; END CASE RETURN ST; END

    SQL0104N An unexpected token "(" was found following "E ST INT; SET ST = 0". Expected tokens may include "LEFT_BRACKET".

    I've tried with and without parentheses.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You should format your code to debug it.
    Without proper formating, it is difficult to konw the structure of the code.
    It is not specific to SQL language. Common to C/C++, Java, (any languages you like)...

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Like this way
    Code:
    CREATE FUNCTION FUNCTIONTEST
    (CRKA VARCHAR(25))
     RETURNS INT
    BEGIN ATOMIC
       DECLARE ST INT;
       SET ST = 0
       (CASE
        WHEN CRKA = 'A' THEN SET ST = 1;
        WHEN CRKA = 'B' THEN SET ST = 2;
        ELSE SET ST = 0;
        END CASE
       RETURN ST;
    END
    There are many syntax errors.
    - unbalanced parenheses.
    - incorrect use of semicolons.
    - so on...

  6. #6
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    You (and maybe your organization) may find code that isallruntogether acceptable, but most places do not.

    If you want help, please be curteous enough to properly indent your code so people can easily read it and not have to decipher it.

Posting Permissions

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