Okay so working on trying to find records that are technically the same but different. Comparing data between 2 different databases by using concatinate of the 3 columns. However, for my data I need to pull back records that the same 3 values but in a different order. For example: my database lets say that I have 3 columns Like this with their appropriate values:
Database 1:
Column 1 Column 2 Column 3
BDD ABC ITG
Database 2:
Column 1 Column 2 Column 3
ABC ITG BDD
So I need a query that will do the logic to see all the possible combinations these columns can have and realize that these 2 records are the same. Sorry if I didn’t explain this well enough.
There are probably several solutions to this, but here are two ideas.
-
You could add a column to each table that is a sorted version of the col1,2,3
So Database 1 would have KEYVALUE and it is ABCBDDITC, then Database 2 would have KEYVALUE which is ABCBDDITC. So you’d get a match. You’d have to write a routine to do the concatenate and sort, which isn’t impossible. I think I’ve done something like this before. If you can’t add the fields, you could make a function and maybe join on the functions - it might be slow. (personal note - I like to put separators when I do this, so I’d have ABC-BDD-ITC - which may not be necessary, but that’s just me)
-
You could cross-join the databases (tables, actually I guess). If you cross join by some field (like ID, etc), then you can just do compares:
Where
(d1_column1 = d2_column1 OR d1_column1 = d2 column2 OR d1_column1=d2_column3)
AND
(d1_column2 = d2_column1 OR d1_column2 = d2 column2 OR d1_column2=d2_column3)
AND
(d1_column3 = d2_column1 OR d1_column3 = d2 column2 OR d1_column3=d2_column3)
Might also be slow based on size of tables, but it will get the job done. Cross joins are useful in some cases like this.
The second solution would have issues if values are repeated between columns - for instance if Database 2 Column 1 is ABC and Database 2 Column2 is also ABC, you could get a match, which you may not want.
I was thinking of a sorted version too. You could probably make a CASE stmt for that. Something along the lines of
select
CASE
WHEN ((column1 < column2) and (column2 < column3)) then Column1 || Column2 || Column3
WHEN ((column2 < column1) and (column1 < column3)) then Column2 || Column1 || Column3
etc…
END as sorted_column
from table
(The above doesn’t account for nulls in any of the columns, nor cases where columns have equal values, so you’d need to work that out too if needed)
If you can make a view like that, and can have a DB Link between your two databases so that you can access them both using the same connection in Toad, then you can compare the views in Toad with Database -> Compare -> Data