How to assign a value of a column in query transform to a Global variable

[Former Member]

I have a value that is coming within query transform, just 1 row 1 cell value, how to assign this value to a Globalvariable.

Also is there a way to perform select count(*) from a table? using Data services transform objects?

Thank you very much for teh helpful info.

[former_member190054 · Accepted solution]

Hi Roger,

Create a custom function inside that assign the column value to the Global variable .

And for getting count u have two methods,

1. write a sql statement inside script or custom function.

   SQL('Data Store','Select count(*) from table');

2. use inbuild database function table_rows;

Please try this and let me know...

Regards

Asgar

[Former Member · Accepted solution]

Hello Asgar, I read your articles extremely good .

Regarding using sql statement within script, i can do that, but the problem is i would like to find a way to assign this Query_1.PCenter value to a global variable.

Please kindly if you don't mind can you provide some steps on building this new custom function , to which i can assign the query_1.Pcenter value?

for sure this query_1.PCenter  will have just one single value only.

How can use Tab_rows function what does it do and how can i get it.

In my project they want to know how many rows got processed. and having difficulty on acheiving that.

Thank you Asgar, you have a good day.

Roger.

[former_member190054 · Accepted solution]

Hi Roger ,

Please follow below step to assign the column value to a glabal variable.

1. Create a new customer function(F_Test)

2. Create a global variable on job level.($g_test)

3. Inside a custom function create a parameter(Input).($p_test)

4. and output parameter is return only.

5. write below statement inside the custom function

$G_test = $P_test;

Return  $P_test;

6. call this function on the column in the query transform which u want to store.

Also make sure that the glabal varibale and parameter data type s are matching each other.

For find number of row processed inside a qery transform u can use function 'gen_row_num() ' that will generate a sequence number for each row so that u can take the max of this to function to find out how much row processed.

Or if u want to find out outside the query transform then

1. Put a script after this dataflow.

2 . write simple sql statement inside that and store this value to a  gloabel variable .

$Count = sql('Data store ','selct count (*) from table');

or

Total_count(data base table);

Regards

Asgar

[former_member202087 · Accepted solution]

Asgar,

I am trying to follow your steps to assign the column value to the global variable.  When I get to step 5 I get an error message that says I am referencing an undeclared variable ($G_Test).

I created the variable on the job level and can see it when I am creating the function.  How do I actually declare it so that the function recognizes it?

[former_member190054 · Accepted solution]

hi ken,

i hope you have created the global variable at job level. in the customer function editor , on left sid u can see the tab  called global variables and inside that select the job where u declare the variable and then global variable

i hope the issue is a minor issue,may be spelling mismatch.and also let me know is it is error or a warning.

if it is warning dont care about that, the formula will work.

to check this formula give an print statement after the dataflow and check wether the global variable is assigned correctly.

please let me know whether this will helpfull for you or not?

regards

Asgar

[Former Member · Accepted solution]

Hello Asgar boss, Thanks a lot for the helpful details.

Regards, Raj

[former_member190054 · Accepted solution]

Thankx Buddy,

Regards

Asgar

[former_member202087 · Accepted solution]

It is definitely just a warning.  When I call the function in my transform it is populating the field correctly, however, the global variable itself does not appear to be getting set.  Even in debug I can see that the global variable stays as NULL.

[Former Member · Accepted solution]

Yes, I'm running into the same issue.

I have declared the GV at the job level and written the CF, but when I validate it, it says " Declare the variable in the context that it is being used."

Pretty frustrating for something that should be simple

Clay