[resolved] Assigning value to context variable

[SJ3]

Hi,
In this job, I want to get the value of min(date) as conf_time, assign the conf_time value to my context variable and use the context_variable in where query statement in pegsprodcaf02 input file.
But when I run the job, it passes 1 row from tInformixInput_2 to tFlowToIterate_1 and 0 rows from pegsprodcaf02(second input file) to dhscbindbs01al_stage. I am sure that there are around 9000000 rows in the table after min(date).
So am I missing something like tJava? that this job is not working properly or is there a problem in the way context variables is defined and referenced? or my query in where statement is not right? Thanks!

                                 

[TRF]

Hi,
Are you sure of the format and value of context.conf_date_context?
Regards,
TRF

[SJ3]

Thanks for replying! I am not sure whether this is the right way for assigning value to context variable since this is a different case. The value is coming from an input file column (column name: conf_time). I used a function min(date) to get this (conf_time)date column(in 4th screenshot). I have also used tflowiterate to assign the conf_time column value to my context variable but still the value is not assigned. Do you think that there is a problem in the way I am assigning the value?
In input file the data type of column was string but then I changed it to date using date(date) function( in 4th screenshot) and I also defined date data type for my context variable (in 3rd screenshot) so that shouldn't be a problem now.

[Anonymous]

Hi 
Link the input component to a tJavaRow and assign value to context variable there, eg:
tinformixInput2--main--tJavaRow--main--tFlowToIterate--iterate--tinformixinput1....
on tJavaRow:
context.conf_date_context=input_row.conf_time;
Regards
Shong

[SJ3]

Thank you for responding. I am now getting a new error that I have extra characters at the end of date time or internal. As you can see in attached screenshot that it shows .0 after date. How can I fix that?
I want that date time without .0 for doing date comparison in my where statement. Even date won't work in my case. The data type of conf_time column is string.
Also tflowiterate is giving me following warning:
Columns(conf_time) of parameter (customize) are not existing in the schema and the studio will take the first column instead of the original one. Please correct them manually.

[Anonymous]

Hi
In order to debug the job, try to print the value of context variable to check if it is assigned correctly from input data, eg;
on tJavaRow:
context.conf_date_context=input_row.conf_time;
System.out.println(context.conf_date_context);

on tinformixInput1, try to input a fixed value and see if it is able to extract data, for example:

"select * from tableName where date(create_date) >='2016-02-12 14:15:10'"


Regards
Shong

[Anonymous]

It looks like your conversion to a String is at fault here. However, a simple way to fix this if you have no control over the conversion is something like below.....

context.conf_date_context.substring(0, context.conf_date_context.indexOf("."))

That will remove everything from the ".".
It is obviously better to sort out the conversion, but this will get it working.

[SJ3]

Thanks for responding Shong and rhall!
Shong, I used your system code to see whether correct value was assigned to context variable or not and I found that it was assigned correctly but it still has .0 in the end! You can check in screenshot!
And I want to use context variable instead of fixed date in this job so that I can automate this job using cron in Linux. But when I used fixed date, it extracted data from the table.
Rhall, I have used your code in tjavarow(screenshot attached) but still its giving me same error of having extra characters in date time or interval. Is there any other way we can fix this error?
Thanks!
Sony

[Anonymous]

Hi Sony,
I was assuming that you would use the code I gave you in your SQL WHERE clause or something similar. Due to that I didn't specify any value assignment. If you want to change the value in your tJavaRow, use something like this....

context.conf_date_context = input_row.conf_time.substring(0, input_row.conf_time.indexOf("."));

In the above example I am assigning the value of the method I used in my previous method, to the context.conf_date_context context variable. 

[Anonymous · Accepted solution]

A slight change to cover the situation when there is no ".0" in the String (which I am assuming might be possible).
Use the following instead...

if(input_row.conf_time.indexOf(".")!=-1){
      context.conf_date_context = input_row.conf_time.substring(0, input_row.conf_time.indexOf("."));
}else{
      context.conf_date_context = input_row.conf_time;
}