Query Editor Join to Excel list

1

I’m trying to create a direct SQL server query where:

select * from Table where column in {List of values from excel} ( e.g. “a”,“b”,“c”)

When running Query Editor, there’s no option to pass a list or join. Any simple workarounds that we can use in this scenario?

2

You will need to use the “Select matching database rows” action. It should be used as follows:

  1. Produce a list of values in EasyMorph (e.g. load “a”,“b”,“c” from Excel)
  2. Use the “Select matching” action with the list to query the database table

Under the hood, EasyMorph created a temporary table in the database with the list of values, and then runs SELECT * WHERE EXISTS

3

Is there an alternative approach?

We are trying to set-up automated daily reports that run on the production database but we only have a read-only access to production for security purposes, so it can’t create a temp table.

4

An alternative option would to compose the list part of WHERE clause in EasyMorph as a parameter then use it the query with custom SQL WHERE statement with the parameter inserted.

In the example below, the value of parameter {List} is "ca","va","ma", so the query works correctly.

image

Here is a sample project that does it.
custom-sql-where-in-list.zip (249.8 KB)