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
