Error Message from SQL query

I am trying to use a Common Table Expression but I am getting the error 'Incorrect syntax near the keyword 'WITH'.'  I am not sure what the problem is.  I tried the adventureworks example and it gives the same error.  Attched is the SQL query that I am using.
WITH Diagnosis(ICDCode,ICDDiagnosis,ICDDiagnosis,ICDDiagnosis,ICDDiagnosis)  AS 
(
	SELECT     dbo.ICDDiagnosisVISNList.ICDCode, dbo.ICDDiagnosisVISNList.ICDDiagnosis, dbo.ICDDiagnosisVISNList.MajorDiagnosisCategory, 
						  dbo.PTFDiagnosis.Rank, dbo.PatientMovement.PatientMovementID
	FROM         dbo.PTF INNER JOIN
						  dbo.PTFDiagnosis ON dbo.PTF.PTFID = dbo.PTFDiagnosis.PTFID AND dbo.PTF.Site = dbo.PTFDiagnosis.Site INNER JOIN
						  dbo.ICDDiagnosisVISNList ON dbo.PTFDiagnosis.ICDDiagnosisID = dbo.ICDDiagnosisVISNList.ICDDiagnosisID INNER JOIN
						  dbo.PatientMovement ON dbo.PTF.PTFID = dbo.PatientMovement.PTFID AND dbo.PTF.Site = dbo.PatientMovement.Site
	WHERE     (dbo.PTF.Site = 648) OR
						  (dbo.PTF.Site = 653) OR
						  (dbo.PTF.Site = 692)
)
SELECT     dbo.PatientMovement.AdmissionMovementID, dbo.PatientMovement.Site, dbo.WardLocationList.WardLocation, 
                      CASE WHEN MONTH(dbo.PatientMovement.MovementDateTime) >= 10 THEN YEAR(dbo.PatientMovement.MovementDateTime) 
                      + 1 ELSE YEAR(dbo.PatientMovement.MovementDateTime) END AS FiscalYr, dbo.TransactionTypeVISNList.TransactionType, 
                      dbo.PatientAddress.AddressType, dbo.PatientAddress.County, dbo.PatientMovement.PTFID, t.Site, t.ICDCode, t.ICDDiagnosis, t.MajorDiagnosisCategory, t.Rank
FROM         dbo.PatientMovement INNER JOIN
                      dbo.TransactionTypeVISNList ON dbo.PatientMovement.TransactionTypeID = dbo.TransactionTypeVISNList.TransactionTypeID INNER JOIN
                      dbo.WardLocationList ON dbo.PatientMovement.WardLocationID = dbo.WardLocationList.WardLocationID AND 
                      dbo.PatientMovement.Site = dbo.WardLocationList.Site INNER JOIN
                      dbo.Patient ON dbo.PatientMovement.PatientID = dbo.Patient.PatientID AND dbo.PatientMovement.Site = dbo.Patient.Site INNER JOIN
                      dbo.PatientAddress ON dbo.Patient.PatientID = dbo.PatientAddress.PatientID AND dbo.Patient.Site = dbo.PatientAddress.Site INNER JOIN t ON dbo.PatientMovement.AdmissionMovementID=t.PatientMovementID
WHERE     (dbo.PatientMovement.Site = 653) AND (dbo.PatientAddress.AddressType = 'P') OR
                      (dbo.PatientMovement.Site = 648) AND (dbo.PatientAddress.AddressType = 'P') OR
                      (dbo.PatientMovement.Site = 692) AND (dbo.PatientAddress.AddressType = 'P')

Open in new window

LVL 7
vincem1099Asked:
Who is Participating?
 
momi_sabagCommented:
your statement is ok
are you sure that you are using sql server 2005 with compatiablity mode 90?
0
 
vincem1099Author Commented:
Not sure about the compatibility mode.  How would I check?  I do not have Admin priviledge.
0
 
Mark WillsTopic AdvisorCommented:
Well, normally have a quick look at properties for the database. Can you get privileges ? Problem is you will need to access in order to change it.

It does look OK...
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
vincem1099Author Commented:
I found that I can look at the server properties and it shows version 8.00.2050.  I think that I might have been mistaken about it being SQL Server 2005.  Should the common table expression work with this version of SQL server?
0
 
BrandonGalderisiCommented:
No.  CTE's are only in SQL 2005+
0
 
vincem1099Author Commented:
Thanks, I didn't realize the version
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.