Hi All, I am new to DM. I have a process flow with 2 data flows and 1 procedure to update CUST_NAME column in target table.
In FIRST_DATA_FLOW , I am getting source data from oracle and load the data in MS SQL server stage table Table_stg_1 (new target) .
In SECOND_DATA_FLOW, I am using Table_stg_1 table and loading the data into production table Table_Target_1(existing target) in the same MS SQL server .
After SECOND_DATA_FLOW, I have a procedure to update CUST_NAME column as "None" for null values using below code.
SQL SQLMSS SET SERVER CONN_1
SQL SQLMSS
UPDATE Table_Target_1
SET
CUST_NAME = 'None'
WHERE
YEAR_MTL >= '201901' and (CUST_NAME IS NULL OR CUST_NAME ='')
END
SQL SQLMSS COMMIT WORK
END
Previously this flow worked fine. But now I have a issue.
My issue is, After running the above process flow, CUST_NAME column has only 'None' for all the rows in Table_Target_1 table . When I check the source table (Table_stg_1), it's showing the valid values for CUST_NAME.
Then I checked the sample data option from SECOND_DATA_FLOW >> Column Selection window(SQL Columns), Sample data also showing values for CUST_NAME column. But I couldn't figure out why Table_Target_1 table has only "None" in CUST_NAME column.
My assumption is CUST_NAME filed is loaded as null for all rows while loading the target table.
I Can't rerun this process flow in production to test the data load. So I replicated the same process flows and logic with Test table and run the process flow. when I check the test target table, CUST_NAME column is updated correctly. But I don't know why it's not updated correctly in production table.
Can anyone please help me how to debug these kind of issues in DM?This message has been edited. Last edited by: Maran,
It's difficult to debug procedures, that's why I prefer to have all my logic in a data flow. Is this an intermittent issue in production?
Why does the CUST_NAME update have to happen after the SECOND_DATA_FLOW has executed? I would use COALESCE(Table_stg_1.CUST_NAME, 'None') function in the SECOND_DATA_FLOW to transform the value to what you want before inserting it in the target table
You might have to use a nullif function to cater for '' values. Try COALESCE(NULLIF(Table_stg_1.CUST_NAME, ''), 'None')
iWay Datamigrator 76, iWay Service Manager 5.5, Windows server 2000,
January 30, 2019, 12:52 PM
Maran
Hi Tsepe,
Thanks for your Inputs. We have some cleanup process in the CUST_NAME field after the SECOND_DATA_FLOW. after that only we do the None update.
But here the issue is CUST_NAME column not mapped correctly. I added the new column for testing and saved the flows. After that CUST_NAME column loaded correctly in target table. somehow the column remapped correctly and loaded after saving the flows. may be a glitch in column mapping and the issues is solved now.
But your point is taken. I'll try to implement my logic inside the flows as much as possible.