Untitled
unknown
plain_text
a year ago
4.0 kB
2
Indexable
Never
EXPLAIN INSERT INTO BITEAM_EXP.TRANS_VH ( ID, STATE, CORE_RESULT, BANKCODE, AGENT_, NAME_, PARTNER_RESULT, DESCRIPTION, TYPE_, CREATED, GROUP_, USER_PAYMENT, AMOUNT, TECH_DESC, ERROR_TYPE, ERROR_DESCRIPTION, REFERENCE, ETL_DATE ) WITH PAID_V4_FILTER AS ( SELECT ID, STATE, RESULT, BANKCODE, TYPE_, CREATED, DEBITOR, CREDITOR FROM UMARKETADM.MS_ALL_TRANS_PAID_V4 WHERE TRUE AND STATE IN (2, 6) AND ETL_DATE = '2022-09-22' ORDER BY ID ), TAM1 AS ( SELECT T1.ID, T1.STATE, T1.RESULT, T1.BANKCODE, T3.AGENT_, T3.NAME_, CASE WHEN T2.PARTNER_RESULT IS NULL THEN 'NULL' ELSE T2.PARTNER_RESULT END AS PARTNER_RESULT, T2.PARTNER_DES, T1.TYPE_, T1.CREATED, T3.GROUP_, CASE WHEN T3.CREDIT_DEBIT = '+' THEN T1.DEBITOR WHEN T3.CREDIT_DEBIT = '-' THEN T1.CREDITOR END AS USER_INITIATOR, T4.USER_PAYMENT, T4.SERVICE_CODE, T5.SERVICE_DESCRIPTION, T4.AMOUNT, T6.REFERENCE FROM PAID_V4_FILTER AS T1 LEFT JOIN ( SELECT * FROM ( SELECT MS_TRANS, PARTNER_RESULT, PARTNER_DES, ROW_NUMBER() OVER (PARTITION BY CAST(MS_TRANS AS INT) ORDER BY TIME_ DESC) ROWNUM_ FROM OPERATOR_PROD.PARTNER_RESULT_TOTAL WHERE TRUE AND DATE((TIME_)) >= DATE('2022-09-22') - INTERVAL '1 MONTH' AND ETL_DATE = DATE('2022-09-22') ORDER BY MS_TRANS ) AS PARTNER_RESULT_TOTAL WHERE ROWNUM_ = 1 ) AS T2 ON T1.ID = T2.MS_TRANS LEFT JOIN ( SELECT TYPE_, CREDIT_DEBIT, AGENT_, NAME_, GROUP_ FROM OPERATOR_PROD.DIM_VH_SQI_AGENT_SERVICE_PROD WHERE '2022-09-22' BETWEEN EFF_START_DATE AND EFF_END_DATE ORDER BY TYPE_, AGENT_ ) AS T3 ON TRUE AND T3.TYPE_ = T1.TYPE_ AND SIP_HASH(T3.AGENT_) = (CASE WHEN T3.CREDIT_DEBIT = '+' THEN T1.CREDITOR WHEN T3.CREDIT_DEBIT = '-' THEN T1.DEBITOR END) LEFT JOIN ( SELECT ID, USER_PAYMENT, SERVICE_CODE, AMOUNT FROM BITEAM_EXP.PAID_USER_TRANS WHERE ETL_DATE = '2022-09-22' ORDER BY ID ) AS T4 USING(ID) LEFT JOIN BITEAM_EXP.D_SERVICE_LIST AS T5 USING(SERVICE_CODE) LEFT JOIN ( SELECT T.* FROM ( SELECT * FROM UMARKETADM.DIM_AGENT_REF WHERE TRUE AND DATE('2022-09-22') BETWEEN EFF_START_DATE AND EFF_END_DATE AND NOT IS_DELETED ) AS T ) AS T6 ON T4.USER_PAYMENT = T6.BODYID ) SELECT TAM1.ID, TAM1.STATE, TAM1.RESULT, TAM1.BANKCODE, COALESCE(TAM1.AGENT_, TAM1.SERVICE_CODE), COALESCE(TAM1.NAME_, TAM1.SERVICE_DESCRIPTION), TAM1.PARTNER_RESULT, (CASE WHEN COALESCE(T3.TECH_DESC, T4.TECH_DESC) IS NULL THEN TAM1.PARTNER_DES ELSE COALESCE(T3.TECH_DESC, T4.TECH_DESC) END), TAM1.TYPE_, EXTRACT(EPOCH FROM TAM1.CREATED)::INT, TAM1.GROUP_, TAM1.USER_PAYMENT, TAM1.AMOUNT, COALESCE(T3.TECH_DESC, T4.TECH_DESC), COALESCE(T3.ERROR_TYPE, T4.ERROR_TYPE, 4), CASE WHEN COALESCE(T3.ERROR_TYPE, T4.ERROR_TYPE, 4) = 1 THEN 'MService' WHEN COALESCE(T3.ERROR_TYPE, T4.ERROR_TYPE, 4) = 2 THEN 'Partner' WHEN COALESCE(T3.ERROR_TYPE, T4.ERROR_TYPE, 4) = 3 THEN 'Customer' WHEN COALESCE(T3.ERROR_TYPE, T4.ERROR_TYPE, 4) = 4 THEN 'Other' WHEN COALESCE(T3.ERROR_TYPE, T4.ERROR_TYPE, 4) = 0 THEN 'Success' END, COALESCE(SUBSTRING(TAM1.REFERENCE::VARCHAR, 0, 4), '') || 'xxx' || COALESCE(RIGHT(TAM1.REFERENCE::VARCHAR, 3), ''), '2022-09-22' FROM TAM1 LEFT JOIN ( SELECT TECH_DESC, ERROR_TYPE, MSCODE FROM OPERATOR_PROD.VH_SQI_MAPCODE WHERE TRUE AND ETL_DATE = '2022-09-22' AND AGENT = 'UhlPANHkpTapgILgOmC9mQ==' AND FUNCTION = 'version2' ) AS T3 ON TAM1.RESULT = T3.MSCODE LEFT JOIN ( SELECT AGENT, PARTNERCODE, ERROR_TYPE, TECH_DESC FROM OPERATOR_PROD.VH_SQI_MAPCODE WHERE TRUE AND agent <> 'core' AND ERROR_TYPE <> 0 AND FUNCTION = 'version2' AND ETL_DATE = '2022-09-22' ) AS T4 ON TAM1.PARTNER_RESULT = T4.PARTNERCODE AND ENCRYPT_HIGH(TAM1.AGENT_) = T4.AGENT WHERE TRUE