Unable to update BE using sql query

View previous topic View next topic Go down

Unable to update BE using sql query

Post  Sujeet on Tue Sep 03, 2013 8:18 am

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

Sujeet

Posts : 3
Join date : 2013-09-02

View user profile

Back to top Go down

Do not update MAP or ATT Tables directly

Post  Peter Lamb on Tue Sep 03, 2013 11:51 am

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.

Peter Lamb

Posts : 7
Join date : 2011-11-18
Location : Toronto Canada

View user profile

Back to top Go down

Re: Unable to update BE using sql query

Post  Sujeet on Tue Sep 03, 2013 11:59 am

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

Sujeet

Posts : 3
Join date : 2013-09-02

View user profile

Back to top Go down

Familiarity with Kalido

Post  Peter Lamb on Tue Sep 03, 2013 12:05 pm

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.

Peter Lamb

Posts : 7
Join date : 2011-11-18
Location : Toronto Canada

View user profile

Back to top Go down

Re: Unable to update BE using sql query

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum