Apologies for not replying before now - a thing called work was interrupting me
Thanks Rob
That was it
I understand about the concatenate (||) - but I was just trying to get the function to work
As far as being on V5r4 - well hopefully that will soon no longer be true (fingers and everything else crossed)
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:48 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: problem with sql function created from procedure in service program
It's a two part fix.
First, it is a character, not a variable length character.
Second, when you concatenate those strings together it is assuming a variable length character so you have to CAST it as character.
GETTHRTCLWGHT(
cast(DIGITS(a.PAMMDD) || DIGITS(a.PAYY) ||
DIGITS(a.PAXXX) || a.PRDNUM || '0' || DIGITS(a.PSPNUM)) as character(14)
) AS
Theoretical_wght
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 Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 04/19/2016 02:39 PM
Subject: RE: problem with sql function created from procedure in
service program
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
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
midrange.com
