How to call a routine in query

Five Stars

How to call a routine in query

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.


Accepted Solutions
Community Manager

Re: How to call a routine in query

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.


All Replies
Fifteen Stars TRF
Fifteen Stars

Re: How to call a routine in query

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


TRF
Community Manager

Re: How to call a routine in query

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.

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Download