-
Matt Haas
- PowerBuilder
- Friday, 6 November 2020 11:11 AM UTC
Hi.
I have a datawindow which worked fine in PB12.6, but is missing data after retrieve in PB2019r2.
retrieve: dw_data.retrieve(lnDstid, lnJmtFrom, lnJmtTarget, lnTimeTarget, lnTimePrefix) => result = some data is missing
if i get the sql-select like dw_data.Object.DataWindow.Table.Select and replace all retrieve parameter with their corresponding value => it work as it should.
It seems the dw has a problem to set the values for the retrieve parameter. I checked the values just before the retrieve line and they are ok.
i am using PB2019r2 build 2353, win10, mysql8, mysql8odbc.
here is the sql select for the dw:
retrieve="SELECT tm.id, tm.dstid1, tm.prsid1, CONCAT(pers.fnam, IFNULL(CONCAT(' ', pers.vnam), ''), ' (', IFNULL(pers.dnno, ''), ')') as cName, dpbr.id, dpbr.kbez as cBereich, IFNULL(dpbr.nType, -1) as dpbr_nType, pers.dnno as nPersNo,
v_att.nYMD as jhrmnttag, v_att.tsEvent as tsEvent, IFNULL(v_att.nType, -1) as v_att_nType,
v_dtl.jhrmnttag, v_dtl.dattm, v_dtl.btm, v_dtl.etm, v_dtl.dtlstd
FROM dptl_tdpmn as tm
INNER JOIN dptg_personal as pers ON tm.prsid1 = pers.id
INNER JOIN dptl_persmn ON tm.dstid1 = dptl_persmn.dstid1 and tm.prsid1 = dptl_persmn.prsid1
LEFT OUTER JOIN
(SELECT t_tdpdtl.dstid1, t_tdpdtl.prsid1, t_tdpdtl.jhrmnttag, TIMESTAMP(t_tdpdtl.jhrmnttag, CONCAT(TRUNCATE(t_tdpdtl.btm/60, 0), ':', MOD(t_tdpdtl.btm,60), ':00')) as dattm, t_tdpdtl.btm, t_tdpdtl.etm, t_tdpdtl.dtlstd, t_tdpdtl.dpbrid1
FROM dptl_tdpdtl as t_tdpdtl
WHERE t_tdpdtl.dstid1 = :anDstid and
t_tdpdtl.jhrmnttag = :anJmtTarget and
t_tdpdtl.btm <= :anTimeTarget and
t_tdpdtl.etm >= :anTimeTarget
) as v_dtl ON tm.dstid1 = v_dtl.dstid1 and tm.prsid1 = v_dtl.prsid1 and tm.jhrmnttag = v_dtl.jhrmnttag
LEFT OUTER JOIN
dptl_dpbr as dpbr ON v_dtl.dpbrid1 = dpbr.id
LEFT OUTER JOIN
(SELECT t_attend.nDstid1, t_attend.nPrsid1, t_attend.nPersNo, t_attend.nYMD, :anJmtTarget as jmttarget, t_attend.tsEvent, t_attend.nType
FROM dptl_attendance as t_attend
WHERE (t_attend.nDstid1 = :anDstid) and
(t_attend.nPrsid1 > 0) and
(t_attend.nYMD between :anJmtFrom and :anJmtTarget) and
(t_attend.tsEvent = (select max(a.tsEvent) as dattm FROM dptl_attendance as a
WHERE a.nDstid1 = t_attend.nDstid1 and a.nPrsid1 = t_attend.nPrsid1
and a.nYMD between :anJmtFrom and :anJmtTarget
and a.tsEvent <= TIMESTAMP(:anJmtTarget, CONCAT(TRUNCATE(:anTimeTarget/60, 0), ':', MOD(:anTimeTarget,60), ':00'))
))
) as v_att ON tm.dstid1 = v_att.nDstid1 and tm.prsid1 = v_att.nPrsid1 and tm.jhrmnttag = v_att.jmttarget
WHERE tm.dstid1 = :anDstid and
tm.jhrmnttag = :anJmtTarget
" arguments=(("anDstid", decimal),("anJmtFrom", number),("anJmtTarget", number),("anTimeTarget", number),("anTimePrefix", number)) sort="tsevent A personal_npersno A " )
Does anybody know if this is a bug or is there something i overlooked?
Regards
Matt
Find Questions by Tag
Helpful?
If a reply or comment is helpful for you, please don’t hesitate to click the Helpful button. This action is further confirmation of their invaluable contribution to the Appeon Community.