Solved

SQL Server 2008 - Mixing Databases in a query

Posted on 2015-01-14
4
185 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 47

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql server insert 12 30
Find SQL query used by application 3 17
Tsql query 6 20
transaction in asp.net, sql server 6 31
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

785 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