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.
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.
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.
After extensive testing, this is the only way I could get this to work.
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.
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.
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.
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.