the only thing that comes to my mind OOTB is that you're using SUBSTR() with the positions of the pipe characters.
Normally I would use SUBSTR() from <position of one pipe> + 1 up to and including <position of next pipe> - 1.
But this has one disadvantage: it works only if there is always at least one character, otherwise you get empty strings instead of NULL values.
Could you please provide one or two sample input values and the expected output?
Thanks, now I found the problem.
The root cause of trouble is that the approach with the five v_pipe_* variable ports requires that the input string does always contain five input fields. Which is not the case here. So in some cases the string variables are set to the "remainder" of the input string, and that's of course not correct.I would change the whole approach with the v_val* ports like this (this also will make sure that they never contain the pipe signs):First (as your input strings may contain up to five fields, meaning four delimiter pipes within the string itself plus to the two extra ones) set up one more variable port v_pipe_6:InStr( in_AAS, '|', 1, 6)This makes the following steps a little easier.Next set up five more variable ports v_len_1 thru v_len_5 with these contents:v_len_1:Decode( TRUE, v_pipe_2 > v_pipe_1 + 1, v_pipe_2 - v_pipe_1 - 1, 0)
Decode( TRUE, v_pipe_3 > v_pipe_2 + 1, v_pipe_3 - v_pipe_2 - 1, 0)
Decode( TRUE, v_pipe_4 > v_pipe_3 + 1, v_pipe_4 - v_pipe_3 - 1, 0)
Decode( TRUE, v_pipe_5 > v_pipe_4 + 1, v_pipe_5 - v_pipe_4 - 1, 0)
Decode( TRUE, v_pipe_6 > v_pipe_5 + 1, v_pipe_6 - v_pipe_5 - 1, 0)
Now set the v_val_* ports as follows:v_val_1:Decode( v_len_1, 0, '', SubStr( in_AAS, v_pipe_1 + 1, v_pipe_2 - v_pipe_1 - 1))
Decode( v_len_2, 0, '', SubStr( in_AAS, v_pipe_2 + 1, v_pipe_3 - v_pipe_2 - 1))
and so on until v_val_5:
Decode( v_len_5, 0, '', SubStr( in_AAS, v_pipe_5 + 1, v_pipe_6 - v_pipe_5 - 1))
That should work, but to be honest I haven't tried it myself, I'm writing this off the top of my head.
Thanks so much - will give it a try this morning.
To be honest I'm not sure.
You might want to change the expression a bit, maybe that helps. If not, I can make another suggestion, but let's see them step by step.
For now I would change the expression terms for v_val* like this:
Current expression (for example, v_val_1):
Decode( v_len_1, 0, '', SubStr( in_AAS, v_pipe_1 + 1, v_pipe_2 - v_pipe_1 - 1))
New expression term:
Decode( v_len_1, 0, NULL, SubStr( in_AAS, v_pipe_1 + 1, v_pipe_2 - v_pipe_1 - 1))
(meaning you replace the empty string with a NULL value).
Again, I really don't know whether PowerCenter will enclose NULL strings in a target file with quotes; for empty strings it sure does, as we can see here.
1 of 1 people found this helpful
Nico has the right fix. PwC does NOT enclose NULL values in quotes but does empty strings (''). So if you switch to the new expression he provided above, even if you configure the target to enclose the values in double strings, null values will not.