Specifying parameter in SQL-query file

I have a bunch of SQL-files over which I iterate, import the data from the database and export to DSET.

In those queries I had to apply the same where clause over and over again. Is there a way to set a parameter in the SQL-file that refers to a parameter in easyMorph where the “where clause” is defined so that I can change it on one place only ?

I know it is possible with direct input of the SQL in EasyMorph but that is not what I want because I have more than 30 queries to run.

Hi Nikolaas,

Parameters are not supported in SQL queries which are read from files.

But you can try to achieve the required behavior like this:

  1. Iterate over SQL files.
  2. Import SQL file as a text with “Import delimited text” action and the following options:
    • “Separator” set to “No separator”.
    • “Columns don’t have headers” checked.
    • “Ignore quoting” in advanced option checked.
  3. Use “Aggregate” action in “Concatenate” mode to concatenate all the lines of an SQL query to a single cell.
  4. Use “Table-wide replace” action to replace some predefined string with a required WHERE clause.
  5. Use “Iterate” action to pass resulting SQL query as a parameter to a subproject.
  6. In that subproject use “Import from database” action in the “Custom SQL” mode with “Custom SQL query” field set to the parameter which contains an SQL query.

I have a similar issue: want to pass parameters to sql query. I copy & pasted the query - not running it from a file.

Here is an example:

where
PPTSH.“NewState” = ‘PaymentProcessed’
and PPTSH.“Timestamp” >= {StartMonth}
and ({EndMonth} is null or PPTSH.“Timestamp” < {EndMonth})

{StartMonth} and {EndMonth} are my parameters.

EM reports an error near ‘and’

My guess is it is referring to the second ‘and’ - the one after the parameter.

Any advice?

Can you paste this part of the query but with the parameters already inserted? You can see the actual query by pressing “View SQL” in the Query Editor (see below).

Figured it out - after 4 hours!!

where
PPTSH.“NewState” = ‘PaymentProcessed’
and PPTSH.“Timestamp” >= ‘{StartMonth}’
and (’{EndMonth}’ is null or PPTSH.“Timestamp” < ‘{EndMonth}’)

Wrapping the parameter in single quotes ‘’ solved it

Great :slight_smile:

Thanks for sharing the solution.