I never worked with Db2 on i, but this query works on LUW.. give it a try..
select empno, lastname, firstnme, workdept, salary,
last_value(empno) over( partition by workdept order by empno range between unbounded preceding and unbounded following ) last_emp_on_dept,
case last_value(empno) over( partition by workdept order by empno range between unbounded preceding and unbounded following ) when empno then bonus else null end bonus_on_last
from samue.employee order by workdept, empno
EMPNO LASTNAME FIRSTNME WORKDEPT SALARY LAST_EMP_ON_DEPT BONUS_ON_LAST
------ --------------- ------------ -------- ----------- ---------------- -------------
000010 HAAS CHRISTINE A00 152750.00 200120 -
000110 LUCCHESSI VINCENZO A00 66500.00 200120 -
000120 O'CONNELL SEAN A00 49250.00 200120 -
200010 HEMMINGER DIAN A00 46500.00 200120 -
200120 ORLANDO GREG A00 39250.00 200120 0.00
000020 THOMPSON MICHAEL B01 94250.00 000020 800.00
000030 KWAN SALLY C01 98250.00 200140 -
000130 QUINTANA DELORES C01 73800.00 200140 -
000140 NICHOLLS HEATHER C01 68420.00 200140 -
200140 NATZ KIM C01 68420.00 200140 600.00
000060 STERN IRVING D11 72250.00 200220 -
000150 ADAMSON BRUCE D11 55280.00 200220 -
000160 PIANKA ELIZABETH D11 62250.00 200220 -
000170 YOSHIMURA MASATOSHI D11 44680.00 200220 -
000180 SCOUTTEN MARILYN D11 51340.00 200220 -
000190 WALKER JAMES D11 50450.00 200220 -
000200 BROWN DAVID D11 57740.00 200220 -
000210 JONES WILLIAM D11 68270.00 200220 -
000220 LUTZ JENNIFER D11 49840.00 200220 -
200170 YAMAMOTO KIYOSHI D11 64680.00 200220 -
200220 JOHN REBA D11 69840.00 200220 600.00
000070 PULASKI EVA D21 96170.00 200240 -
000230 JEFFERSON JAMES D21 42180.00 200240 -
000240 MARINO SALVATORE D21 48760.00 200240 -
000250 SMITH DANIEL D21 49180.00 200240 -
000260 JOHNSON SYBIL D21 47250.00 200240 -
000270 PEREZ MARIA D21 37380.00 200240 -
200240 MONTEVERDE ROBERT D21 37760.00 200240 600.00
000050 GEYER JOHN E01 80175.00 000050 800.00
000090 HENDERSON EILEEN E11 89750.00 200310 -
000280 SCHNEIDER ETHEL E11 36250.00 200310 -
000290 PARKER JOHN E11 35340.00 200310 -
000300 SMITH PHILIP E11 37750.00 200310 -
000310 SETRIGHT MAUDE E11 35900.00 200310 -
200280 SCHWARTZ EILEEN E11 46250.00 200310 -
200310 SPRINGER MICHELLE E11 35900.00 200310 300.00
000100 SPENSER THEODORE E21 86150.00 200340 -
000320 MEHTA RAMLAL E21 39950.00 200340 -
000330 LEE WING E21 45370.00 200340 -
000340 GOUNOT JASON E21 43840.00 200340 -
200330 WONG HELENA E21 35370.00 200340 -
200340 ALONZO ROY E21 31840.00 200340 500.00
42 record(s) selected.
as I don't have your tables, I've used employee table from sample db. The dept here plays your ORDER number role. and I ordered the rows for each dept by EMPNO.
The LAST_EMP_ON_DEPT column is there just for make it easier to see the query logic. If the emp is the last one for it's dept, I am showing the bonus value.
Just change it for your needs.
Regards
------------------------------
Samuel Pizarro
Db2 DBA
Kyndryl
Brazil / Sumaré - SP
------------------------------
Original Message:
Sent: Fri December 30, 2022 05:06 AM
From: Paolo Salvatore
Subject: Show a value only when key change
Hi,
I have this need the above result set is made with some join between various table, starting from the order, I read some details from other table, but the column commissions is relative to the order so I want to display this value only when the order change.
Could you suggest me any tricks?
this is what I have now:
| 20221228 | 90643 | E | H | 3 | AAA | ORDER1 | DESCRIPTION | desc | C | PRICE1 | commissions |
| 20221229 | 90743 | E | H | 3 | AAA | ORDER1 | DESCRIPTION | desc | C | PRICE2 | commissions |
| 20221229 | 90811 | E | H | 3 | AAA | ORDER1 | DESCRIPTION | desc | C | PRICE3 | commissions |
| 20221228 | 93028 | E | H | 3 | CCC | ORDER2 | DESCRIPTION | desc | C | PRICE1 | commissions |
| 20221229 | 101252 | E | H | 3 | CCC | ORDER2 | DESCRIPTION | desc | C | PRICE2 | commissions |
| 20221229 | 115252 | E | H | 3 | DDD | ORDER3 | DESCRIPTION | desc | C | PRICE3 | commissions |
this is what I want:
| 20221228 | 90643 | E | H | 3 | AAA | ORDER1 | DESCRIPTION | desc | C | PRICE1 | - |
| 20221229 | 90743 | E | H | 3 | AAA | ORDER1 | DESCRIPTION | desc | C | PRICE2 | - |
| 20221229 | 90811 | E | H | 3 | AAA | ORDER1 | DESCRIPTION | desc | C | PRICE3 | commissions |
| 20221228 | 93028 | E | H | 3 | CCC | ORDER2 | DESCRIPTION | desc | C | PRICE1 | - |
| 20221229 | 101252 | E | H | 3 | CCC | ORDER2 | DESCRIPTION | desc | C | PRICE2 | commissions |
| 20221229 | 115252 | E | H | 3 | DDD | ORDER3 | DESCRIPTION | desc | C | PRICE3 | commissions |
Is it possible with some olap_function? I'm on IBMi V7r3.
Many thanks
------------------------------
Paolo Salvatore
------------------------------
#Db2