Category: DB2

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), […]

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’) […]

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 […]

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  

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, […]