DB2 Aggregate Sum Max Min Over Partition By

 select * from (
  select organisation_code
   ,ref_correlation_id
   ,batch_received_batch_id
   ,event_rr_id
   ,EVENT_RR_LOCAL_REF_ID
   ,EVENT_OPERATION_ID
   ,EVENT_RR_TRANS_TS
   ,max(EVENT_RR_TRANS_TS)
    over (partition by organisation_code, EVENT_RR_LOCAL_REF_ID) as maxTS
  from NPF.npf_event_referral_received
   where organisation_code in ('G00037-F', 'G00035-B')
    and EVENT_OPERATION_ID <> 3
  ) RR
 where EVENT_RR_TRANS_TS = maxTS

DB2 SQL Extract hierarchy from linked list like table

CREATE TABLE EMPL
 (
 NAME VARCHAR(15),
 EMPID INT NOT NULL PRIMARY KEY,
 MGRID INT
 );
INSERT INTO EMPL VALUES
 ('Sal',       1, NULL),
 ('Berni',     2, 1),
 ('Serge',     3, 2),
 ('Kathy',     4, 2),
 ('David',     5, 1),
 ('Katherine', 6, 5),
 ('Miro',      7, 6),
 ('John',      8, 6),
 ('Rick',      9, 6),
 ('Eric',     10, 7),
 ('Steve',    11, 7),
 ('Haiwei',   12, 7),
 ('Yuri',     13, 8),
 ('Kaarel',   14, 8);
WITH n(empid, name, mgrid) AS
 (SELECT empid, name, mgrid
 FROM EMPL
 WHERE name = 'Sal'
 UNION ALL
 SELECT nplus1.empid, nplus1.name, nplus1.mgrid
 FROM EMPL as nplus1, n
 WHERE n.empid = nplus1.mgrid
 )
 SELECT empid, name, mgrid FROM n;
-- With level
 WITH n(empid, name, mgrid, lvl) AS
 (SELECT empid, name, mgrid, 1 as lvl
 FROM EMPL
 WHERE name = 'Sal'
 UNION ALL
 SELECT nplus1.empid, nplus1.name, nplus1.mgrid, n.lvl+1 as lvl
 FROM EMPL as nplus1, n
 WHERE n.empid = nplus1.mgrid
 )
 SELECT empid, name, mgrid, lvl FROM n;

Results:
EMPID|NAME|MGRID|LVL
1|Sal|(null)|1
2|Berni|1|2
5|David|1|2
3|Serge|2|3
4|Kathy|2|3
6|Katherine|5|3
7|Miro|6|4
8|John|6|4
9|Rick|6|4
10|Eric|7|5
11|Steve|7|5
12|Haiwei|7|5
13|Yuri|8|5
14|Kaarel|8|5
Posted in DB2

DB2 UTC to NZDT NZST Daylight Saving

CREATE OR REPLACE FUNCTION TEST.CONVERT_UTC_TO_NZ (INTS TIMESTAMP)

RETURNS TIMESTAMP

LANGUAGE SQL

DETERMINISTIC

BEGIN

  declare vHoursToAdd INT;

  if INTS IS NULL then 

  set vHoursToAdd = NULL;

  else

  if INTS < timestamp('2014-09-27 14:00:00')

  OR (INTS >= timestamp('2015-04-04 14:00:00') and INTS < timestamp('2015-09-26 14:00:00')

  )

  OR (INTS >= timestamp('2016-04-02 14:00:00') and INTS < timestamp('2016-09-24 14:00:00')

  )

  OR (INTS >= timestamp('2017-04-01 14:00:00') and INTS < timestamp('2017-09-23 14:00:00')

  )

  OR (INTS >= timestamp('2018-03-31 14:00:00') and INTS < timestamp('2018-09-29 14:00:00')

  )

  OR (INTS >= timestamp('2019-04-06 14:00:00') and INTS < timestamp('2019-09-28 14:00:00')

  )

  OR (INTS >= timestamp('2020-04-04 14:00:00') and INTS < timestamp('2020-09-26 14:00:00')

  )

  then set vHoursToAdd = 12;

  else set vHoursToAdd = 13;

  end if;

  end if;

RETURN

  INTS + vHoursToAdd HOURS;

END@

DB2 Correlated Sub Query

 

create or replace view TEST.V_P1RP_EV_TO_RELATEDRR
as
-- We need to find the last P1 RR ADD UPD operation immediately preceeding
-- each other ADD UPD event type
-- this is for when a NON RR event column is populated from the 'associated' RR record
select P1RPEV.ORGANISATION_CODE as P1RPRRR_ORGID
 ,P1RPEV.BATCH_RECEIVED_BATCH_ID as P1RPRRR_BID
 ,P1RPEV.REF_CORRELATION_ID as P1RPRRR_EV_CORRID
-- ,P1RPEV.REF_LOCAL_REF_IDENTIFIER as P1RPRRR_LRID
-- ,P1RPEV.REF_CURRENT_EVENT_ID as P1RPRRR_EVID
 ,P1RPRR.REF_CORRELATION_ID as P1RPRRR_RR_CORRID
from NPFP1.NPF_REFERRAL_PROCESS P1RPEV
join NPFP1.NPF_REFERRAL_PROCESS P1RPRR
 on P1RPRR.ORGANISATION_CODE = P1RPEV.ORGANISATION_CODE
 and P1RPRR.REF_LOCAL_REF_IDENTIFIER = P1RPEV.REF_LOCAL_REF_IDENTIFIER
 and P1RPRR.EVENT_DOMAIN_ID = 1 and P1RPRR.EVENT_OPERATION_ID < 3
where 1 = 1
-- and P1RPEV.REF_CORRELATION_ID = 'NPF-256BPUPDCA0049'
and P1RPRR.REF_TRANS_TS =
 (
 select MAX(REF_TRANS_TS) as MAXTS
 from NPFP1.NPF_REFERRAL_PROCESS RP
 where RP.ORGANISATION_CODE = P1RPEV.ORGANISATION_CODE
  and RP.REF_LOCAL_REF_IDENTIFIER = P1RPEV.REF_LOCAL_REF_IDENTIFIER -- **Correlated**
  and RP.EVENT_DOMAIN_ID = 1 and RP.EVENT_OPERATION_ID < 3
  and RP.REF_TRANS_TS <= P1RPEV.REF_TRANS_TS -- **Correlated**
 )@

 

DB2 Relative Row-Number

20150828

select ORGANISATION_CODE
 ,REF_CORRELATION_ID
 ,BATCH_RECEIVED_BATCH_ID
 ,EVENT_RR_LOCAL_REF_ID
 ,EVENT_OPERATION_ID
 
 ,EVENT_RR_DELETE_FLAG
 ,EVENT_RR_TRANS_TS
 
 ,row_number()
  over(partition by ORGANISATION_CODE, EVENT_RR_LOCAL_REF_ID
   order by EVENT_RR_TRANS_TS) as OPSEQ

from NPF.NPF_EVENT_REFERRAL_RECEIVED

 

Posted in DB2

DB2 Aggregate SubQuery across unions performance

This is very slow:

    select F_KEY, count(*) as P1FM_F_KEY_C
    from (
     select FILE_KEY as F_KEY from NPFP1.NPF_FILE_MIGRATION
     where FILE_KEY IS NOT NULL
     union all
     select FILE_ERROR_KEY as F_KEY from NPFP1.NPF_FILE_MIGRATION
     WHERE FILE_ERROR_KEY IS NOT NULL
     ) FK
    GROUP By F_KEY

This is much faster

select F_KEY, sum(P1FM_F_KEY_C) as P1FM_F_KEY_C from (
  select FILE_KEY as F_KEY, count(*) as P1FM_F_KEY_C
  from NPFP1.NPF_FILE_MIGRATION
  group by FILE_KEY
  union all
  select FILE_ERROR_KEY as F_KEY, count(*) as P1FM_F_KEY_C
  from NPFP1.NPF_FILE_MIGRATION
  group by FILE_ERROR_KEY
 ) T
group by F_KEY
Posted in DB2