As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at firstname.lastname@example.org and provide your corporate email address, company, and name.
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
CUST_NAME = 'None'
YEAR_MTL >= '201901' and (CUST_NAME IS NULL OR CUST_NAME ='')
SQL SQLMSS COMMIT WORK
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,
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.