Validate Source to Target ETL mapping Using Single SQL Query

I got the following mapping table and asked me to validate source to target the ETL mapping using Single SQL query. How can we validate source to target the ETL mapping using Single SQL query? I need help to write a query.
Source to Target Mapping Document
Target Table Target Column Source Table Source Column Transformation rule ( ....)
TRG_LOG LOG_KEY SCA ID
TRG_LOG ORDER_NUMBER SCA ORDER_NUMBER
TRG_LOG SYSTEM_MESSAGE SCA DETAIL
TRG_LOG MESSAGE_ID SCA MESSAGE_ID
TRG_LOG AMOUNT SCA MESSAGE_ID,DETAIL (MESSAGE_ID= ‘100’ Then Refunded;
= ‘114’ then placed for ELSE NULL)
TRG_LOG REASON SCA MESSAGE_ID,DETAIL (MESSAGE_ID= ‘101’ Then being and ELSE NULL)
TRG_LOG FRAUD_TYPE SCA FRAUD_TYPE (Populate for MESSAGE_ID= ‘104’)
TRG_LOG FRAUD SCA FRAUD (Populate for MESSAGE_ID= ‘104’)
TRG_LOG INITIAL_PRICE SCA MESSAGE_ID,DETAIL (ONLY FOR MESSAGE_ID= ‘134’ extract value between from and to)
TRG_LOG NEW_PRICE SCA MESSAGE_ID,DETAIL (Only for Message_ID= ‘134’ extract value after to)
TRG_LOG UPDATE_DATE SYSDATE

Interview Candidate
Oct 6th, 2016
6
13608

ETL

Answer

Showing Answers 1 - 6 of 6 Answers

Deepti

Jan 5th, 2017

Write separate queries as shown below and join by union. Here, it is assumed that LOG_KEY is the key to join both the tables. This way we can know what is the validation that failed and the for which ID it failed.

Code
SELECT T.LOG_KEY AS ERROR_COL,  ORDER_NUMBER FAILED AS ERROR_MSG


FROM TRG_LOG t


          INNER JOIN CSA s ON s.ID=t.LOG_KEY


WHERE NVL(t.ORDER_NUMBER,9)<>NVL(s.ORDER_NUMBER,8)


UNION


SELECT LOG_KEY AS ERROR_COL,  DETAIL FIELD FAILED AS ERROR_MSG


FROM TRG_LOG t


          INNER JOIN CSA s ON s.ID=t.LOG_KEY


WHERE SYSTEM_MESSAGE<>s.ORDER_NUMBER


UNION


------------------------- ETC....