I’ve been using the table difference finder node without issue on a variety of Oracle tables without issue. We recently switched over to a SQL Server table and when I use the Table Difference Finder there, I am getting missing values when there are none.
For example, I have a 2 row table and the DB Reader output shows the two rows with values and looks great. However, when I hook that up to the difference finder, it says that table is effectively missing all values. The same workflow with Oracle was successful. Is there a trick to SQL Server tables that I need to do?
I have tried reproducing the issue, but so far it wasn’t successful. The DB Reader node outputs a KNIME data table, and if you add a Table Difference Finder node after it, the comparison will happen in the KNIME table, so it should be independent from the database where the data was read from.
Could you please send us a screenshot of the failing workflow?
Please see the screenshot of the workflow. It doesn’t fail but it shows a difference when there is none. The table results in for both SQL Server and the Snowflake tables are actually identical. However, when I run the difference finder, it shows the red question marks. Not a big deal on a small table but this is happening on all of the tables in the database. The top table is the data in the SQL Server DB Reader results and the bottom one is the difference finder results.
Thank you for the screenshot and the description. With those, I was able to reproduce the issue, which, I think, is caused by the differing column names: in the table connected to the SQL Server node (the “Compared table”), all column names are in all lower case, while in the Snowflake-based table (the “Reference table”), column names are capitalized. Because of this difference, columns of the Reference table cannot be paired with any of the columns of the Compared table by the Table Difference Finder node. You can also check this if you tick the “Compare entire tables” check box in the node configuration window and re-execute the node. In this case the first output table will have 8 rows since none of the columns in the two tables have pairs in the other table.
Could you please harmonize the column names between the two tables and re-execute the workflow to check if it fixes the problem?
Thank you. That looks to be the problem. I used the column rename node to harmonize the names and that seems to have worked. Appreciate the help on this.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.