Topic
  • 5 replies
  • Latest Post - ‏2014-03-20T19:28:53Z by CRPence@vnet.ibm.com
Arthur_Adams
Arthur_Adams
10 Posts

Pinned topic User Defined Functions with Multiple Parameters

‏2014-03-19T14:54:05Z |

Can anyone show me an example of using a user defined function in SQL where the function has multiple input parameters and makes use of a program written in RPGLE?

I have created a number of SQL functions that use RPGLE programs, but the only functions that work are those with a single input parameter. The syntax I am using (and I've tried many different combinations) must be wrong, but I can't find any examples of using that type of function.

In case it helps, the multiple parameter function was defined with this piece of SQL:

Create Function                                              
  AULWJLP2/WITHDRAWN                                         
    (cono char(2), pnum char(15), inDate date, strc char(2)) 
  Returns  decimal(1,0)                                      
  Language RPGLE                                             
  Called On Null Input                                       
  Specific WITHDRAWN                                         
  External Name 'AULWJLP2/SWAURORA(WITHDRAWN)'               
  Parameter Style General                                    
  Program Type Sub;

and the working single parameter function used this:

Create Function                                  
  AULWJLP2/CYMDTODATE                            
    (inDate decimal(7,0))                        
  Returns  Date                                  
  Language RPGLE                                 
  Deterministic                                  
  No SQL                                         
  Called On Null Input                           
  Specific CYMDTODATE                            
  External Name 'AULWJLP2/SWUTILITY(CYMDTODATE)' 
  Parameter Style General                        
  Program Type Sub;                              
                                                             
  • charleswilt
    charleswilt
    23 Posts

    Re: User Defined Functions with Multiple Parameters

    ‏2014-03-19T15:06:33Z  

    Looks ok to me...

    What does "not work mean"? That  the create fails, if so with what error message?

    Here's one I use all the time:

    Create Function iDATE(Decimal(8,0), VarChar(10))  
      Returns Date                                    
      Language RPGLE                                  
      External Name 'IDATE/XVIDAT(IDAT_PACKED)'       
      Deterministic                                   
      No SQL                                          
      Parameter Style SQL                             
      Allow Parallel                                  
      No External Action;                             

    (from Alan Campin's iDate http://www.think400.dk/downloads.htm)

     

  • krmilligan
    krmilligan
    450 Posts

    Re: User Defined Functions with Multiple Parameters

    ‏2014-03-19T17:54:10Z  

    My guess is that you're function isn't working because of the CHAR parameter.  A constant string is treated by SQL as a VARCHAR value and the SQL standard function rules don't allow a VARCHAR value to be passed to a CHAR parameter.

    Try invoking as:

    WITHDRAWN(CHAR('AA'),CHAR('MYPNUMVALUE'),CURRENT DATE,CHAR('TT'))                                

     
  • Arthur_Adams
    Arthur_Adams
    10 Posts

    Re: User Defined Functions with Multiple Parameters

    ‏2014-03-20T11:06:55Z  

    Looks ok to me...

    What does "not work mean"? That  the create fails, if so with what error message?

    Here's one I use all the time:

    Create Function iDATE(Decimal(8,0), VarChar(10))  
      Returns Date                                    
      Language RPGLE                                  
      External Name 'IDATE/XVIDAT(IDAT_PACKED)'       
      Deterministic                                   
      No SQL                                          
      Parameter Style SQL                             
      Allow Parallel                                  
      No External Action;                             

    (from Alan Campin's iDate http://www.think400.dk/downloads.htm)

     

    Sorry, I should have been more specific.

    My problem is that I can't even get the SQL to run because I get SQL syntax errors which are related to the function call.

    For example, the following SQL will work

    Select aulwjlp2.cymdtodate(a.esdt35) from ault2f2.inp35 as a;

    but this piece of SQL

    Select * from ault2f2.inp35 as a where not aulwjlp2.withdrawn(a.cono35, a.pnum35, '2014-03-19', a.strc35);

    gets the syntax error

    Token <END-OF-STATEMENT> was not valid. Valid tokens: < > = <> <= !< !> != >= ¬< ¬> ¬= IN.

    However, I've now done a little more experimenting and discovered that if I modify the statement to:

    Select * from ault2f2.inp35 as a where aulwjlp2.withdrawn(a.cono35, a.pnum35, '2014-03-19', a.strc35) = 0;

    then it runs.

    The problem is that the program WITHDRAWN returns a logical value, but I couldn't figure out how to specify that so I coded it as

    Returns  decimal(1,0) 

    which means I can't use 'not' and have to use '= 0' instead.

  • charleswilt
    charleswilt
    23 Posts

    Re: User Defined Functions with Multiple Parameters

    ‏2014-03-20T14:04:40Z  

    Sorry, I should have been more specific.

    My problem is that I can't even get the SQL to run because I get SQL syntax errors which are related to the function call.

    For example, the following SQL will work

    Select aulwjlp2.cymdtodate(a.esdt35) from ault2f2.inp35 as a;

    but this piece of SQL

    Select * from ault2f2.inp35 as a where not aulwjlp2.withdrawn(a.cono35, a.pnum35, '2014-03-19', a.strc35);

    gets the syntax error

    Token <END-OF-STATEMENT> was not valid. Valid tokens: < > = <> <= !< !> != >= ¬< ¬> ¬= IN.

    However, I've now done a little more experimenting and discovered that if I modify the statement to:

    Select * from ault2f2.inp35 as a where aulwjlp2.withdrawn(a.cono35, a.pnum35, '2014-03-19', a.strc35) = 0;

    then it runs.

    The problem is that the program WITHDRAWN returns a logical value, but I couldn't figure out how to specify that so I coded it as

    Returns  decimal(1,0) 

    which means I can't use 'not' and have to use '= 0' instead.

    Glad to see you figured it out.

    I must admit that I too have wished DB2 for i supported an actual Boolean type.

    AFAIK, IBM still recommends CHAR(1) FOR BIT DATA for  boolean.

    This would allow you to pass back an RPG indicator variable.

    But you'd still need the MYUDF() = '0'

    Charles

    Updated on 2014-03-20T14:17:01Z at 2014-03-20T14:17:01Z by charleswilt
  • Arthur_Adams
    Arthur_Adams
    10 Posts

    Re: User Defined Functions with Multiple Parameters

    ‏2014-03-20T14:32:31Z  

    Glad to see you figured it out.

    I must admit that I too have wished DB2 for i supported an actual Boolean type.

    AFAIK, IBM still recommends CHAR(1) FOR BIT DATA for  boolean.

    This would allow you to pass back an RPG indicator variable.

    But you'd still need the MYUDF() = '0'

    Charles

    Yes, it is annoying especially when I can use the same function call in RPGLE and code it as though I am testing an indicator.

    Ho Hum, that's computers for you.

  • CRPence@vnet.ibm.com
    40 Posts

    Re: User Defined Functions with Multiple Parameters

    ‏2014-03-20T19:28:53Z  

    Sorry, I should have been more specific.

    My problem is that I can't even get the SQL to run because I get SQL syntax errors which are related to the function call.

    For example, the following SQL will work

    Select aulwjlp2.cymdtodate(a.esdt35) from ault2f2.inp35 as a;

    but this piece of SQL

    Select * from ault2f2.inp35 as a where not aulwjlp2.withdrawn(a.cono35, a.pnum35, '2014-03-19', a.strc35);

    gets the syntax error

    Token <END-OF-STATEMENT> was not valid. Valid tokens: < > = <> <= !< !> != >= ¬< ¬> ¬= IN.

    However, I've now done a little more experimenting and discovered that if I modify the statement to:

    Select * from ault2f2.inp35 as a where aulwjlp2.withdrawn(a.cono35, a.pnum35, '2014-03-19', a.strc35) = 0;

    then it runs.

    The problem is that the program WITHDRAWN returns a logical value, but I couldn't figure out how to specify that so I coded it as

    Returns  decimal(1,0) 

    which means I can't use 'not' and have to use '= 0' instead.

    The boolean data type for RPG is declared with the type specification of "N" for "an indicator field"; or as a return data type on a prototype specification.  The logical values for the RPG "indicator" data type are represented with a one byte of either x'F0' for false or x'F1' for true.  Those values are not compatible with the SQL data type DEC(1).  The RETURNS clause should be either NUMERIC(1) or CHAR(1) for proper representation.  In older releases, e.g. v5r3, the function invocation with the improper definition of the indicator as a RETURNS data type of DECIMAL(1) would cause the function invocation to fail catastrophically [msg CPF503E rc2].  I can not imagine why nor how the function would operate properly, when defined as shown.