Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [Solved] Column not Loaded/Updated correctly
Go
New
Search
Notify
Tools
Reply
  
[Solved] Column not Loaded/Updated correctly
 Login/Join
 
Gold member
posted
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,


WebFOCUS 8201m
Windows,AppStudio, DevStudio,All Outputs
 
Posts: 69 | Location: India | Registered: November 03, 2015Reply With QuoteReport This Post
Member
posted Hide Post
Hi 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,
 
Posts: 5 | Registered: April 15, 2009Reply With QuoteReport This Post
Gold member
posted Hide Post
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.

Thanks once aging!!!!


WebFOCUS 8201m
Windows,AppStudio, DevStudio,All Outputs
 
Posts: 69 | Location: India | Registered: November 03, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [Solved] Column not Loaded/Updated correctly

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.