In Database Tools - Compare for differences

[stapuff]

I have connection to a system table and a connection to a yxdb that was written from the system table yesterday (basically a snapshot).

Looking to compare against each other and report back the records just with the differences. Those differences could be record differences or field value differences. From a SQL perspective I am looking to do an EXCEPT between the 2.

Any thoughts on how to accomplish this would be greatly appreciated.

Puff

[mceleavey]

Hi @stapuff ,

You can achieve this using the union tool and using only those where there is a commonality of fields. You then take those outputs and join them back to the original source to determine which ones don't exist.

I've attached the workflow, but given time I could wrap this into a single tool which would effectively be an additional function of the Union tool.

In your example you will need to stream in the .yxdb data using the Data Stream In tool (you will need the in-db connection string).

Hope this helps,

M.

[stapuff]

@mceleavey 

I appreciate your response back.

Place this in the IN-DB

StreamNumber ID firstName lastName
1 1 Tim Berners-Lee
1 2 Isaac Newton
1 3 Benjamin Franklin
1 4 Thomas Edison
1 5 Henry Ford

Place this in yzdb

StreamNumber ID firstName lastName
2 1 Tim Lee
2 2 Isaac Newton
2 3 Benjamin Franklin
2 4 Thomas Edison

What I would like to have returned is

Name Value

ID 1

lastname Berners-Lee

ID 5

firstname Henry

lastname Ford

Puff

[mceleavey]

Hi @stapuff ,

you're talking about the data itself, not a metadata comparison.

That's simply a join tool, with the L and R outputs representing those that don't match.

Use a join on the key field, ie the ID field, those that don't join are completely different rows.

Those that do match, join again on the name. Those that don't join are different names for the same key field. Or simply use a formula tool, if x=y then Y else N

M.

[stapuff]

@mceleavey 

I apologize if my post was not clear.

Doing the joins and formula was the direction I was heading down before the post.  With comparing 2 data sets values with 10 columns isn't so bad... doing 90 columns is a different story.

In addition the In-Database join tool does not have the L & R output like the standard join does. It only has 1 output.

I was hoping that since the yxdb was being brought in and a temp table was getting created that additional SQL could be used to achieve what I was looking for.

End result may just be use standard tools.

Thanks,

Puff 

[mceleavey]

Hi @stapuff ,

Yeah, I would suggest streaming this out and using the standard tools.

You can apply this join technique after you've pivoted the data using the Transpose tool grouped by the key field. This will then allow you to apply this to multiple fields simultaneously.

M.