topic global variable in SQL inside lookup not working as advertised in Talend Studio

global variable in SQL inside lookup not working as advertised

tale103108 — Sun, 15 Nov 2015 22:34:17 GMT

Talend 6.0
Linux 6.5

If you look at the Talend Help Center doc at https://help.talend.com/search/all?query=Handling+Lookups
you will see an example of using a global variable in a lookup. This is exactly what I want to do but I cannot get the SQL
to parse correctly as described in the Help Center document.
It uses as an example SQL

"Select * from person where id=" (Integer)globalMap.get("id")"

but when I enter my lookup SQL as a Built-In Query Type (see below) I get syntax errors - is the doc incorrect or is it my SQL?

"select PayCode, PayId
from Payment 
where MaximumPay >= "   (Integer)globalMap.get("Pay")   "
and MinimumPay <= "   (Integer)globalMap.get("Pay")"

My error message is:

String literal is not properly closed by double-quote

Added notes: Tried \"Pay\" with no luck. "Pay" is a global variable set with tSetGlobalVar prior to the lookup and the Lookup Model is set to "Reload at each row".

Re: global variable in SQL inside lookup not working as advertised

Anonymous — Mon, 16 Nov 2015 00:41:52 GMT

You don't need the quote at the end.
Try....

"select PayCode, PayId
from Payment 
where MaximumPay >= " + (Integer)globalMap.get("Pay") + "
and MinimumPay <= " + (Integer)globalMap.get("Pay")

Re: global variable in SQL inside lookup not working as advertised

tale103108 — Wed, 18 Nov 2015 22:41:18 GMT

Thank you rhall_2.0, that worked.

Re: global variable in SQL inside lookup not working as advertised

Anonymous — Thu, 19 Nov 2015 01:47:45 GMT

To explain, you only need to place double quotes in SQL statements, the globalMap statements arent SQL so thats why they are outside the double quotes, and appended to the SQL statements with the + sign.