DBConnect Lookup with joined tables

[Yorokobi]

I have the DBConnect app successfully connected to an Oracle DB and I can successfully perform ad-hoc queries. However, I am unable to figure out how to use a DB lookup using the advanced options to use the following SQL query:

SELECT c.chan_id, c.inst_id, i.inst_name from TempTable.chan c, TempTable.inst i WHERE c.inst_id = i.inst_id ORDER BY c.inst_id

I defined the lookup in Manager >> Lookups >> Database Lookups, Advanced lookup settings. Whether I specify the chan_id, inst_id and inst_name columns as Lookup Fields and chan_id as an Input Field I get the following error when I try using it in a search or with '|inputlookup tablename':

The lookup table 'tablename' is invalid.

What am I missing?

[ziegfried]

Database lookups (and actually all scripted lookups) cannot be used with the inputlookup command. You can still get similar results by using the dbquery command, though.

One thing I noticed with your query is that you didn't use the input field in your query, which is necessary, though. Something like this is necessary:

SELECT c.chan_id, c.inst_id, i.inst_name from TempTable.chan c, TempTable.inst i WHERE c.inst_id = i.inst_id AND c.chan_id = $chan_id$ ORDER BY c.inst_id

The repacement token $chan_id$ is replaced with the actual lookup value when the lookup is executed.

Alternatively you could create a view for your query and create a simple lookup for this view, which does not require input fields.