Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select returns wrong data

Author  Topic 

stevenandler
Starting Member

42 Posts

Posted - 2013-05-21 : 16:28:13
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
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2013-05-21 : 16:33:23
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

1974 Posts

Posted - 2013-05-21 : 16:33:44
Sample Data?

-Chad
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-05-21 : 16:39:10
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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-05-21 : 16:40:16
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

42 Posts

Posted - 2013-05-21 : 16:41:29
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

42 Posts

Posted - 2013-05-21 : 16:47:03
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

42 Posts

Posted - 2013-05-21 : 17:03:24
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
   

- Advertisement -