× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.