Unable to update BE using sql query
2 posters
Page 1 of 1
Unable to update BE using sql query
Please help me on this.
We have a customer CBE and we need to append three characters in every customer ID. For example: If customer ID is 1234 then we need to do this PND:1234, here PND: is the three character which I want to append in every customer ID. To achieve this I have apply the update SQL query on the customer ID field in the mapping table, the data have been successfully updated but when I apply the full build on the mapping table then my changes has been rolled backed.
Please suggest me some solution on this.
Thank you,
Sujeet
We have a customer CBE and we need to append three characters in every customer ID. For example: If customer ID is 1234 then we need to do this PND:1234, here PND: is the three character which I want to append in every customer ID. To achieve this I have apply the update SQL query on the customer ID field in the mapping table, the data have been successfully updated but when I apply the full build on the mapping table then my changes has been rolled backed.
Please suggest me some solution on this.
Thank you,
Sujeet
Sujeet- Posts : 3
Join date : 2013-09-02
Do not update MAP or ATT Tables directly
Kalido holds the real master data in it's Generic Data Store. Mapping Tables and Attribute tables are simply representations of this. The only exception to that is a Custom CBE.
The right way is to update through a normal File Def / ULC Load. This is very simple to do. Create a one time load that selects from the existing Attribute Table. Something like:
SELECT cust_id
,'PND:' || cust_id NEW_CUST_ID
,sysdate TRANSACTION_DATE
FROM att_cust
WHERE start_date <= sysdate
AND end_date > sysdate
AND BE_ID != '$NOOID'
Use this as an input to the load. Map the CUST_ID to the current ID and NEW_CUST_ID to the new customer ID. Run this and it will update them all. Rebuild ATT and MAP tables after.
Note that the SQL above is for ORACLE. You did not say what you are using. It would be different syntax for Teradata or SQL Server.
You may want to think about the Time Variance impacts also. You could do a Purge State on the values after completion or set the date as required.
The right way is to update through a normal File Def / ULC Load. This is very simple to do. Create a one time load that selects from the existing Attribute Table. Something like:
SELECT cust_id
,'PND:' || cust_id NEW_CUST_ID
,sysdate TRANSACTION_DATE
FROM att_cust
WHERE start_date <= sysdate
AND end_date > sysdate
AND BE_ID != '$NOOID'
Use this as an input to the load. Map the CUST_ID to the current ID and NEW_CUST_ID to the new customer ID. Run this and it will update them all. Rebuild ATT and MAP tables after.
Note that the SQL above is for ORACLE. You did not say what you are using. It would be different syntax for Teradata or SQL Server.
You may want to think about the Time Variance impacts also. You could do a Purge State on the values after completion or set the date as required.
Peter Lamb- Posts : 7
Join date : 2011-11-18
Location : Toronto Canada
Re: Unable to update BE using sql query
Thanks Peter, this will help me lot.
Just one question "How I will do a Purge State on the values after completion". Please suggest.
Thank you,
Sujeet
Just one question "How I will do a Purge State on the values after completion". Please suggest.
Thank you,
Sujeet
Sujeet- Posts : 3
Join date : 2013-09-02
Familiarity with Kalido
Purge state is a normal part of the reference data purge utility. The same applies to the ability to update a code.
Have you taken the Kalido training? I think you need to get some further exposure.
Have you taken the Kalido training? I think you need to get some further exposure.
Peter Lamb- Posts : 7
Join date : 2011-11-18
Location : Toronto Canada
Similar topics
» What to do when facts change? Update the fact table with update and deletes? or use SCD type 2?
» Data Integration and ETL is Shifting to the End-User
» Query on Dimensions
» data puzzle - looking for a query to solve this
» Bridge table FK query
» Data Integration and ETL is Shifting to the End-User
» Query on Dimensions
» data puzzle - looking for a query to solve this
» Bridge table FK query
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|