Unable to return lookup value to variable using Lookup_ext function

[Former Member]

Hi Guys,

I tried many ways to output a lookup value to the parameter or global variable using lookup_ext function in Mapping Tab, but still cant make it work. I am using Data Services XI3.1

The senario is:

1) I used Query Transform to output 1 column as New_Col at Schema Out.

2) Then at Mapping Tab, I inserted lookup_ext function (using the Lookup_ext function Windows) as such :

lookup_ext(\[Control_DS.DBO.EXIST_FILE_ITEM,'PRE_LOAD_CACHE','MAX'], \[LOOKUP_YES],\['NO'],\[SOURCE_SYSTEM,'=',FF_Header_Record.SrcSys],\[],\[$Check_Header]) SET ("run_as_separate_process"='no', "output_cols_info"='<?xml version="1.0" encoding="UTF-8"?><output_cols_info><col index="1" expression="no"/>

</output_cols_info>' )

3) The function supposed to return success lookup value 'YES' from lookup table Control_DS.DBO.EXIST_FILE_ITEM to the column New_Col and also the parameter (or global variable) of $Check_Header. However, after execution, only the New_Col being set to 'YES', which this column actually is used as dummy purpose, but the aim is to update $Check_Header, but it is still empty or Null.

The purpose of the exercise is I am trying to find a way to return the arbitary value or flag set inside the Data Flow to outside calling parent Work Flow or Job.

I have read through the manual but still cant figure out what went wrong. Please help.

Thank you.

[Former Member]

the global variable will lose its value when the dataflow is completed, there is limitation with global variable and its initialisation inside a dataflow, its not available outside the dataflow

though you would be able to set the global varaible using custom function, but would be able to use that value only in that DF

if you need data available outside a dataflow, you will need to persist the value in a flat file or xml or table or persistent cache and read the value from that and set the variable by reading from there

[former_member236217]

> though you would be able to set the global varaible using custom function, but would be able to use that value only in that DF

>

> if you need data available outside a dataflow, you will need to persist the value in a flat file or xml or table or persistent cache and read the value from that and set the variable by reading from there

That's correct, I should have re-read the posters comment as to what the actual goal was with his data flow.

A small change would be to create a tmp_table and insert the values in that table from the custom script using the SQL command. You will accomplish two things in one shot.

-Gera Mats

Edited by: Gera Mats on Nov 23, 2008 12:47 AM

Edited by: Gera Mats on Nov 23, 2008 12:48 AM

[Former Member]

Hi,

Sorry for my confusing explaination, but I think my question is partly answered or I got to explore the concept more. Basically I wanna to :

1) Find a way to pass some checking result from the table of Data Flow to the calling parent of Work Flow or Job. Based on my example, let's say I have a datastore table called EXIST_FILE_ITEM which I will used to update and check/input to control job run environment properties.

2) I will used EXIST_FILE_ITEM as lookup table to look for the directory of source files :

lookup_ext(\[Control_DS.DBO.EXIST_FILE_ITEM,'PRE_LOAD_CACHE','MAX'], \[INPUT_DIRECTORY],\['Cannot file directory'],\[INPUT_FILE,'=',$filename],\[],\[$global1]);

3) To use the above lookup, I used a Dataflow to read EXIST_FILE_ITEM and create a dummy column called Dummy_Col at Query Transform. Then I used the lookup_ext function wizard and insert the above lookup inside the Mapping Tab.

4) The problem is that I able to retrieve the directory result value from lookup to the dummy column Dummy_Col , but the global variable ($global1) still remain NULL.

5) I try another way is to used local data flow parameter called $local1 which I created as Input/Output type to replace $global1 in the lookup. And then try to pass the lookup result of $local1 to the calling parent Work Flow variable of $WF_var1. However, $WF_var1 remain NULL and no update from $local1. The manual said Data Flow parameter supposes can't pass value to the outside, but I still can define the local parameter as Input/Output or Output type, this is another issue which I confused...???

6) Another question is Gera suggested custom function with "New Function Call". Is it not this method and my above method will have to create a dummy column to store this lookup value and then pass it to a dummy target datastore? Everytime I use lookup_ext function inside dataflow to search some value I got to output it to a dummy column and dummy datastore target, before I able to assign the value to a variable like the above example (which is my real intention) ?

[former_member236217]

I think I understand what you want to accomplish so here goes...

> lookup_ext(\[Control_DS.DBO.EXIST_FILE_ITEM,'PRE_LOAD_CACHE','MAX'], \[INPUT_DIRECTORY],\['Cannot file directory'],\[INPUT_FILE,'=',$filename],\[],\[$global1]);

You may want to try the custom function method and see if that helps you because I verified that my custom function worked to save the value. You can then do a sql() command to insert this value to a particular temp table and then run another DF to do what you want with the result.

You can't use the variable value because its scope only works outside in, not inside out as the above posted mentioned. Being the script we are pretty much at the lowest level and even though you set a value there, by going outside of the the script into the data flow its in a new scope.

Plus, who knows what column gets calculate first, second or last. You may use the variable before it its calculated. This is the reason for the scoping. Well we also never initialized this global variable to anything because we want it to be dynamic. That is why you are seeing the NULL pop up.

Its usually best practice to split apart complex actions you are trying to perform as long as we aren't dealing with millions of records.

Please let us know if this helps,

-Gera Mats

Edited by: Gera Mats on Nov 24, 2008 12:01 AM

[Former Member]

Thank you the suggestion, I see the point.

[former_member236217]

> 3) The function supposed to return success lookup value 'YES' from lookup table Control_DS.DBO.EXIST_FILE_ITEM to the column New_Col and also the parameter (or global variable) of $Check_Header. However, after execution, only the New_Col being set to 'YES', which this column actually is used as dummy purpose, but the aim is to update $Check_Header, but it is still empty or Null.

I'm not sure I totally I understand but let me know. Are you looking to set the variable $Check_Header to whatever the output from the lookup is? If that's the case, create a custom function where you do the lookup and assign the global variable to the output as in the example below.

lookup_ext([source_ds.SOURCE.CUSTOMER_MASTER,'PRE_LOAD_CACHE','MAX'], [CUSTOMER_NAME_1,INDUSTRY_ID],[NULL,NULL],[CUSTOMER_ID,'=',$cust],[],[$out1,$out2]);

$Check_Header = $out2 ;

print( $Check_Header);

return 1;

Please note the bolded variable outputs in your lookup. You can then assign your Global Variable to one of these. In the mapping do "New Function Call" and reference the custom lookup function.

> The purpose of the exercise is I am trying to find a way to return the arbitary value or flag set inside the Data Flow to outside calling parent Work Flow or Job.

Not sure what you mean by this....

> Thank you.

Please let us know if this helped,

-Gera Mats

Edited by: Gera Mats on Nov 22, 2008 9:03 PM