RE: problem with sql function created from procedure in service program

Thanks for your reply Glenn
I dropped the function and created it again using CHAR instead of VARCHAR and ran th sql script
I received the following
Routine GETTHRTCLWGHT in *N not found with specified parameters.

I must admit to never having used service entry points


Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Glenn Gundermann
Sent: Tuesday, April 19, 2016 2:33 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: problem with sql function created from procedure in service program

Can you try changing the VARCHAR to simply CHAR?

To debug, use Service Entry Points.


Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Work: (416) 675-9200 ext. 89224
Cell: (416) 317-3144


On 19 April 2016 at 14:19, Alan Shore <ashore@xxxxxxxx> wrote:

Hi everyone
Before I forget - we are on V5r4
I have created an SQL function from a procedure in a service program
using the following

CREATE FUNCTION PRODPA/GETTHRTCLWGHT ( INORDNBR VARCHAR(14) )
RETURNS CHAR(8) LANGUAGE RPGLE SPECIFIC PRODPA/GETTHRTCLWGHT NOT
DETERMINISTIC NO SQL CALLED ON NULL INPUT DISALLOW PARALLEL
EXTERNAL NAME 'PRODPA/ORDERSP(THRTCLWGHT)'
PARAMETER STYLE SQL

Where the procedure accepts a 14 character value and returns an 8
character value The service program definitely works - but when I
attempt to use the sql function I receive Query cannot be run. See
lower level messages.

Looking at the job log - I see
Decimal data error.
User-defined function error on member PKGIDHST.

Where PKGIDHST is the file in the following query

SELECT iDate((a.PDATE - 72),'*MDY') AS Shipped_date,
CAST(DIGITS(a.PAMMDD)||
SUBSTR(DIGITS(DECIMAL(a.PAYY+28,3,0)),2,2)
||'-'||DIGITS(a.PAXXX)||'-'||a.PRDNUM AS CHAR(13) CCSID 37) AS
Order_Nbr, DIGITS(a.PSPNUM) as Disp_Num, a.PPKGID AS Package_Id,
GETTRACKINGNUM(a.PAYY,a.PAMMDD,a.PAXXX,a.PRDNUM, a.PSPNUM) as
Parcel_Tracking_Nbr,
GETTHRTCLWGHT(DIGITS(a.PAMMDD) || DIGITS(a.PAYY) ||
DIGITS(a.PAXXX) || a.PRDNUM || '0' || DIGITS(a.PSPNUM)) AS
Theoretical_wght FROM prodfa/PKGIDHst a WHERE a.PAYY = 87 and a.PAMMDD
= 0319 When I remove the lines
GETTHRTCLWGHT(DIGITS(a.PAMMDD) || DIGITS(a.PAYY) ||
DIGITS(a.PAXXX) || a.PRDNUM || '0' || DIGITS(a.PSPNUM)) AS
Theoretical_wght

I get results - so it HAS to be this function I tried using STRDBG in
this procedure - but it does NOT enter the procedure

Any ideas as to how I can debug this - or even better - if you see
what is wrong - then I would be grateful if you can point it out As
always - I will be eternally grateful for any and all answers

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.