UDF in a view

Steve Bourgeois

UDF in a view

I am trying to create a view that contains a UDF that has input parameters.

Here is the example view.

CREATE VIEW TXTEST.VLRPSD                         

    (  COL1                                

    ,  COL2

    ,  COL3                               

    ,  COL4                                

    ,  COL5                               

    ,  COL6                               

    ,  COL7                                

    ,  RESULT                              

    )                                             

    AS                                            

  SELECT A.col1                          

        ,A.col2                               

        ,A.col3                          

        ,A.col4                        

        ,A.col5                            

        ,A.col6                               

        ,A.col7                            

        ,A.PROP_LENGTH                            

        ,  TXTEST.GETRESULT(                         

             CAST(  COL1                  AS CHAR(3)), 

             CAST(  COL2               AS CHAR(7)), 

             CAST(  COL3                AS SMALLINT),

             CAST(  COL4               AS CHAR(3)), 

             CAST(  COL5           AS SMALLINT),

             CAST(  COL6            AS SMALLINT),

             CAST(  COL7             AS SMALLINT) )

    FROM TXTEST.DATA_TABLE A                

    ;            

When I run the UDF like the following,

TXTEST.GETRESULT(                         

             CAST(  ‘TXC’                  AS CHAR(3)), 

             CAST(  ‘7100071’               AS CHAR(7)), 

             CAST(  1                AS SMALLINT),

             CAST(  ‘POL’               AS CHAR(3)), 

             CAST( 1           AS SMALLINT),

             CAST(  0            AS SMALLINT),

             CAST(  10005             AS SMALLINT) )

I get a good result.

When I try the following query.

SELECT result              

  FROM TXTEST.View

 WHERE COL1     = 'TXC'    

   AND COL2    = '7100071'

   AND COL3    = 1        

   AND COL4      = 'POL'    

   AND COL5     = 1        

   AND COL6    = 0        

   AND COL7    = 10005

I get nothing. 

0 rows returned.

Any idea where I am going wrong.

 

Steve B.

                                 

Steve Bourgeois

RE: UDF in a view
(in response to Steve Bourgeois)

I should say that we are running Db2 V11 on a z/OS.