I have table A which contains a numeric value in a column. Is it possible to use this value in a where clause in an another query (table B)?
Table B has multi-million rows, and I need only the filtered ones by the value from the another table. The tow tables are in different databases accessed by different connectors.
I know, I can create an iteration and pass the values from table A as a parameter to a new module but its really inconvenient because table B would have a lot of dependencies from the another module.
The other way would be a custom query with an inner SQL in the WHERE clause but table A and B are in different databases
So is there a simple way somehow using a value as a quasi-parameter in an another query?
Thanks in advance!
Hi Mihaly and welcome to the Community!
There a few ways how to address the problem in EasyMorph:
Use the “Select matching database rows” action. It retrieves from a database table only rows with ID present in an EasyMorph table. Under the hood, it creates a temporary table in the target database and performs SELECT … WHERE … EXISTS.
First column value
If your table A contains only 1 value, you can use it directly in a filtering condition in EasyMorph version 5.1 or above. For that, use the “First column value” mode in the filtering condition as shown below.
Hello, and thank you! My filter condition would be a “greater than”, so the second option suits my needs.
I created a query with the necessary column only, however the ‘first column value’ list is empty.
What did I do wrong?
The column you’re going to use for filtering must exist in the input dataset of the action. However, in your screenshot, the action’s position is 1st in the table which means that it has no input dataset as there are no preceding actions that could produce the necessary column.
There are a few options how to resolve it:
- Move the import action from the currently selected table to the “last_finish” table after the circled action.
- Or, derive the selected table from the “last_finish” table.
- Or, move the circled action to the 1st position in the currently selected table, before the import action which query is shown in the screenshot.