Select matching database rows - select all columns

Hi,

With the select matching database rows it seems that one can only check the columns that one needs.
If you would like to use this in an iteration, I believe it would crash because maybe the columns do not exist in every table. So, it would be nice if we could select all columns and that it does something like select * from in the background.

Or is there a workaround at this moment ?

Kind regards
Nikolaas

The “Select matching database rows” creates a temporary table in the target database, exports specified columns into it, then executes SELECT … WHERE … EXISTS statement, then deletes the temporary table. The same logic can be done using regular actions in EasyMorph:

  1. “Database command” (to create a temporary DB table)
  2. “Export to database”
  3. “Import from database”. Here you can use a custom SQL statement with SELECT * WHERE … EXISTS
  4. “Database command” (delete the temporary DB table)

Hi community,

I tried to work with the ‘Select matching database rows’ action, but I have a timestamp in the db table which is not allowed (or I made a mistake there) .
grafik

Now I try to solve it as you described with regular actions. I made a derived table with all the fields which should be compared, but how can I put this table into the “EXISTS” part?
*select cust_key, loc_key, dp_id, value_name, t_factor, datetime from *{table} where exists {fields of derived table}

Could You pleas give a hint how to do this? Or is there an example, which I haven’t found yet?

Thanks in advance,

Jochen

Hi Jochen,

You should not just extract all the fields which should be compared to a derived EasyMorph table
but also create a temporary database table and export those fields to the created table.

Then you will be able to use that temporary table in an “Import from database” action with a custom SQL query which should look similar to this:

SELECT * FROM "Target_Table" WHERE EXISTS( 
    SELECT 0 FROM "Temporary_Table" WHERE 
    (
        ("Target_Table"."SomeField" = "Temporary_Table"."SomeField")
        OR
        ("Target_Table"."SomeField" IS NULL AND "Temporary_Table"."SomeField" IS NULL)
    )
    AND
    (
        ("Target_Table"."OtherField" = "Temporary_Table"."OtherField")
        OR
        ("Target_Table"."OtherField" IS NULL AND "Temporary_Table"."OtherField" IS NULL)
    )
)

Actual query syntax depends on your database.

Also, don’t forget to delete the temporary DB table after the import.

Jochen, also I would like to mention two things.

First, you did not make a mistake. Non-integer numbers are not allowed in “Select matching DB rows” and “Delete matching DB rows” actions in order not to deal with the comparison of floating-point numbers.

Second, when a datetime value is exported to EasyMorph from a database, its fractional seconds part is truncated to 3 digits. For instance,

1/1/2019 01:23:34.56789

becomes

1/1/2019 01:23:34.567

So if your datetime values have seconds with more than 3 fractional digits, you have to wrap the datetime column from the Target_Table with a CAST expression which truncates column values to only 3 fractional digits. Otherwise the original database timestamps won’t match (i.e. be equal to) the timestamps imported from EasyMorph.

I can send you the exact CASE expression if you can tell me which database you are using.

1 Like

@Jochen_Marquardt,

as @andrew.rybka described above – matching timestamps can be problematic due to the truncation.

Usually, using timestamps as a primary/foreign key is not a good idea. If it’s possible to add some kind of record ID to the original table (for instance a column with UIDs), then you won’t have to do timestamp matching at all. More than that, you could just use the “Select database rows” action directly without the workaround that I described above.

Thanks Andrew,

I already found out about the “truncation” problem :grinning: , but I guess I could fix it.
Just for completion: we use a PostgreSQL database.

Now I know how it is meant. I did not recognize the first step with the creation of a temporary table and thought, I could use the table from EasyMorph. I will give it another try.

Thanks again for your quick help. :+1:

I will check out whether I can add another column to the table (I am not the owner). Thanks a lot.

Jochen, in visual mode of “Import from DB” action we internally use the following expression with PostgreSQL in order to match datetime literals, created from EasyMorph datetime values, with database timestamp columns:

date_trunc('milliseconds', "databaseColumnName") = '1999-09-01 01:49:07.061'

date_trunc() function should also work for comparison of datetime values between the mentioned earlier temporary table and your target table.

1 Like

Hi Dmitry,

Does the select matching database performs a LEFT JOIN or more like an INNER JOIN?

Now the select matching database only returns the columns from the database table. We lose the columns from our table in EasyMorph unless we create a derived table first and then perform the select matching database rows and then put that column back in the other table in EasyMorph. Why is it not possible to just append the columns from the database to the table in EasyMorph directly?

Thanks
Nikolaas

“Select matching DB rows” works like SELECT WHERE EXISTS. It’s not meant to be an equivalent of a join.

Hi Dmitry,

Are there plans to integrate a feature so that we can do joins between EasyMorph tables and database tables (e.g. a LEFT JOIN) ?

Thanks !
Nikolaas

Hi Nikolaas,

You can already do it. Just import a database table into EasyMorph and do a merge in EasyMorph. It’s just one extra action.

Hi Dmitry,

Yes that I was planning to do :slight_smile: Thanks for the advise !
Nikolaas