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.
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 156187001198 Jun 10 2010 12:00AMResults 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 )MINNER JOIN OGEN.GEN_M_PATIENT_MAST P ON M.ADMIT_DATE = P.ADMIT_DATE PRINT @PATNUMBER PRINT @PATIENTID PRINT @ADMITDATE160058051331 Jun 10 2010 12:00AMresults are incorrectAs 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 aSELECT @foo = [colum] from table What will happen is that you will cursor over the table assigning row after row to @fooWhat'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 CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2013-05-21 : 16:33:44
|
Sample Data?-Chad |
|
|
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. |
|
|
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_DATEFROM OGEN.GEN_M_PATIENT_MASTWHERE MRN = @LMRN AND SSN = @SSN AND FACILITY_KEY = @FACILITYKEY)MINNER JOIN OGEN.GEN_M_PATIENT_MAST P ON M.ADMIT_DATE = P.ADMIT_DATE |
|
|
stevenandler
Starting Member
42 Posts |
Posted - 2013-05-21 : 16:41:29
|
SAMPLE DATAPAT_NUMBER PATIENT_ID FACILITY_KEY ADMIT_DATE MRN SSN156187 001198 ADLU 2010-06-10 00:00:00.000 123516 9999999996597 123516 ADLU 2003-08-19 00:00:00.000 123516 999999999 |
|
|
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_DATEFROM OGEN.GEN_M_PATIENT_MASTWHERE MRN = @LMRN AND SSN = @SSN AND FACILITY_KEY = @FACILITYKEY)MINNER JOIN OGEN.GEN_M_PATIENT_MAST P ON M.ADMIT_DATE = P.ADMIT_DATE
|
|
|
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.
|
|
|
|
|
|