× 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.



Here we go Rob

D Thrtclwght pi 8
D InOrdnbrdsp 14 CONST

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 Rob Berendt
Sent: Tuesday, April 19, 2016 2:36 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: problem with sql function created from procedure in service program

May we see the PI (Procedure Interface) for
PRODPA/ORDERSP(THRTCLWGHT)


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Alan Shore <ashore@xxxxxxxx>
To: "midrange-l@xxxxxxxxxxxx" <midrange-l@xxxxxxxxxxxx>
Date: 04/19/2016 02:19 PM
Subject: problem with sql function created from procedure in
service program
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

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.