Hi All,
I have a procedure that is returning a zero value because of the following error. The same SQL returns the correct value in WinSQL and System I Navigator Run SQL.
Anyone have any ideas on why it would cast into a 31 decimal field with no significant digits? Is there a work around to force it not to do this?
We are on V6R1.
Thanks for any help you can provide.
Message ID . . . . . . : CPF5035 Severity . . . . . . . : 10
Message type . . . . . : Diagnostic
Date sent . . . . . . : 04/18/13 Time sent . . . . . . : 09:05:54
Message . . . . : Data mapping error on member RLEMI.
Cause . . . . . : A data mapping error occurred on field
Cast(Sum(RLEMLABOR_39.RLEMLMEAL+RLEMLABOR_39.RLEMLTRVL) AS Decimal(31,31))
in record number 0, record format *FIRST, member number 1, in member RLEMI
file RLEMI in library ERSDS, because of error code 2. The error code
meanings follow:
1 -- There is data in a decimal field that is not valid.
2 -- A significant digit was truncated.
From DBU table RLEMLABOR
70 Y Meal Payout RLEMLMEAL P 5 2 33
80 Y Travel Payout RLEMLTRVL P 5 2 36
Procedure in service program:
P GetBlueprintService...
P B EXPORT
D GetBlueprintService...
D PI
D peRCID 19 0 Const
D peStartDate D Const
D peEndDate D Const
D peServiceSum 11 2
/Free
Exec SQL
Select
Sum(STS_UNIT_PRICE_2)
Into :peServiceSum
From
(SELECT
Sum(coalesce(RLEMLABOR.rlemlmeal,0) +
coalesce(RLEMLABOR.RLEMLTRVL,0)) STS_UNIT_PRICE_2
FROM RLEMI RLEMI
INNER JOIN RSCAF RSCAF
ON (RLEMI.RLEMIRSID= rscaf.rsid )
INNER JOIN RSCAFLVLLK RSCAFLVLLK
ON ( RSCAFLVLLK.RSLRSID= RSCAF.RSID)
INNER JOIN RLEVEL RLEVEL
ON ( RLEVEL.L1RLIID= RSCAFLVLLK.RSLRLIID)
LEFT OUTER JOIN RLVLDTLI ERSDS_RLVLDTLI3_3
ON ( RLEVEL.L3RLIID=ERSDS_RLVLDTLI3_3.RDIRLIID)
INNER JOIN RLVLDTLS ERSDS_RLVLDTLS3_3
ON (ERSDS_RLVLDTLI3_3.RDIRDSID=ERSDS_RLVLDTLS3_3.RDSID)
INNER JOIN RLEMLABOR RLEMLABOR
ON ( RLEMI.RLEMIID= RLEMLABOR.RLEMLLIID)
LEFT OUTER JOIN (
select mtla.lemlaborid,
mtla.updateDate,
(case when m.RSAPID is null then 0 else 1 end) m,
(case when tla.RSAPID is null then 0 else 1 end) tla
from
(select distinct rsaplmlID lemlaborid,
UPDATEDATETIME updateDate
from RSAPEXP
where RSAPHTYP in ('M', 'T', 'L','A')) mtla
left outer join RSAPEXP m
on (mtla.lemlaborid=m.rsaplmlID
and m.RSAPHTYP='M')
left outer join RSAPEXP tla
on (mtla.lemlaborid=tla.rsaplmlID
and tla.RSAPHTYP in('T','L','A'))
) exp on RLEMLABOR.RLEMLID = exp.lemlaborid
LEFT OUTER JOIN (
select mtla.lemlaborid ,
mtla.RSERSUSER3 uniqueId,
sum(m.RSERSQTY) mealQty,
sum(tla.RSERSQTY) tlaQty,
m.RSERSUNTPR mealrate,
tla.RSERSUNTPR tlarate
from
(select distinct RSERSUNQID as lemlaborid,
RSERSUSER3
from RSHELLERS
where RSERSTYP in ('M', 'T', 'L','A')) mtla
left outer join RSHELLERS m
on (mtla.lemlaborid=m.RSERSUNQID and m.RSERSTYP='M')
left outer join RSHELLERS tla
on (mtla.lemlaborid=tla.RSERSUNQID
and tla.RSERSTYP in ('T','L','A'))
group by mtla.lemlaborid,
mtla.RSERSUSER3,
m.RSERSUNTPR,
tla.RSERSUNTPR ) ERS
on RLEMLABOR.RLEMLID = ERS.lemlaborid
WHERE
( RLEMI.RLEMIDT between :peStartDate and :peEndDate) AND
RLEMI.RLEMIRCID =:peRCID AND
ERSDS_RLVLDTLS3_3.RDSNAME = 'GSAP' AND
ERSDS_RLVLDTLI3_3.RDIVALUE <> '1' AND
(rlemlmeal <> 0 OR RLEMLABOR.RLEMLTRVL <> 0 )
union all
select
Sum(RLEMMPRC * RLEMMQTY *
(1 + Coalesce(RLEMMPREM, 0)/100)) STS_UNIT_PRICE_2
from RLEMI RLEMI
JOIN RLEMMATL RLEMMATL
on RLEMMATL.RLEMMLIID= RLEMI.RLEMIID
JOIN rscaf rscaf
on RLEMI.RLEMIRSID= rscaf.rsid
JOIN RSCAFLVLLK RSCAFLVLLK
ON RSCAFLVLLK.RSLRSID= RSCAF.RSID
JOIN RLEVEL RLEVEL
ON RLEVEL.L1RLIID= RSCAFLVLLK.RSLRLIID
LEFT OUTER JOIN RLVLDTLI ERSDS_RLVLDTLI3_3
ON ( RLEVEL.L3RLIID=ERSDS_RLVLDTLI3_3.RDIRLIID)
INNER JOIN RLVLDTLS ERSDS_RLVLDTLS3_3
ON (ERSDS_RLVLDTLI3_3.RDIRDSID=ERSDS_RLVLDTLS3_3.RDSID)
LEFT JOIN (select a.* from RSHELLSRVR a
join rcontract c1 on a.rssrrcid = c1.rcid
join rsite rsite on c1.RCRSSID= rsite.RSSID
join rcontract c2 on c2.RCRSSID = rsite.rssid
where c1.RCACTIVE='Y' and c2.rcid=:peRCID) abc
on RLEMMATL.rlemmSHTT=abc.rssrgsap
left outer join rsapexp exp
on RLEMMATL.RLEMMID =exp.rsaplmmID and exp.RSAPHTYP='C'
left outer join (
select RSERSUNQID lemmaterialid,
RSERSUSER3 uniqueId,
sum(RSERSQTY) Qty ,
RSERSUNTPR RATE
from rshellers
where RSERSTYP='C'
group by RSERSUNQID , RSERSUSER3 , RSERSUNTPR
) ERS on RLEMMATL.RLEMMID = ERS.lemmaterialid
where RLEMI.RLEMIDT between :peStartDate and :peEndDate
AND RLEMI.RLEMIRCID= :peRCID
AND ERSDS_RLVLDTLS3_3.RDSNAME = 'GSAP'
AND ERSDS_RLVLDTLI3_3.RDIVALUE <> '1') as Total;
Return;
/End-Free
P E
Doug Lewis
Programmer/Analyst
Safway Group Holding LLC
N19 W24200 Riverwood Dr.
Waukesha, WI 53188
Phone: 262-523-6281
Fax: 262-523-9854
www.safway.com
midrange.com
