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?
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.