Solved

SQL Server 2008 - Mixing Databases in a query

Posted on 2015-01-14
4
186 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 48

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Connect to an SQL server ActiveX 10 29
SQLCMD Syntax 2 17
In sql, how to roll up multiple rows to only one row. 4 39
Amazon RDS Server load 2 17
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

820 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