RE: UDF problems in STRSQL

["Mackie, Roger L. (Precision Press)" <RLMackie@xxxxxxxxxxxxx>]

Interesting thought. I don't know that much about CAST and didn't find a
way to use it to specify a date format attribute in the manuals, but I
was only skimming. Tomorrow is another day...

Thanks,
Roger Mackie

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Thursday, May 05, 2005 15:53
To: Midrange Systems Technical Discussion
Subject: Re: UDF problems in STRSQL

Any way to

SELECT * FROM MYFILE WHERE INVYR = 2005 and INVNO = 6557 and
CLCDAYSHPLAT(cast(ODDDAT as ...

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Mackie, Roger L. (Precision Press)" <RLMackie@xxxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
05/05/2005 08:46 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
cc

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