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

Showing Answers 1 - 2 of 2 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
  1. SELECT T.LOG_KEY AS ERROR_COL,  ORDER_NUMBER FAILED AS ERROR_MSG

  2. FROM TRG_LOG t

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

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

  5. UNION

  6. SELECT LOG_KEY AS ERROR_COL,  DETAIL FIELD FAILED AS ERROR_MSG

  7. FROM TRG_LOG t

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

  9. WHERE SYSTEM_MESSAGE<>s.ORDER_NUMBER

  10. UNION

  11. ------------------------- ETC....

  12.  

  Was this answer useful?  Yes

swpna

  • Jun 17th, 2019
 

by using minus query (column mapping between source and target

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions