Custom SQL Where Clause

1

It is possible to use column values from one EasyMorph Dataset within the ‘Where Clause’ of a ‘Custom SQL’ statement of an ‘Import from database’ function?

I have a complex ‘Custom SQL’ statement within an ‘Import from database’ function that needs to be prefiltered by values that already exist within an EasyMorph Dataset.

I know I can use the ‘Select matching database rows’ function, but that does not allow for the ‘Custom SQL’ statement.

I also understand that if ‘Select matching database rows’ function, I can then transform the data within EasyMorph to match what my ‘Custom SQL’ statement is doing but I was trying to avoid additional work.

Thanks,
David

2

Custom SQL expressions in EasyMorph allow inserting parameters. So you can generate the WHERE clause using usual actions and functions in EasyMorph, and then insert it via a parameter into custom SQL in another module.

SELECT * FROM Table1
WHERE {Where clause}

See the example below. It doesn't work out of the box because it uses a dummy connector, but it conveys the logic.

custom-where-sql.morph (5.8 KB)

3

Thank you for getting back to me so quickly.
I am still running into some issues.

This is my Custom SQL for the Import from database action within the submodule.
select * from [orditem] where [order_numb] in {WhereParam}

When I Run the query in the submodule, I receive the following error message:
Incorrect syntax near ‘in’

From the main module, I confirmed that the value of {WhereParam} is:
('547016','547020','547022','547067','547078','547211','547218','547230','547243','547281','547293')

If I place the above value into the Default Value for {WhereParam}, my select query works fine.
I am not sure what is occurring and not sure if this is enough information to assist you.

Thanks,
David

4

Check how the list is calculated and the parameter is assigned. It must be a trivial error.

Notice that in my example I used a first column value to assign the parameter in the "Call" action.

5

I would try the following:

  • Go to the "Import from database" action and open the "Query Editor"
  • Click on "View SQL" and take a look at the SQL statement, which replaces all parameters with current values.
  • Copy the statement to the clipboard
  • Run SQL in DBVisualizer or another DBMS Tool to check for error. At least you will see, that this is no error of easymorph. :slight_smile:
    image
    image1229×250 17.4 KB
6

Thank you both for your help.

After extensive testing, this is the only way I could get this to work.

  1. There can be no spaces between order numbers in the Custom SQL where clause. Thus, it is formatted like this:
    a). ('547016','547020','547022','547067','547078','547211','547218','547230','547243')
    b). I am not sure why; perhaps this has something to do with the version of SQL Server that I am using, although when executed in another IDE like SSMS, spaces are acceptable.

  2. The parameter in the ‘where clause’ of the Submodule must have a Default Value. If not, the ‘Import from database’ action will fail in the Submodule.
    a). In this case, I have used a dummy order number ‘000000’, which returns zero records in the Submodule.

  3. In the Main Module, within the ‘Call another module/project,’ the ‘Return table’ must be set to ‘Default.’ This will return the query results to the Main Module based on the column values passed to the parameter in the Submodule.

I don't know if this is how it is supposed to work, but for now, it does.

Thanks again for the help.

David

7

Under the hood, EasyMorph passes the constructed SQL query verbatim to the database driver. The difference can come from the database driver - we've seen how native and ODBC drivers behave slightly differently, even when they target the same database.