Help with Nested Select statement with join

• bharatgi

  SSChasing Mays

  Points: 635

  More actions

  May 18, 2012 at 6:27 am

  #256652

  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

• Gullimeel

  Hall of Fame

  Points: 3761

  More actions

  May 18, 2012 at 6:35 am

  #1489616

  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]