cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chilli
Helper I
Helper I

DB 2 Client doesn't load data fully cause of invalid packed decimal value

Hi,

 

I have a problem uploading data into Power Query (PBI). I just got a query from colleague with full functionality used in Excel via a connection. But uploading into Power Query as a query brings up following failure message:

 

DataSource.Error: Microsoft Db2 Client: HISMPCB0014 In BasePrimitiveConverter an invalid packed decimal value was encountered. SQLSTATE=HY000 SQLCODE=-343
Details:
DataSourceKind=DB2
DataSourcePath=s4471405.global.to:446;S2160E5W
Message=HISMPCB0014 In BasePrimitiveConverter an invalid packed decimal value was encountered. SQLSTATE=HY000 SQLCODE=-343
ErrorCode=0

 

Actually it does load data as a forecast, but not at all. In my case it uploads a few hundred lines till it stops a specific row of data.

Checking these based on the failure I posted and knowing the length of decimal values should not to be over 6 digits as I read in a forum.  Changing these didn't helped me out, e.g. Dec(4,3) --> Dec(4,1)

 

In my point of view it seems like the field bkqsth should be limited in some cases like using round() or another function to reduce length. Poorly I am not quiet into these SQL Codes using with. May you can tell me how to reduce the length of these values generally. I just posted the SQL quote beside. Am I focusing on the right type of failure?

 

Thx in advance and greetings from Germany

 

Chilli 🙂

 

with
BOKP (bkcste, bkcact, clieu, bklieu, cfcpos, cvend, ncl, bkecd, ncd, npor, bknpod,
bkqsth, qstaa, vcdp, pu , bkcpw, bkcodc , bkcodm, bktypd, bklgtc, bkcodu, bkcpar, bkcod1, bkcod2, bkqlgt , ncdp) as
(select a.bkcste, a.bkcact, a.clieu, a.bklieu, case when a.CFCPOS = 2 then 'I'
else 'C' end , a.cvend, a.ncl, a.bkecd , a.ncd, a.npor, a.bknpod, a.bkqsth,
cast(case when a.bktypd = 'CE' then
( select sum(b.bkqsth) from S2160E5W.CIMFIC.extbokip as b where a.ncd=b.ncd and a.npor
=b.npor and b.bkcttr = 'F ' and b.bktypd = 'CT'
and b.cport<3
AND (b.CENRGT='1' OR b.CENRGT='2') and a.bkecd = b.bkecd and a.cenrgt = b.cenrgt)
else a.qstaa end as dec(5 , 0)) as qstaa
, a.vcdp , case when a.bkqsth <> 0 then cast(round(a.vcdp/a.bkqsth , 2)
as dec(3 , 2)) else 0 end as PU,
a.bkcpw , a.BKCODC, a.BKCODM , a.bktypd, a.bklgtc, bkcodu, bkcpar, bkcod1, bkcod2, bkqlgt ,

(select max(digits(c.bkecd) concat c.ncd concat digits(c.npor)
concat digits(c.bknpod) concat digits(c.bknspo))
from S2160E5W.CIMFIC.extbokip as c
where a.bknart=c.bknart AND a.bkcodu = c.bkcodu AND a.bkcpar = c.bkcpar AND a.bkcod1 = c.bkcod1 AND a.bkcod2 = c.bkcod2
and a.bkecd > c.bkecd
and c.bkcttr = 'A ' and a.ncd <> c.ncd and
c.cport<3 and
c.CENRGT='1' and a.bkqlgt = c.bkqlgt)
from S2160E5W.CIMFIC.EXTBOKLF as a
where a.cport<3
AND a.BKECD >= 20200101
AND a.BKCTTR = 'A ' AND (a.CENRGT='1' OR a.CENRGT='2')
)

select x.bklieu,/*t3.clieulabel*/ substr( t3.LCDALC , 17, 20) as Werk
, x.cfcpos as Farbe_Farblos, x.cvend as Sachbearbeiter ,
/*t2.cadmlabel*/ substr( t2.LCDALC , 17, 20)Name_Admin, x.ncl as Kunde , cllnmc ,
clcsec as Gebiet,
/*t1.csectlabel*/ substr( t1.LCDALC , 17, 20) as Name, x.bkecd as Bookingdatum , x.ncd as Auftrag, x.npor as Posten , x.bknpod as U_pos,
round(x.bkqsth, 2) as M2, round( x.bkqsth, 0) as M2_rounded,
x.qstaa as KG, x.vcdp as Betrag,

-- gerundeter Wert Datei
-- x.PU as PR_m2_Datei ,

case when
--x.bkqsth <> 0 then cast(round(x.vcdp/x.bkqsth , 2)
round(x.bkqsth,2) <> 0 then cast(round(x.vcdp/ round(x.bkqsth,0) , 2)
as dec(3, 2)) else 0 end as PR_m2_Bericht ,

p1.pocfam as PRD_od_Lager,
x.bkcpw as Werkstatt ,
x.BKCODC as Farbe,
x.BKCODM as my,
p1.PONART as Zeichnung , x.BKLGTC as KD_LAENGE, x.bkcodu as WV,
CASE
WHEN x.BKCPAR = 'J' THEN 'Jobbing'
ELSE 'Extr'
END AS bkcpar, x.bkcod1, x.bkcod2, x.bkqlgt as PR_LAENGE,
p1.POPMTH as KG_m,
cast (p1.POPMEP*1000 as dec(4 , 0)) as Perimeter,
y.vcdp as Betrag_vorher, round(y.bkqsth,2) as menge_vorher ,
round(y.bkqsth, 0) as menge_vorher_round,
y.bkecd as Bookingdatum_vorher,
case when
round(y.bkqsth, 2) <> 0 then cast(round(y.vcdp/y.bkqsth , 2)
as dec(3 , 2)) else 0 end as PR_m2_vorher


from BOKP as x
join S2160E5W.CIMFIC.extclil1 on x.ncl=clncl and x.bkcste=clcste and x.bkcact=clcact and
x.clieu=cllieu
-- left outer join csecttable t1 on clcsec=t1.csectcode
-- left outer join cadmtable t2 on x.cvend=t2.cadmcode
--left outer join clieutable t3 on x.bklieu=t3.clieucode

left outer join S2160E5W.CIMFIC.TABALCP t1 on substr( t1.largum , 6 , 2) = clcsec
and t1.LARGUM <> ' ' and t1.ltabal = 'CSECT'
left outer join S2160E5W.CIMFIC.TABALCP t2 on substr( t2.largum , 6 , 2) = x.cvend
and t2.LARGUM <> ' ' and t2.ltabal = 'CADM'
left outer join S2160E5W.CIMFIC.TABALCP t3 on substr( t3.largum , 6 , 2) = x.bklieu
and t3.LARGUM <> ' ' and t3.ltabal = 'CLIEU'

left outer join S2160E5W.CIMFIC.extposl1 as p1 on x.bkcste=p1.pocste and x.bkcact=p1.pocact
and x.ncd=p1.poncd and x.npor=p1.ponpor and x.bknpod=p1.ponpod
left outer join S2160E5W.CIMFIC.extbokip as y on
substr(x.ncdp , 1 , 8)=digits(y.bkecd)
and substr(x.ncdp , 9 , 6) = y.ncd
and substr(x.ncdp , 15 , 2) = digits(y.npor)
and substr(x.ncdp , 17 , 2) = digits(y.bknpod)
and substr(x.ncdp , 19 , 3) = digits(y.bknspo)
and y.bkcttr = 'A ' and y.cport<3 and
y.CENRGT='1' and y.bkqsta <> 0
order by x.bkcpar, x.bklieu, x.cfcpos, x.cvend, x.ncl, x.bkecd, x.ncd , x.npor , x.bknpod
fetch first 1000 Rows only

 

 

1 REPLY 1
Eyelyn9
Community Support
Community Support

Hi @Chilli ,

 

Hope this may help:FIX: A data conversion error may occur when a Transaction Integrator method is called after you conv...

 

Eyelyn9_0-1635128554216.png

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors