select current timestamp as dt FROM sysibm.sysdummy1 http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.html
Category: DB2
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
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), […]
What groups is a user in: SELECT * FROM TABLE (SYSPROC.AUTH_LIST_GROUPS_FOR_AUTHID(‘AMY’)) AS T xx
Sample retuned from AQT: 2015-11-20-15.00.00.000000 Paste above into Excel, and then: =DATEVALUE(LEFT(SUBSTITUTE(L3,”-“,”/”),10))+TIMEVALUE(MID(SUBSTITUTE(L3,”.”,”:”),12,8)) with milliseconds: =DATEVALUE(LEFT(SUBSTITUTE(AJ207,”-“,”/”),10))+TIMEVALUE(MID(SUBSTITUTE(AJ207,”.”,”:”),12,8) & RIGHT(AJ207,7))
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’) […]
create or replace view TEST.V_P2_REFO_RNUM as select REFO.* ,row_number() over( partition by SUBMT_ORGANISATION_CODE ,REFERRAL_KEY order by SUBMT_ORGANISATION_CODE ,REFERRAL_KEY , REFERRAL_TRANS_TS) as RNUM from NPF.REFERRAL_OPS REFO@
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 […]
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
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, […]