Transformation Expression fails to init column calculation expression

[nixda]

Hello all,

i have been trying to use a transformation function which works perfectly fine on the expression builder and Test Expression works perfectly fine as expected. However when running the tasks i have the following errors. What could possibly be wrong. Also the error message isnt very clear to understand the error.. Can you help me understand the error.?

my expression:

IIF($COL1 == 'xyz', $COL1, IIF($COL1 glob '*[0-9]*', (substr($COL1,0,instr($COL1, ' ')) || ' ' || ltrim(replaceChars($ARKTX," ","-"))), NULL))

[TASK_MANAGER ]W: Table ''.'' (subtask 1 thread 1) is suspended. Failed to init column calculation expression 'IIF($COL1 == 'xyz', $COL1, IIF($COL1 glob '*[0-9]*', (substr($COL1,0,instr($COL1, ' ')) || ' ' || ltrim(replaceChars($ARKTX," ","-"))), NULL))' (replicationtask.c:3147)

[SOURCE_UNLOAD ]E: Failed to send an end of data record (ID=1) for table with ID '1'. [1021911] (endpointshell.c:3902)

Thanks in advance.

[Heinvandenheuvel]

The expression builder does NOT check for valid column names. The runtime needs them. When I deliberately mistype a column name I get " Table...  is suspended. Failed to init column calculation expression ..."

For me this is followed by ' [TRANSFORMATION ]E: Wrong column name '...' in expression '

It may depend on where in the expression the typo is made whether you get a nice message or no.

You did NOT show the full message, please do so if further help is needed, or attached a .TXT file with the 10 lines before and after the error. - Mind you ... I've seen the transformation string being reported NOT matching the error but rather 'all transformations' when there are multiple transformations. Maybe the error is elsewhere than you think? Be sure to test simplifying  down to 1 transformation.

Other then that, it works for me using replicate V2023.5.0.152. I testes using  a minor variation to test with my dataset. My test: "IIF($TXT == 'recreated-restarted2' , $TXT, IIF($TXT glob '*[3]*',
(substr($TXT,0,instr($TXT, 'a')) || ' ' || ltrim(replaceChars($TXT,"-","!"))), NULL))"

Source TXT values as a list:  recreated, recreated-restarted, recreated-restarted2, recreated-restarted3

Target TXT values as a list:: <NULL>, <NULL>, recreated-restarted2, recre recreated!restarted3

Note, the subexpression  replaceChars($ARKTX," ","-")  is using doublequotes. It best stick to singlequotes for text

Note, IFF usage is NOT documented adn possibly not supported. The official suggestion is to use CASE WHEN ... THEN ... ELSE ... END.

Note also that you are asking in the Enterprise Manager forum, whereas this is strictly a Replicate question.

You may want to re-post in the Replicate forum, with full error and version, if further help is desired.

hth,

Hein