topic Re: How to call a routine in query in Talend Studio

How to call a routine in query

Anonymous — Mon, 11 Jun 2018 16:11:47 GMT

Hello, I want to perform a select request on an access database like this:

"SELECT TABLENAME.Something

FROM TABLENAME

WHERE TABLENAME.Something > routineName()

ORDER BY TABLENAME.Something ASC"

I've tried different return type (string and integer) routines and syntaxes like:

"...>"+ routineWhoReturnAString() +"ORDER BY..."

but i always get a null pointer exception or an invalid schema name exception.

Thank you !!!

EDIT : My nullpointereexception comes from a condition in an "if" link even though it was said to be an error in the input componant.

The correct syntax in the query, if the routine returns a String, is:

"SELECT TABLENAME.Something

FROM TABLENAME

WHERE TABLENAME.Something > "+ class.method() +"

ORDER BY TABLENAME.Something ASC"

NOTE: Using a global variable works great too.

Re: How to call a routine in query

TRF — Mon, 11 Jun 2018 16:17:28 GMT

I'm afraid you need to put the routine's result into a global variable, then reuse this variable into your query

Re: How to call a routine in query

Anonymous — Mon, 11 Jun 2018 16:21:03 GMT

Try this....

"SELECT TABLENAME.Something
FROM TABLENAME
WHERE TABLENAME.Something > " + routines.classname.methodName() + " 
ORDER BY TABLENAME.Something ASC"

Routines are just classes. The package is "routines", the class name is the routine name and then you have the method name. I'm assuming your method is returning a number (as a string or an int), so it can be expressed as above.

The best way to test dynamically created SQL is to create it in a tJava and output to the console using a System.out call. This will allow you to see what you are generating and test it in a SQL query analyzer.