Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Answered: How to invoke a UDF that returns a Row

    DB2 V10.1 on Linux

    How do I use a UDF that returns a row?

    I am trying to loop through a result set and set a group of variables from this UDF, and I keep getting this error:

    Code:
    PRODUCTION.UDF_EFORM_VALIDATE_EXPRESSION: 39: The function "PRODUCTION.UDF_EFORM_VALIDATE_ATOM" resolved to specific function "UDF_EFORM_VALIDATE_ATOMIC_EXPRESSIO" that is not valid in the context where it is used.. SQLCODE=-390, SQLSTATE=42887, DRIVER=4.13.111
    The function "PRODUCTION.UDF_EFORM_VALIDATE_ATOM" resolved to specific function "UDF_EFORM_VALIDATE_ATOMIC_EXPRESSIO" that is not valid in the context where it is used.. SQLCODE=-390, SQLSTATE=42887, DRIVER=4.13.111
    Things I have tried:
    Code:
        open expr_cursor;       
        set n_More_Rows = 1;
        FETCH expr_cursor into n_Ordinal;
        WHILE (n_More_Rows = 1) do
           set (n_Valid,n_TABLE_COLUMN_NAME,n_Answer) = 
               (production.udf_eform_validate_atomic_expression(p_Do_Validation,p_XML_Data,p_EXPRESSION_CODE,n_Ordinal));
              
           insert into session.expr values (n_Valid,n_TABLE_COLUMN_NAME,n_Answer);
           
           set n_More_Rows = 1;
           FETCH expr_cursor into n_Ordinal;
        END WHILE;
    Code:
        open expr_cursor;       
        set n_More_Rows = 1;
        FETCH expr_cursor into n_Ordinal;
        WHILE (n_More_Rows = 1) do
           set (n_Valid,n_TABLE_COLUMN_NAME,n_Answer) = 
               values (production.udf_eform_validate_atomic_expression(p_Do_Validation,p_XML_Data,p_EXPRESSION_CODE,n_Ordinal));
              
           insert into session.expr values (n_Valid,n_TABLE_COLUMN_NAME,n_Answer);
           
           set n_More_Rows = 1;
           FETCH expr_cursor into n_Ordinal;
        END WHILE;
    The syntax is OK, and this is driving me nuts.

    Andy

  2. Best Answer
    Posted by mark.b

    "If it's really the row function (declared as 'create function ... returns row (...)'), then your are not allowed to use it outside the 'create transform' statement which defines the transformation functions between the row type and the base types.
    Routines: Row functions"


  3. #2
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    Try this...

    set (n_Valid,n_TABLE_COLUMN_NAME,n_Answer) =
    ( select * from table (production.udf_eform_validate_atomic_expression(p _Do_Validation,p_XML_Data,p_EXPRESSION_CODE,n_Ordi nal)) a ) ;

  4. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    If it's really the row function (declared as 'create function ... returns row (...)'), then your are not allowed to use it outside the 'create transform' statement which defines the transformation functions between the row type and the base types.
    Routines: Row functions
    Regards,
    Mark.

  5. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Thanks, I changed it to return a TABLE and it is working better now.

    Andy

Posting Permissions

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