I loaded an excel file with one column - a list of ids. When I try to add the action “Select Matching Database Rows”, I cannot add the pair of columns that I try to match. What have I done wrong.
A column with the list of IDs should be in the input dataset. Therefore, place “Select Matching Database Rows” in the same table where the “Import from Excel” is, right after it. Otherwise, it won’t “know” where to get a list of IDs.
I try it again today. I selected Table 1 with the id column. Then I clicked add “Select Matching Database Rows” action. Instead of of adding the action next to Table 1, a new table popup for “Select Match Database Rows”. I could not get the “Select Match Database Rows” action to show up in Table 1.
Just drag the action into the desired location.
Wow. That is easy. I got it working! Thank you.
Looks like this breaks the normal pattern of adding an action by clicking “Add new Action” in the “Actions in Table” list. When I do that it pop-up import table instead of adding an action to the existing table.
Now I am encountering a strange error. The select table action try to create a table in the database server.
Select matching database rows action creates a temporary table because of performance reasons. Create temp table will be deleted at the end of this action workflow.
Because of the differences in temporary tables behavior in different database engines, we are using a common table instead of an actual temporary table.
So you’ll have to grand your MySQL user CREATE privilege (along with INSERT, SELECT and DROP privileges) in order to make this action to work.
Thanks for the explanation. Now it makes sense. I cannot use this because I do not have full CRUD privileges on the db server. I will work around by importing data into Easymorph and than run a left join.
I have a similar issue - “cannot execute CREATE TABLE in a read-only transaction”
Is that also because I don’t have (and will never get) CREATE privileges?
If so, how do I work around this?
Hi Hendrik,
Most likely - yes. CREATE privileges are required for the action to work.
Unfortunately, there is no universal and performance efficient workaround.
If the data which you want to match with is present in the same database as the target table,
you can use a custom SQL query to load matched rows.
Otherwise, if the number of rows in the EasyMorph table is relatively small,
you can use the “Iterate” action to import database rows, matching each of the rows of the EasyMorph table.
And if the target database table is not very big, you can load the whole table and then use the “Keep matching” action to select matching rows.
Thanks
Could you not create the temporary table in SqlLite (or MS Access a free and open database) locally?
{I just saw your posted workaround - which does what I suggested}
- “Database command” (to create a temporary DB table)
- “Export to database”
- “Import from database”. Here you can use a custom SQL statement with SELECT * WHERE … EXISTS
- “Database command” (delete the temporary DB table)
Hendrik, a temporary table is required for performance reasons.
If performance is not a problem for you, you can just use the “Import from database” and “Keep matching” actions instead of the “Select matching DB rows” action.
SQLite doesn’t support users and permissions. So you should be able to use the “Select matching DB rows” action with SQLite in any case.
As for the workaround which you mentioned - it does create a temporary table.
Hi @dgudkov,
I'm writing to you here because the question seems relevant to your post. I ask you, why does clicking on the action not end up in the currently active table and why does it have to be dragged?
EasyMorph has actions that import data and actions that transform data. When these actions are clicked in the action catalog, the behavior is different:
- When an action that transforms data is clicked in the action catalog, it's added to the current table.
- When an action that imports data is clicked in the action catalog, it's added to a new table.
This usually corresponds to the user's intention. However, sometimes, importing data should be done in the current table. Hence, the advice is to just drag the import action to the desired location.
1 Like