RE: UDF problems in STRSQL

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Roger, I see you return DECIMAL(3,0) from the UDF.  Not sure but what
happens if you try:

SELECT * FROM MYFILE WHERE INVYR = 2005 and INVNO = 6557 and
CLCDAYSHPLAT(ODDDAT,ODSDAT) > 0.0

Elvis 

-----Original Message-----
 Subject: UDF problems in STRSQL

Hi all,

I searched the archives and read through the SQL Programming Concepts
manual sections on UDFs, but can't seem to solve this problem. This is
my first attempt at creating a UDF (System is at V5R2, soon to be
upgraded to V5R3). The UDF calls a *SRVPGM function written in RPGLE and
compiled in a named activation group (instead of the *CALLER we usually
use). After much trial and error yesterday, I determined that the
service program function completed successfully (in a STRSRVJOB
debugging session it had the proper value in return and did not give any
error), but STRSQL returned this error:

SELECT * FROM MYFILE WHERE INVYR = 2005 and INVNO = 6557 and
CLCDAYSHPLAT(ODDDAT,ODSDAT) > 0                               
Selection error involving field ODDDAT.
 

 Message ID . . . . . . :   QRY2283       Severity . . . . . . . :   30

 Message type . . . . . :   Information

 

 Message . . . . :   Selection error involving field ODDDAT.

 Cause . . . . . :   The query did not run because of a comparison
operand   
   involving data from field ODDDAT (in record 299307 of file MYFILE in

   PRODLIB if other than *N). The ODDDAT data is not necessarily
incorrect,   
   since the operand could be an expression involving other fields and

   constants as well as ODDDAT, or it could require some sort of
conversion  
   that could not be performed successfully.

The job log has the expected query optimization messages, but no error
messages. Field ODDDAT is defined with the DATFMT(*USA) keyword and has
a valid date value stored in it. I tried various registration options
and locations but have not stumbled across a combination that does not
give the above error. Here is the latest statement I used to register
the UDF (in my *CURLIB, not the library the data is in).
 CREATE FUNCTION CLCDAYSHPLAT(DATE, DATE)       
    RETURNS DEC(3,0)                            
    EXTERNAL NAME 'PGMLIB/FTIMCLC(CLCDAYSHPLAT)'
    LANGUAGE RPGLE                              
    NOT FENCED 
    NO SQL
    DETERMINISTIC                                  

If I can get it working, I want to use this UDF and others in SQLRPGLE
dynamic selects. I appreciate any advice the list can provide as I enter
this brave new world of UDFs.

Thanks,
Roger Mackie