Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server 2008 - Mixing Databases in a query

Posted on 2015-01-14
4
Medium Priority
?
195 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 52

Accepted Solution

by:
Vitor Montalvão earned 2000 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 66

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

782 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