Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT A.EV_ID, A.CreatedDate, A.CompletionDate, A.SortCode,
- A.SenderBIC, A.ReceiverBIC, A.NumberOfHitsPerAlert,
- A.Level1ExecutionUser, A.Level2ExecutionUser, A.Final_Level,
- A.Currency, A.Iban, A.Account_Number, A.T_Transaction, A.Last_Operator,
- A.MessageID, A.Message_Type, A.Nature, A.Decision_Type, A.Branch_Contact,
- A.Other_Bank_Contact, A.CompletionDate_TIMESTAMP,
- A.Amount,
- B.Franchise, B.Sub_Franchise, B.Sub_Sub_Franchise
- FROM
- (SELECT X.*,Y.DIV_ID
- FROM
- ( SELECT CAST(EV_ID as VARCHAR(20)) AS EV_ID,
- CreatedDate, CompletionDate, SortCode,
- SenderBIC, ReceiverBIC, NumberOfHitsPerAlert,
- Nonblocking, Level1ExecutionUser, Level2ExecutionUser, Final_Level,
- Currency, Iban, Account_Number, T_Transaction, Last_Operator,
- MessageID, Message_Type, Nature, Decision_Type, Branch_Contact,
- Other_Bank_Contact, CompletionDate_TIMESTAMP,
- Amount
- FROM BAC_AML.EVENT_STATUS_ALERT_TF WHERE
- CompletionDate >= ADD_MONTHS(current_date - EXTRACT(DAY FROM current_date)+1, -1)
- AND CompletionDate <= (current_date - extract(day from current_date)+1)-1
- AND CompletionDate NE '99991231' (date, format 'yyyymmdd')
- AND Final_Level>0
- AND UPPER(DECISION_TYPE) IN ('PASS', 'PASS_L1', 'FAIL', 'UNQUEUE', 'RECHECKED')
- QUALIFY ROW_NUMBER() OVER (PARTITION BY EV_ID
- ORDER BY CompletionDate_TIMESTAMP DESC) = 1
- ) AS X
- LEFT JOIN
- (SELECT CAST(EV_ID as VARCHAR(20)) AS EV_ID, CAST(DIV_ID as VARCHAR(20)) AS DIV_ID
- FROM BAC_AML.TF_FRANCHISE
- ) AS Y
- ON X.EV_ID=Y.EV_ID
- ) AS A
- LEFT JOIN
- (SELECT CAST(FR_KEY as VARCHAR(20)) AS FR_KEY,
- L1_Empos_Name, L2_Emp_Posn_Name, L3_Emp_Posn_Name,
- L4_Emp_Posn_Name, Franchise, Sub_Franchise, Sub_Sub_Franchise,
- ORG_MODEL_LINK
- FROM BAC_AML.FRANCHISE_LOOKUP
- WHERE UPPER(FRANCHISE) IN ('PBB','CPB','ULSTER','NWM')
- ) AS B
- ON B.FR_KEY=A.DIV_ID;
- );
- disconnect from teradata;
- connect to teradata (user=&tera_usr pass=&tera_pw server=tdprod);
Add Comment
Please, Sign In to add comment