Hi,
I have a process to fetch the data from data base and write it to a file.
Requirement as follows
Fetch the data from MAIN table, for every record I need to have a lookup into another table to fetch some more data.
I have created a job as shown in the attachment. Problem here is in the lookup table I have huge data.
When I use query without condition it is working fine, but it is taking more time.
My question is how can I apply the condition that will fetch only required records from lookup table.
Example:
Working Example:
MAIN table query is as follows
"select SECURITY_NAME,SEDOL,CUSIP,ISIN,RIC,msci_security_code from MSCI_DNSA where ISO_COUNTRY_SYMBOL='"+(String)globalMap.get("output.IsoCountrySymbol")+"'"
Lookup table query is as follows
"select sec_code,ticker,exchange from SECURITYINFO"
Not working Example:
MAIN table query is as follows
"select SECURITY_NAME,SEDOL,CUSIP,ISIN,RIC,msci_security_code from MSCI_DNSA where ISO_COUNTRY_SYMBOL='"+(String)globalMap.get("output.IsoCountrySymbol")+"'"
Lookup table query is as follows
"select sec_code,ticker,exchange from SECURITYINFO where sec_code='"+(String)globalMap.get("row3.msci_security_code")+"'"
Can you please suggest on this.
Thanks & Regards,
Syed
Hi Shong,
I have tried the same, but the problem is lookup component is not part of job process.
Because of lookup component is linking to tMap as a different process, we are not able to get the values which
are part of main job.
Please suggest on this.
Thanks & Regards,
Syed
Hi Shong,
Please suggest to solve this Issue.
Even I have tried with context variables using 'tJava' as shown in the screen shot.
This also not working.
Thanks & Regards,
Syed
Hi Shong, Please suggest to solve this Issue. Even I have tried with context variables using 'tJava' as shown in the screen shot. This also not working. Thanks & Regards, Syed
Hi,
I don't understand it don't also work in tJava component. Is there a column called msci_security_cod in row1? Can you upload a screenshot of schema of row1?
Best regards
Shong
Hi
Have a try to upcase all column name in schema and query,
msci_security_code to MSCI_SECURITY_CODE, (String)gloalMap.get("MSCI_SECURITY_CODE")
Best regards
Shong
Hi Shong,
There is no problem in queries because I am able to print the values in 'tJava'.
If you see in one of my screen shot for 'tJava' there I am setting the 'msci_security_code' value in context
and I am retrieving it from context and printing. This is working means I am able to fetch the value from context in 'tJava'
But when I try to fetch the same in lookupDB is null.
I think you understood where I am facing the problem.
Thanks & Regards,
Syed
Hi Syed
Yes, I know your problem! For me, it should work with your job, see attachment, of course, I also tested it on my local machine and it works!
Best regards
Shong
Hi Shong,
There is no problem in queries because I am able to print the values in 'tJava'.
If you see in one of my screen shot for 'tJava' there I am setting the 'msci_security_code' value in context
and I am retrieving it from context and printing. This is working means I am able to fetch the value from context in 'tJava'
But when I try to fetch the same in lookupDB is null.
I think you understood where I am facing the problem.
Thanks & Regards,
Syed
