Custom Functions in Query Transform

[Former Member]

Hello DS Legends,

I have a question related to DS Custom Functions. I'm able to say "New Function Call" in Query transform output schema and call my function. But the custom function is outputting the field like below:

Is the "$" infront of the field name correct or Am I doing anything wrong?

Also, instead of doing like this, I would like to map the custom function to the output field. Is it possible in DS to do it that way?

I'm not sure if I'm clear. Please let me know if you have any questions.

Thanks

Pradeep

[Former Member · Accepted solution]

Hello

You can define the invocation of a custom function using the function wizard (as you did), which is required if you have multiple output values that you wish to use.

You can also invoke a custom function directly from the mapping too, or in the where tab, although there are limitations reagarding the number of return values.

See the Reference Guide for more information.

Michael

[Former Member · Accepted solution]

Hello Michael,

Thanks for your response. When I have 2 return values, I'm making a function call and I'm getting the values in the output fields without any issues. My question here is does the $ sign come as a prefix from the function output fields (I'm not getting the $ sign in the value which is what I want)?

Also, the second scenario is:

I have a Location field in the Query output schema. I want to map this Location field to my custom function CF_GetLocation which outputs a value from the mapping tab in the query transform. When I did that, I'm getting a NULL value instead of the actual value. I'm not sure what I was doing wrong here. Please review my code and let me know the mistake.

My Custom Function:

CF_GetLocation

Here, $LV_Plant and $LV_Location are my local variables and $P_Plant is my input parameter.

$LV_Plant = $P_Plant;

$LV_Location = NULL;

$LV_Location = SQL('MyDatastore', 'SELECT Location FROM MyLookupTable WHERE Plant = \'[$LV_Plant]\'');

RETURN ('[$LV_Location ]');

My Mapping:

CF_GetLocation(QueryInput.Plant)

Please let me know if I'm not clear.

Thanks

Pradeep

[Former Member · Accepted solution]

Hello

The output column names come from the function call, I am sure you can overrride the names though (I can't check at the moment).

For the second question, (ignoring the fact that this could be done using one of the provided lookup functions) your mapping is correct, the issue will be with your function, or the datatypes.

You can repleace \'[$LV_Plant]\' with {$LV_Plant}.  Also remove the square brackets from the return statement, they are not required.

Try testing the function in a script, it is much easier to debug from there.

Michael

[Former Member · Accepted solution]

Michael,

Thanks for your response. I made the changes pointed by you. I have created a new job that has the below:

$GV_Test - Global Variable

$GV_Test2 - Global Variable

Script

Here is my script:

$GV_Test = '0010';

CF_GetLocation($GV_Test, $GV_Test1);

print ('[$GV_Test1]');

I'm getting a value from this print which is great. But when I'm mapping the custom function in the mapping tab of my query output schema, I'm getting a null value. I made some changes to the custom function also. Here is my Custom Function:

$LV_Plant = $P_Plant;

$P_Location = SQL('Datastore', 'SELECT Location FROM MyTable WHERE Plant = {$LV_Plant}');

RETURN $P_Location;

Here $P_Plant is my input parameter, $P_Location is my output parameter, and $LV_Plant is my local variable.

Thanks

Pradeep

[Former Member · Accepted solution]

You are confusing output parameters and the return value from the custom function.  To use in a mapping, the return value must be used, not output parameters.

Michael

[Former Member · Accepted solution]

Ohh. I changed my custom function to the below:

$LV_Plant = $P_Plant;

$LV_Location = NULL;

$LV_Location = SQL('DataStore', 'SELECT Location FROM MyTable WHERE Plant = {$LV_Plant}');

RETURN $LV_Location;

Is that correct? If not, can you please explain me how I can achieve this?

Thanks you so much for all your responses.

[former_member187605 · Accepted solution]

1. Make sure your RETURN type is varchar (by default it is integer, hence the null result you always get).
   
   
   
   
2. No need for any local variables. A one-line function definition will do:
   
   return sql('DataStore', 'SELECT Location FROM MyTable WHERE Plant = {$P_Plant}');
3. If this is all you want to achieve, you better check out the built-in lookup function.

[Former Member · Accepted solution]

Thanks Dirk. This worked out.

The reason for not using the built in lookup function is that I have to use this custom function in multiple places. I thought that using a SQL() will be better in regards to the performance when compared to the Lookup function . Please correct me if I'm wrong.

Thanks

Pradeep

[former_member187605 · Accepted solution]

Lookup will in most cases largely outperform your custom function, especially when you use the correct cache option. With the latter, there will be a time-consuming database roundtrip for each record in the data stream. When you use lookup and cache the lookup table, all processing will be done in memory, even when the lookup is called from different places in your DS job: the table will only be loaded once.