Can I 'join' 2 tables, 2 separatDBs in 1 command with differing data ?

[crogers111]

CR XI
SQL

I have a report that works fine but I need to add it as a subreport to another report and I'm trying to see if I can optimize it.

There are 2 tables in 2 separate databases needed for this subreport..both DBs are on the same server.

Table1 = HC.Data
Table2 = Claims.Claim Header

Currently as a standalon report, I'm using a command for the first DB and am connecting directly to the Claims DB for the 2nd connection and have linked the 2 tables in CR Database Expert and all works but just a bit slow.

Command for Table1:

SELECT VenGroupNumber, CheckDate, ClaimNumber, Vendor, FromDate, DateReceived, EntryDate, ReplacedClaim, ReversalFlag
FROM dbo.Data AS Data
WHERE (VenGroupNumber = '{?GroupNum}') AND (Vendor = 'BRMS') AND (CheckDate >={?PaidFrom}) AND
(CheckDate <= {?PaidTo}) AND (ReversalFlag = 'N') AND (ReplacedClaim IS NULL OR ReplacedClaim = '0')


SQL for Table2:

SELECT "Claim_Header"."holdReason", "Claim_Header"."ClaimNumTxt", "Claim_Header"."Group #"
FROM "Claims"."dbo"."Claim Header" "Claim_Header"

Currently the report is linked via a LOJ on 2 fields:

Data.VenGroupNumber --> Claim_Header.Group #
Data.ClaimNumber --> Claim_Header.ClaimNumTxt


I guess what I'm trying to do is mimic and optimize the joins that I currently have between the command and the table but have only 1 'connection' or command... wondering if this can be accomplished in a single command or other method ?

I've read a lot of examples that incuded using a UNION but it sounds like each query needs to have the same number of fields and data types to use this.

 

[kray4660]

My experience is that working with two (or more) data sources is pretty slow. I have not worked much with bursting indexes to see if it helps.

I don't believe you can pull from two different DB's in one command (but my experience there is limited).

A union will only work if you have the same fields in both queries.

 

[lbass]

The two databases have to use the same driver for you to do the union. You could align the fields you are joining on, but then you would have to use nulls as place holders for the fields that don't correspond and name them if in the first part of the union, as in:

Select 'Orders' as type,
Orders.`Customer ID`,
Orders.`Order Amount`,
null as CustomerName
from Orders
Union All
select 'Customer' as type,
Customer.`Customer ID`,
null,
Customer.`Customer Name`
from Customer

Note that the fields should exactly correspond by order in the select statement, as they will be "merged" into one field in the command--so it's not just a matter of matching datatype. In other words, you wouldn't want to merge Customer Name with a Product field logically. I also added a field that identifies the rows by which database they are coming from in the "type" field.

-LB

 

[IanWaterman]

The quickest solution is to get your DBA to provide a link from one DB to the other.

You can the write your command on one DB and UNION data there as the first DB almost treats the table in DB 2 as its own.

But still follow the rules outlined by LB above

Ian

 

[crogers111]

My DBA was able to create a Synonym for one of the tables which places both in the same database.

The Synonym now shows and appears to function as if it was a table in the same Database as the other table and thus it doesn't seem that I need to use the UNION option.

This in turns allows me to have just one single Command which joins between the table and synonym.

The report returns the same records as the original version of the report which pulls directly from the 2 separate databases, but runs at least twice as fast.

Thanks all for the help.