Skip to main content
Question

I would like to remove leading zeros in the Replication Cockpit before they are loaded into the pool.

  • November 6, 2023
  • 1 reply
  • 50 views
jonas.frisc
Level 4

Hello everyone, I would like to remove the leading zeros from some attributes. This is not a problem with the normal extractions and can be done with an update command. The question arises as to how I can implement this in the Replication Cockpit. The data is stored in a view, which I cannot update. Does anyone already have a solution for this or a suggestion as to how I can implement this?

1 reply

T
Level 0
Forum|alt.badge.img+6
  • tim.von.l
  • Level 0
  • February 2, 2024

Hi Jonas,

In your Replication Cockpit when selecting a table you have the option of Transformation Configuration. These Transformations will be triggered by the corresponding extractions. If you want to update your view, a pracitcal approach (perhaps not the most efficient one) would be to simply create a table from the view, update the table, save it back to a view and replace the views with eachother.

This could be an example of the transformation you are looking for:

 

-- Transform the view into a table

CREATE TABLE SampleTable AS

SELECT *

FROM SampleView;

 

-- Alter the table to trim leading zeros

ALTER TABLE SampleTable

ADD COLUMN TrimmedValue VARCHAR(255); -- Assuming VARCHAR(255) as the data type

 

UPDATE SampleTable

SET TrimmedValue = LTRIM(Value, '0');

 

-- Drop the original view

DROP VIEW IF EXISTS SampleView;

 

-- Create a new view with the transformed data

CREATE VIEW SampleView AS

SELECT TrimmedValue AS Value

FROM SampleTable;

 

-- Drop the temporary table

DROP TABLE IF EXISTS SampleTable;

 

I hope this helps.

Best regards,

Tim

Community Code of ConductAccessibility statement