it happens that I need to filter a database table using the information from an EM table, for example:
In EM I manipulate my data and the result is a table (T1) with a single column “CustomerCode” and few rows.
After that I need to add few information taken from the Customer Registry (description, address etc.).
Usually, at this point, we create another EM table (T2) that is the SELECT * from the customer registry, then we merge T1 with T2 and add all the data we need. But I see that in this way EM read every record in T2.
So we found another way, but I need to understand if it’s a correct one or if I miss something:
We’ve got T1;
We create a module, named “Read customer info” with the “customer code” parameter. In this module we run a Select * from customers where customer_code={parameter};
We iterate T1 records on the newly created module;
The performance are really good, I need to know if it’s the best way of someone can suggest a better method to achieve this.
Yes, there is a better way. The “Select matching database rows” action does exactly that - imports rows from a database table which IDs (keys) are in an EasyMorph table. The action requires a write permission in the target database as it creates a temporary table.
I tried the “Select matching database rows” but the problem is that if in the start table I’ve got other information than the key, the action will remove them.
For example:
Customer code, Number of bought item
1234, 500
125, 700
if I select matching database rows to retrieve the customer address, the result will be:
1234, Asda Street 44 - NY
125, Jason Street 128/1 WY
Ok, it’s true that I can run the select matching before calculating the number of products (in the example), but it means that I’ve to carry alot of information throught the entire flow (or I’m wrong?)
That’s correct, the rest of the columns is removed, but you can easily merge them back (e.g. “Number of bought item” in your example) after you did “Select matching” because the key column remains.
I’ve got another question about the Select matching database: is there any way to use a custom SQL instead of a table?
My customer send me the Item+Color+Size code, and I need to keep only the rows with a valid code, so atm I read the entire Item+Color+Size registry (400K records) and then use the Keep/remove matching.
I thought that I could use the Select matching, but the source table isn’t a table but a custom SQL of three joined table, or is there a better solution?