(Possibly related to my prior post - LOOKUP_EXT in a custom function - Strange behavior #1)
I have a common custom function that includes a lookup_ext that is called in the mapping of multiple columns in the same query transform. Imagine the mapping for COL_A, COL_B, COL_C, COL_D all make the same call to this custom function, passing local variables and some literals in as input parameters.
Then later, when I add COL_E, COL_F, and COL_G to the query transform with the same function call (different literals), I can see from my debug messages (and lack thereof) that the calls from mappings A, B, C, and D, are all executed, but the calls from mappings E, F, and G are never made! But if I delete columns A thru G from the output schema and re-add them all in the same Designer session, all the calls will execute successfully!
Perhaps similar to the previously referenced “Strange behavior #1”, I can’t help but think there is an array that is defined with the mapped function calls that is set to a fixed size in the first designer session and not extended in subsequent sessions until I remove ALL of the mapped calls and re-add them.
As you surmised in your previous query… custom functions can persist local variable values from call to call, if not instantiated/assigned in the function.
The variable declaration of the function should clear the contents of each local variable for each instance, but I have seen where failing to assign a null value to a variable allowed data from a previous row to persist through subsequent custom function calls.
The parameters and local variables are all being initialized with each call and when executed, each call made does everything as designed. The function call should be made 6 or 8 or 15 or so times for each row by appearing in the mapping for 6 or 8 or 15 or so columns in the output schema. We only have a problem when we add columns to the original query transform during later maintenance/enhancement activity. The problem lies in the fact that, once maintained, the query transform only makes the call to the original, say, 6 columns and does not even make the call to the function when I have added that 7th or 8th and Nth column in a later Designer session. Rather than a valid return value from the function, just NULL, and with my debug messages enabled, no messages indicating the function was ever reached for the 7th or 8th or Nth column.
In other words, in order to perform maintenance that requires adding another output column that calls the custom function, it seems we have to delete the original ones and then add them all (original plus new) back in. BTW, parallel execution is enabled for the custom function.
(BOB member since 2009-10-27)