May 18, 2012 at 6:27 am
Hi
I am trying to write a Nested Select statement with a join but keep getting the following error
Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword 'ON'.
Can you please tell me what is wrong with the query below
Select Volume
,ipkey
,NAECP1.TYPE
FROM dbo.********** as NAECP1
LEFT OUTER JOIN
(
SELECT
COUNT ([HospitalProviderSpellNumber]) as volume
,([LocalPatientID]+LEFT(CONVERT(nvarchar(8), CAST([AdmissionDate] AS datetime), 112), 8)) as IPKEY
FROM ******* as IP
WHERE Period in ('201203')
AND EpisodeNumber IN ('01')
and AdmissionMethodCode in ('21')
GROUP BY
([LocalPatientID]+LEFT(CONVERT(nvarchar(8), CAST([AdmissionDate] AS datetime), 112), 8))
)
ON NAECP1.[AEKEY]=IPKEY
Cheers
May 18, 2012 at 6:35 am
Alias name is missing
Before the on put something like as dta
Select Volume
,ipkey
,NAECP1.TYPE
FROM dbo.NWLHAandEConversionPT1 as NAECP1
LEFT OUTER JOIN
(
SELECT
COUNT ([HospitalProviderSpellNumber]) as volume
,([LocalPatientID]+LEFT(CONVERT(nvarchar(8), CAST([AdmissionDate] AS datetime), 112), 8)) as IPKEY
FROM PLDFlexYTDIP as IP
WHERE Period in ('201203')
AND EpisodeNumber IN ('01')
and AdmissionMethodCode in ('21')
GROUP BY
([LocalPatientID]+LEFT(CONVERT(nvarchar(8), CAST([AdmissionDate] AS datetime), 112), 8))
) as dta
ON NAECP1.[AEKEY]=dta.IPKEY
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply