Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2010
    Posts
    7

    Question Unanswered: How to select from a function?

    Hi,
    I have created a table and function on LUW

    Code:
    CREATE TABLE t_row (a INT, b INT);
    
    CREATE OR REPLACE FUNCTION f_row (row_param ANCHOR ROW t_row) RETURNS ANCHOR t_row.a
    BEGIN
      RETURN row_param.a;
    END;

    When I'm trying to select from it I'm getting an exception below
    Code:
    VALUES f_row( row_param=>(100,1));
    How to select from such function?

    ERROR [42601] [IBM][DB2/NT] SQL0104N An unexpected token "," was found following "ROW( ROW_PARAM=>(100". Expected tokens may include: "+".

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    usually it is select function(colname) from table ......
    where function(colname) => value
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Nov 2010
    Posts
    7
    I know, how it usually happens using simple scalar functions but I'm curious how to select from this exact function and this is the question

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It would be better if you indicated what you're really trying to achieve. Your artificial example doesn't work for two reasons. Firstly, the DB2 CLP does not understand row types. Secondly, you can only construct a row variable by assignment. This works:
    Code:
    begin 
     declare v1 anchor row t_row; 
     declare v2 int; 
     set v1=(100,1); 
     set v2 = f_row(v1); 
    end
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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