Results 1 to 6 of 6

Thread: Multiple assign

  1. #1
    Join Date
    Aug 2003
    Posts
    16

    Unanswered: Multiple assign

    How to assign values to more variables in a single statement?

    I have a function which returns a table (actually a row from a table from the given primary key).
    How to assign the columns returned by the function to more variables within a single statement?

    I have the function: f(id int) returns table(col1, col2...coln).....
    I want to call this function from a trigger:
    ???? = (select t.* from table(f(1)) as t)

    What should I put instead of "????"
    Should I use a structure? How?
    Should I use a row function instead a table function?

    I don't want to have the following code (although this code works):
    declare a_col1...;
    declare a_col2...;
    set a_col1 = (select t.col1 from table(f(1)) as t); ......
    set a_col2 = (select t.col2 from table(f(1)) as t); ......

    Can anyone help?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You do not state version od DB2 or which OS.

    In 8.1 for LUW, you can:

    SET (v1,v2,v3,...) = myfunction(pk)

    HTH

    Andy

  3. #3
    Join Date
    Aug 2003
    Posts
    16
    Sorry, I've forgot to mention:

    Database server = DB2/LINUX 7.2.0

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Check the DB2 7.2 SQL Reference manual to see if you can use SET Variable the same way as in 8.1. (I cannot remember if you can or not).

    Andy

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You can do

    SELECT col1,col2 into v1,v2 from table1
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Aug 2003
    Posts
    16
    select c1,c2 into v1,v2 from.... doesn't work in trigger.

    But: set(v1,v2) = (select c1,c2 from ....) works!.

    I tested with 7.2.0 on Linux (although I didn't find SET(...) in DB2 documentation, which I consider a true nightmare).

    Thank you very much for help!

Posting Permissions

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