Visual Query Builder using IN when connecting to excel file

[cowannbell]

Our database is very large, so I'm trying to filter as much data as possible in the Visual Query Builder.  I'm try to do this.  It is not accepting the file location.  Can I do this and if so, what am I doing wrong?

select GDAD00D.TBL_DAD_RATES_AUSH.SERVC_CD,
GDAD00D.TBL_DAD_RATES_AUSH.RATE_SYS_CD,
GDAD00D.TBL_DAD_RATES_AUSH.RATE,
GDAD00D.TBL_DAD_RATES_AUSH.EFFTV_DT,
GDAD00D.TBL_DAD_RATES_AUSH.SPCLTY,
GDAD00D.TBL_DAD_RATES_AUSH.SERVC_TYPE,
GDAD00D.TBL_DAD_RATES_AUSH.ERR_IND,
GDAD00D.TBL_DAD_RATES_AUSH.TRMN_DT
from TBL_DAD_RATES_AUSH
where GDAD00D.TBL_DAD_RATES_AUSH.RATE_SYS_CD IN (C:\data\DADs.txt)
and GDAD00D.TBL_DAD_RATES_AUSH.ERR_IND = 'N'
and GDAD00D.TBL_DAD_RATES_AUSH.TRMN_DT =
order by GDAD00D.TBL_DAD_RATES_AUSH.RATE_SYS_CD, GDAD00D.TBL_DAD_RATES_AUSH.SERVC_CD

[mceleavey · Accepted solution]

Hi @cowannbell ,

this method will not work simply because you are trying to define a range, but instead are providing a filepath to an external source. This is not how you reference a secondary source and so it would never understand the syntax here.

You can copy the values on your text file and present them in the WHERE clause as follows:

Alternatively you can connect using the In DB tools, which will allow you to stream in the data from your external source and join to the In DB table. This will then allow you to use the Join In Db tool to reduce your data before streaming the results out into Alteryx:

I hope this helps.

M.

[cowannbell]

Okay, so the in database is the way to go.  

Now when I try to use the Data Stream in tool and select Temporary Table it says that I need to select a table.  I can't write in the database, so that may be an issue here.

[mceleavey]

@cowannbell ,

You need to load in that external source, put it into tabular format, then use the data stream in tool to load it up into the remote DB as a temp table (it will only exist while the workflow is running) and then it joins to the table. You are essentially using the join to restrict the data. You only want those records from the source table where it joins to the external source.

M.

[cowannbell]

I can't write to this external source.  When I try to do temporary table, it says that I haven't selected a table and it won't let me create a new table.

[mceleavey]

HI @cowannbell ,

I didn't say write to an external source.

Load in the text file you were referencing in your SQL script, put it into tabular format, then stream that data in to connect to your In DB stream, as per the previous screenshot.

However, if you don't have write access to the DB, even for temp tables, then you can't do it. You'll need to carry out the first option which is to paste in the range in the IN clause.

M.

[cowannbell]

Okay, thanks.  Yes it doesn't appear that I have write access at all.