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

Question

Hi,&nbsp;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:&nbsp;DataSource.Error: Microsoft Db2 Client: HISMPCB0014 In BasePrimitiveConverter an invalid packed decimal value was encountered. SQLSTATE=HY000 SQLCODE=-343Details:DataSourceKind=DB2DataSourcePath=s4471405.global.to:446;S2160E5WMessage=HISMPCB0014 In BasePrimitiveConverter an invalid packed decimal value was encountered. SQLSTATE=HY000 SQLCODE=-343ErrorCode=0&nbsp;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.&nbsp; Changing these didn't helped me out, e.g. Dec(4,3) --&gt; Dec(4,1)&nbsp;In my point of view it seems like the field&nbsp;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?&nbsp;Thx in advance and greetings from Germany&nbsp;Chilli 🙂&nbsp;withBOKP (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&lt;3AND (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 &lt;&gt; 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 cwhere a.bknart=c.bknart AND a.bkcodu = c.bkcodu AND a.bkcpar = c.bkcpar AND a.bkcod1 = c.bkcod1 AND a.bkcod2 = c.bkcod2and a.bkecd &gt; c.bkecdand c.bkcttr = 'A ' and a.ncd &lt;&gt; c.ncd andc.cport&lt;3 andc.CENRGT='1' and a.bkqlgt = c.bkqlgt)from S2160E5W.CIMFIC.EXTBOKLF as awhere a.cport&lt;3AND a.BKECD &gt;= 20200101AND 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 &lt;&gt; 0 then cast(round(x.vcdp/x.bkqsth , 2)round(x.bkqsth,2) &lt;&gt; 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,CASEWHEN 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 whenround(y.bkqsth, 2) &lt;&gt; 0 then cast(round(y.vcdp/y.bkqsth , 2)as dec(3 , 2)) else 0 end as PR_m2_vorherfrom BOKP as xjoin S2160E5W.CIMFIC.extclil1 on x.ncl=clncl and x.bkcste=clcste and x.bkcact=clcact andx.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.clieucodeleft outer join S2160E5W.CIMFIC.TABALCP t1 on substr( t1.largum , 6 , 2) = clcsecand t1.LARGUM &lt;&gt; ' ' and t1.ltabal = 'CSECT'left outer join S2160E5W.CIMFIC.TABALCP t2 on substr( t2.largum , 6 , 2) = x.cvendand t2.LARGUM &lt;&gt; ' ' and t2.ltabal = 'CADM'left outer join S2160E5W.CIMFIC.TABALCP t3 on substr( t3.largum , 6 , 2) = x.bklieuand t3.LARGUM &lt;&gt; ' ' and t3.ltabal = 'CLIEU'left outer join S2160E5W.CIMFIC.extposl1 as p1 on x.bkcste=p1.pocste and x.bkcact=p1.pocactand x.ncd=p1.poncd and x.npor=p1.ponpor and x.bknpod=p1.ponpodleft outer join S2160E5W.CIMFIC.extbokip as y onsubstr(x.ncdp , 1 , 8)=digits(y.bkecd)and substr(x.ncdp , 9 , 6) = y.ncdand 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&lt;3 andy.CENRGT='1' and y.bkqsta &lt;&gt; 0order by x.bkcpar, x.bklieu, x.cfcpos, x.cvend, x.ncl, x.bkecd, x.ncd , x.npor , x.bknpodfetch first 1000 Rows only&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;Chilli&nbsp;,
&nbsp;
Hope this may help:FIX: A data conversion error may occur when a Transaction Integrator method is called after you convert a type library by using the TIConversionTool.exe utility
&nbsp;

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

Forum Discussion

Related content

Decimal Precision

Invalid credentials Snowflake

Invalid Connection Credentials

Materialized Lake View - 'Invalid Date, Invalid Date'

remove decimals