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.