SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select returns wrong data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stevenandler
Starting Member

USA
42 Posts

Posted - 05/21/2013 :  16:28:13  Show Profile  Reply with Quote
I have a two t-sql statements the first one works fine. The second one doesn't work.

Here they are:

DECLARE @PATNUMBER VARCHAR(25)
DECLARE @PATIENTID VARCHAR(25)
DECLARE @LMRN VARCHAR(25)
DECLARE @SSN VARCHAR(10)
DECLARE @FACILITYKEY VARCHAR(5)
DECLARE @ADMITDATE DATETIME

SET @LMRN = '123516'
SET @SSN = '999999999'
SET @FACILITYKEY = 'ADLU'

SELECT @PATNUMBER = M.PAT_NUMBER , @PATIENTID = PATIENT_ID,@ADMITDATE = ADMIT_DATE
FROM
(
SELECT MAX(PAT_NUMBER) AS PAT_NUMBER
FROM OGEN.GEN_M_PATIENT_MAST
WHERE MRN = @LMRN AND SSN = @SSN AND FACILITY_KEY = @FACILITYKEY
)M
INNER JOIN OGEN.GEN_M_PATIENT_MAST P ON M.PAT_NUMBER = P.PAT_NUMBER

PRINT @PATNUMBER
PRINT @PATIENTID
PRINT @ADMITDATE


156187
001198
Jun 10 2010 12:00AM

Results are correct




DECLARE @PATNUMBER VARCHAR(25)
DECLARE @PATIENTID VARCHAR(25)
DECLARE @LMRN VARCHAR(25)
DECLARE @SSN VARCHAR(10)
DECLARE @FACILITYKEY VARCHAR(5)
DECLARE @ADMITDATE DATETIME

SET @LMRN = '123516'
SET @SSN = '999999999'
SET @FACILITYKEY = 'ADLU'

SELECT @PATNUMBER = PAT_NUMBER , @PATIENTID = PATIENT_ID,@ADMITDATE = M.ADMIT_DATE
FROM
(
SELECT MAX(ADMIT_DATE) AS ADMIT_DATE
FROM OGEN.GEN_M_PATIENT_MAST
WHERE MRN = @LMRN AND SSN = @SSN AND FACILITY_KEY = @FACILITYKEY
)M
INNER JOIN OGEN.GEN_M_PATIENT_MAST P ON M.ADMIT_DATE = P.ADMIT_DATE


PRINT @PATNUMBER
PRINT @PATIENTID
PRINT @ADMITDATE


160058
051331
Jun 10 2010 12:00AM
results are incorrect


As a matter of fact this query is ignoring my where clause.
Any ideas?


Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 05/21/2013 :  16:33:23  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
When you do a

SELECT @foo = [colum] from table

What will happen is that you will cursor over the table assigning row after row to @foo

What's happening (I believe) is that your second query is picking a different sort order from the rows returned from your query and assigning one after another to your variables.

Try just selecting without the variables and see what comes back --

Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 05/21/2013 :  16:33:44  Show Profile  Visit chadmat's Homepage  Reply with Quote
Sample Data?

-Chad
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/21/2013 :  16:39:10  Show Profile  Visit russell's Homepage  Reply with Quote
Your where clause isn't being ignored. It is deciding the left side of your inner join. But not the right side.

All it's doing now is matching any patient with ADMIT_DATE = to whatever the max(ADMIT_DATE) is for the particular patient.

Add it to the right and you'll get the expected results.


EDIT: And the reason the first one works is you're joining on patient_number.


Edited by - russell on 05/21/2013 16:40:33
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 05/21/2013 :  16:40:16  Show Profile  Reply with Quote
Does this return more than one row?:
SELECT *
FROM
(
SELECT MAX(ADMIT_DATE) AS ADMIT_DATE
FROM OGEN.GEN_M_PATIENT_MAST
WHERE MRN = @LMRN AND SSN = @SSN AND FACILITY_KEY = @FACILITYKEY
)M
INNER JOIN OGEN.GEN_M_PATIENT_MAST P ON M.ADMIT_DATE = P.ADMIT_DATE 
Go to Top of Page

stevenandler
Starting Member

USA
42 Posts

Posted - 05/21/2013 :  16:41:29  Show Profile  Reply with Quote
SAMPLE DATA
PAT_NUMBER PATIENT_ID FACILITY_KEY ADMIT_DATE MRN SSN
156187 001198 ADLU 2010-06-10 00:00:00.000 123516 999999999
6597 123516 ADLU 2003-08-19 00:00:00.000 123516 999999999
Go to Top of Page

stevenandler
Starting Member

USA
42 Posts

Posted - 05/21/2013 :  16:47:03  Show Profile  Reply with Quote
quote:
Originally posted by Lamprey

Does this return more than one row?:
SELECT *
FROM
(
SELECT MAX(ADMIT_DATE) AS ADMIT_DATE
FROM OGEN.GEN_M_PATIENT_MAST
WHERE MRN = @LMRN AND SSN = @SSN AND FACILITY_KEY = @FACILITYKEY
)M
INNER JOIN OGEN.GEN_M_PATIENT_MAST P ON M.ADMIT_DATE = P.ADMIT_DATE 


Go to Top of Page

stevenandler
Starting Member

USA
42 Posts

Posted - 05/21/2013 :  17:03:24  Show Profile  Reply with Quote
Ok I modified the second transaction to join on PAT_NUMBER and it works. I will leave it as a self study project to figure why this is.

Thank you all for your help.


quote:
Originally posted by russell

Your where clause isn't being ignored. It is deciding the left side of your inner join. But not the right side.

All it's doing now is matching any patient with ADMIT_DATE = to whatever the max(ADMIT_DATE) is for the particular patient.

Add it to the right and you'll get the expected results.


EDIT: And the reason the first one works is you're joining on patient_number.



Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000