getting one row result for update

[kast1]

I am trying to be able to update the Pop' column in Table A with the New column value from Table B based on where the SDate for each row in Table A fits in the date history based on
LDate (i.e. the first column with SDate of 2019-01-12 is after LDate row with 2018-10-03 and before 2019-04-03 so the updated value for Pop in Table A would be the New value of D)

Table A

SDate Line Item Pop
2019-01-12 WIX WP10266 B
2019-01-30 WIX WP10266 B
2019-01-18 WIX WP10266 B
2019-01-17 WIX WP10266 B
2019-01-24 WIX WP10266 B
2019-01-02 WIX WP10266 B

Table B

LDate Line Item Oldval Newval
2019-10-03 WIX WP10266 C B
2019-04-03 WIX WP10266 D C
2018-10-03 WIX WP10266 W D
2018-09-06 WIX WP10266 N W

I started down the path of using CTE to get a single value but then realized I can't do an update using CTE,
I specified the line and item for speed while I was testing

with t1 as
(select SDate, line, item, pop
from table A
where backfill = 'Y' and line = 'WIX' and item = 'WP10266'),
t2 as
(select LDate, line, item, oldval, newval
from table B
where field = 'IPOPCD' and line = 'WIX' and item = 'WP10266')

select t1.SDate, t2.LDate, t1.line, t1.item, t2.oldval, t2.newval
from t1
join t2 on t1.line = t2.line and t1.item = t2.item
where t1.SDate >= t2.LDate;

which still leaves me multiple rows for each row in Table A

SDate LDate Line Item oldval newval
2019-01-12 2018-09-06 WIX WP10266 N W
2019-01-12 2018-10-03 WIX WP10266 W D
2019-01-30 2018-09-06 WIX WP10266 N W
2019-01-30 2018-10-03 WIX WP10266 W D
2019-01-18 2018-09-06 WIX WP10266 N W
2019-01-18 2018-10-03 WIX WP10266 W D
2019-01-17 2018-09-06 WIX WP10266 N W
2019-01-17 2018-10-03 WIX WP10266 W D
2019-01-24 2018-09-06 WIX WP10266 N W
2019-01-24 2018-10-03 WIX WP10266 W D

[TedHolt]

I use MERGE for this sort of thing. You can probably select just the rows you need in the USING clause.

https://www.itjungle.com/2013/04/03/fhg040313-story02/

[kast1]

Thanks Ted good suggestion on the merge, my biggest hurdle right now is trying to get the correct date picked from Table B in one statement

[TedHolt]

You could try something like this:

declare global temporary table C as
(select b.Line, b.Item, b.Ldate as FromDate,
coalesce(lead(b.LDate) over(partition by b.Line, b.Item order by b.Line, b.Item) - 1 day, '9999-12-31') as ThruDate,
b.NewVal
from session.B
order by b.Line, b.Item, b.Ldate)
with data;

merge into session.A as tgt
using (select c.Line, c.Item, c.FromDate, c.ThruDate, c.NewVal
from session.C) as src
on tgt.Line = src.Line
and tgt.Item = src.Item
and tgt.SDate between src.FromDate and src.ThruDate
when matched then
update set tgt.Pop = src.NewVal;

The expression with the LEAD function gives you a date range, which you can use to update with BETWEEN.

LINE	ITEM	FROMDATE	THRUDATE	NEWVAL
WIX	WP10266	2018-09-06	2018-10-02	W
WIX	WP10266	2018-10-03	2019-04-02	D
WIX	WP10266	2019-04-03	2019-10-02	C
WIX	WP10266	2019-10-03	9999-12-31	B

I tried putting the SELECT with LEAD in the USING clause, but the UPDATE didn't work properly, so I created a temporary file C instead and used it to update from.

My quick test seemed to work, but don't take this as debugged code.

[TedHolt]

I just saw a problem. I left the date field out of the ORDER BY for LEAD.

merge into session.A as tgt
using (select b.Line, b.Item, b.Ldate as FromDate,
coalesce(lead(b.LDate) over(partition by b.Line, b.Item order by b.Line, b.Item, b.LDate) - 1 day, '9999-12-31') as ThruDate,
b.NewVal
from session.B) as src
on tgt.Line = src.Line
and tgt.Item = src.Item
and tgt.SDate between src.FromDate and src.ThruDate
when matched then
update set tgt.Pop = src.NewVal

I don't guess you need the temporary C table after all.

Again, this is not debugged, just thrown together quickly. I hope it helps.

[kast1]

Thanks Ted, assuming by 'session' here you mean table. I've tried this and reviewed it number of times I keep getting hit with a Keyword OVER not expected. Valid tokens: ) error. Did you get this to run? I have not used lead function before but looks like one param should be fine and the over syntax seems fine as well. I have not addressed this yet but looks like the coalesce could have date calc of null - 1 day so I may need to look at changing that around. See any syntax issues that would cause my error? I am trying this on an ACS script window

[Vectorspace]

What IBMi version are you on? LEAD() was new in an IMBi 7.3 PTF: https://www.itjungle.com/2016/05/31/fhg053116-story03/

[kast1]

thought we were 7.3 but found out today we are still 7.2, looks like I'm headed toward a different solution

[TedHolt]

assuming by 'session' here you mean table.

SESSION means QTEMP. I used DECLARE GLOBAL TEMPORARY TABLE to create the test files. I should have removed SESSION. from the statement when I posted my code.

[kast1]

Came up with this instead, used lateral before but not with cross join, do I need to be concerned about performance here updating approx 7M rows

Merge Into TableA tgt Using
( Select a.line, a.item, a.sdate, b.newVal
from tableA a Cross Join lateral (select B.* FROM TableB B
Where B.Ldate <= A.Sdate and B.Line=A.Line and B.item=A.item
Order by B.Ldate Desc Fetch First 1 Rows Only ) B
) SRC ON on tgt.line = src.line and tgt.item = src.item and tgt.sdate = src.sdate
when matched then update set tgt.Pop = src.newVal
else ignore