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
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
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
WHERE [FILE_ID] = @fileId and HST_FG = 1
Then the query would be:
FROM CLAIM_HISTORY ch WITH(NOLOCK)
INNER JOIN #CLAIM_TEMP ct on ch.CLM_KY = ct.CLM_KY
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..