Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I
Helper I

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



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
Message=HISMPCB0014 In BasePrimitiveConverter an invalid packed decimal value was encountered. SQLSTATE=HY000 SQLCODE=-343


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 🙂


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,
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
-- 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



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



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

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors