Failed Inner Join Using Custom SQL
I have two databases. Both have Serial Number fields in common. I am attempting to join both databases on this field, using the Multi-Database Joins tab, in the Database Connection Setup.
Unfortunately, one database has duplicate Serial Numbers, which was causing Bartender to hang on the inner join attempt.
The duplicate data problem has been solved by selecting the "Use Custom SQL Statement" checkbox, at the table level (and writing a more complex statement to filter out the duplicates). But when I again attempt to join the two databases, I am now unable to see the necessary field entries from the drop down menu, in the Multi-Join Database tab.
The necessary field entries are visible again, when I uncheck the "Use Custom SQL Statement" checkbox, at the table level. But the Operator and Criteria options provided in the Query tab seem too limited for filtering out duplicates. So it does my situation no good.
Is there a way around this? I do not have authority to make changes to the actual database.
I guess what I'm asking is...can two databases be joined together, when one of them is using a custom SQL statement, instead of an auto-generated query statement?
-
Hi Mario,
i have the same problem like you described. May you have a solution for this? Thanks.
0 -
Hi Harald,
I had to ditch the attempt at using a custom SQL statement on one of the tables.
I was told, by Bartender Support, that it was probably best to create a SQL view, on the server side, from both tables, and connect it to the app. So, basically, instead of inner joining the tables within Bartender, they said it was best to create a SQL view from both tables on the server, with whatever fields I need; and then connect that view to the Bartender app, as if it were a single table.
In my situation wasn't able to do that, for various reasons. So I had to bear down and further analyze the data (starting with 280,000 records), in an effort to further eliminate the duplicate field values, using the operator and criteria options within the Bartender Query tab. Took me a very long time to figure it out
0 -
I would think this could also be circumvented by joining the tables in the query (although how you did that would depend on your database platform). The OP didn't list if the databases existed in different databases on the same server, or completely different systems. If on SQL Server for instance, this could be handled with a linked server if on multiple systems or different database instances, or by prefixing the database name to the database objects if on the same SQL Server instance. Other platforms would be similar... Of course, if you don't have access to add a view, you may also not have linked-server access either.
0
Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.
Kommentare
3 Kommentare