Tutorials & Examples Web-help Blog

Select matching database rows - select all columns


#1

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


#2

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)

#3

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


#4

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.


#5

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.


#6

@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.


#7

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:


#8

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


#9

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.