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