Leading zeros getting truncated

dataservices job design
1

Hi - DS 12.2.0.0
I am moving a field from a source table to a target table as-is (no transformations applied). a varchar (length 512) to a varchar (length 512).
The source field can be a variable length up to 18 bytes.

Leading zeros are getting truncated.

i have tried this to get the field length and then to lpad the target field with zeros:
lpad(SOURCE_FIELD,length(SOURCE_FIELD),‘0’)

This throws an error msg that says the 2nd lpad parameter must be an interger constant.

Has anyone else seen this, leading zeros getting truncated on a varchar2 field ?
And how to solve to a variable length field, and left padding it given that DS does not accept the length function output as the integer value for an lpad.

thanks

janderson021 (BOB member since 2010-05-05)

2

I just checked and we are extracting several numbers with leading zero’s from several databases and we haven’t lost a 0 anywhere. We’ve recently upgraded from 12.2.0 to 12.2.2 but we never had this problem before either.

Are you sure that somewhere/somehow between Source and Target those values do no get converted to an Decimal/Integer type?

When you add a field in a query transform, the first (default) data type Integer (INT). The same is true for custom functions and remember, the RETURN output variable is set to INT by default!

I actually had this problem a few times but it was always down to muppetness on my part :slight_smile: Trace that column through all your data flows and functions and ensure it doesn’t get casted as an Integer or Decimal anywhere.

Now as part of your second problem: yes LPAD requires a constant.
The way to get around this is to use a 2 stage approach:

  1. in your first Query transform, do an LPAD to 512 characters - completely filling the target field up the brim with left padded 0’s.
    Include a second INT field that contains the LENGTH of the source field.

  2. In a second Query transform, do a SUBSTRING against the 512 character field, starting at the length of the original field and all the way until the end of the field - which will be a constant value of 512 anyway. (Too bad DI doesn’t have a RIGHT / REVERSE SubString function).

In the second Query tranform you can discard of that length column which has served its purpose and doesn’t need to be carried downstream.

ErikR :new_zealand: (BOB member since 2007-01-10)