Solved

SQL Server 2008 - Mixing Databases in a query

Posted on 2015-01-14
4
182 Views
Last Modified: 2015-01-14
Hi Experts,

First I would like to mention that I am new to all this.

I have this query:
select ch.file_ID, COUNT (*) 
from OPID_POSTPAY_BCBSNE_CORELINK_PURGE_ORIG.CLAIM_HISTORY ch
INNER JOIN ( SELECT DISTINCT OPID_POSTPAY_BCBSNE_CORELINK_PURGE.CLM_KY
				FROM OPID_POSTPAY_BCBSNE_CORELINK_PURGE.PURGE_STRIP
				WHERE OPID_POSTPAY_BCBSNE_CORELINK_PURGE.[FILE_ID] = 41552 and OPID_POSTPAY_BCBSNE_CORELINK_PURGE.HST_FG = 1
) ct on ch.CLM_KY = ct.CLM_KY
Group by ch.FILE_ID

Open in new window


As you can see this is not going to work, notice the two different DB's but they are on the same Server?

I am just running this as a test, in the real SP I am getting much different counts then what I should be getting and am just breaking this down so I can see why there is wrong counts and I suspect that the problem I am having is related to the "Inner Join".

In the SP where the "Inner Join" is instead of a Select statement it uses a temp table but I don't have access to the temp table while doing this test?  Unless there is a way to have a temp table in a query?

So in the one that is in the SP it would look like this but still I am breaking it down just to show the difference:
  INSERT INTO #CLAIM_TEMP (CLM_KY) 
				SELECT DISTINCT CLM_KY
				FROM PURGE_STRIP
				WHERE [FILE_ID] = @fileId and HST_FG = 1

Open in new window

Then the query would be:
FROM CLAIM_HISTORY ch WITH(NOLOCK)
            INNER JOIN #CLAIM_TEMP ct on ch.CLM_KY = ct.CLM_KY

Open in new window


The reason I am asking for mixing the DB's is that the query on top is a query based on results of a Dev DB using a restored DB to test the counts so please don't get confused.

Please help and thanks..
0
Comment
Question by:Amour22015
4 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40548952
How could we know there are two different databases?

If there were two different databases, then there would have been two periods in the name, i.e. database_name.[schema_name].object_name - see http://technet.microsoft.com/en-us/library/ms187879(v=sql.105).aspx for more information.
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40548955
As you can see this is not going to work, notice the two different DB's but they are on the same Server?
It will work if you had the schema since it was missing. dbo schema is the default so I added it as you can see below:
select ch.file_ID, COUNT (*) 
from OPID_POSTPAY_BCBSNE_CORELINK_PURGE_ORIG.dbo.CLAIM_HISTORY ch
INNER JOIN (SELECT DISTINCT CLM_KY
			FROM OPID_POSTPAY_BCBSNE_CORELINK_PURGE.dbo.PURGE_STRIP
			WHERE FILE_ID = 41552 AND HST_FG = 1
) ct on ch.CLM_KY = ct.CLM_KY
Group by ch.FILE_ID

Open in new window


I don't have access to the temp table while doing this test?
Temp tables exists only in the context they're created so you can't access them out of that context.
0
 

Author Closing Comment

by:Amour22015
ID: 40548975
Thanks that worked...
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40548981
>First I would like to mention that I am new to all this.
That's fine, but just to set expectations that doens't excuse you from asking questions clearly, with enough details to be actionable, and to work with us to get to your answer.

>As you can see this is not going to work, notice the two different DB's but they are on the same Server?
Is this a statement or a question?

Queries can execute fine on different databases as long as all objects, or at least objects not in the database that the query is executed in, are prefixed with database_name.schema_name.table_name
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now