SPL data input to SQL search?

[sarge338]

Hello All,

I have a situation in which I need to use local lookup file as input in another search, however, the secondary search will happen on an external database, using DB_connect.  So the question is, how would I read-in the inputlookup file as input (WHERE clause) into a SQL query vs a SPL search? 

I have done the opposite, in the past... use a lookup file to compare against the results of a SQL query.  

If it has any bearing on the answer, the lookup file will be a CSV with multiple values for a single field.

Thank you.

[isoutamo]

Basically you should do it as any other use of inputlookup e.g. with subquery.

Can you share your current query and some sample data, so we could help you with it?

[sarge338]

My "any other use of inputlookup" has been exclusively in SPL.  I am trying to figure out how to get that data into the SQL query as input... for the WHERE clause.

Since I really don't know how to use it, I'll show you how I would like for it to work.  I just think, if it's possible, that there is some kind of SPL voodoo that I need to do for it to work.

| dbxquery 
connection="my-remote-data" 
timeout=1800 query="
SELECT *
FROM   phonecalls
WHERE  dateStamp >= to_timestamp('2021-03-08 00:00:00.00','YYYY-MM-DD HH24:MI:SS.MS')
      AND dateStamp < to_timestamp('2021-03-15 00:00:00.00','YYYY-MM-DD HH24:MI:SS.MS')
      AND destinationnumber IN (MY_INPUT_LOOKUP_FILE) 
      AND direction = 'INBOUND' 
       

" shortnames=t
| table *

 Thanks!