Custom Function Error - BODI-1111182

dataservices job design
1

I am trying to create a custom function which will parse empty or null string fields and substitute them with a padded string. The custom is as below

$outStr = lpad($padStr, $inputStrLen , $padStr) ;

Return (ifthenelse( ltrim_blanks( $inputStr ) = ‘’ , $outStr , nvl( $inputStr , $outStr ))) ;

here inputStr (varchar) is the input string, inputStrLen(int) is the length of the input string and padStr (varchar) is the string which will be used to pad if input string is empty. All these are defined as parameters

outStr (varchar) is defined as a local variable.

I get an error for the first line which says.

[Function:CF_SubstEmptyField]
The function <CF_SubstEmptyField> contains an invalid expression. Additional information: <The second parameter of the function must be an integer constant.>. (BODI-1111182)

The inputStrLen is defined as int. So whats wrong here :?

anup_kumar_n :us: (BOB member since 2008-04-29)

2

If your input string is null, the length(input string) could be null or a zero. You should be checking for a length > 0 before doing the lpad.

dnewton :us: (BOB member since 2004-01-30)

3

I am doing that. I did not include that piece. I am getting the error at validation time itself. I have not reached runtime yet.

So my whole function reads.

if ( $inputStr is null or
$inputStrLen is null or
$replaceStr is null )
begin
Return null ;
end

$padStr = ltrim_blanks( $replaceStr );
$outStr = lpad($padStr, $inputStrLen , $padStr) ;

Return (ifthenelse( ltrim_blanks( $inputStr ) = ‘’ , $outStr , nvl( $inputStr , $outStr ))) ;

And i get the error mentioned when i click on the button to validate the function.

anup_kumar_n :us: (BOB member since 2008-04-29)

4

Ahhh, that. Imagine the mapping of a varchar(3) is

lpad(‘9’, $x, ‘0’)

Should that raise a validation error, yes or no? It depends, if $x <=3 then the lpad returns a string of length(3) or less and all is in other, otherwise if $x=100, you will lose data assigning it to a varchar(3).

As a result, development decided that the length portion of the lpad cannot be a variable.

Workaround:

substr(‘0000000000’, 1, 10-length($inputStr)) || $inputStr

For a substr() at least we know the maximum length.

Werner Daehn :de: (BOB member since 2004-12-17)

5

Thanks werner, the substr worked.

Now i have the custom function as

if ( $inputStrLen is null or
$replaceStr is null )
begin
Return null ;
end

$padStr = ltrim_blanks( $replaceStr );
$dummyStr = lpad( $padStr , 50, $padStr );
$outStr = substr( $dummyStr ,1, $inputStrLen );

if (ltrim_blanks( $inputStr ) = ‘’ )
begin
Return $outStr ;
end
else
begin
Return (nvl( $inputStr , $outStr )) ;
end

anup_kumar_n :us: (BOB member since 2008-04-29)