Solved

SQL Server 2008 - Mixing Databases in a query

Posted on 2015-01-14
4
187 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 50

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

751 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