Go Premium for a chance to win a PS4. Enter to Win

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

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

3 Solutions
your statement is ok
are you sure that you are using sql server 2005 with compatiablity mode 90?
vincem1099Author Commented:
Not sure about the compatibility mode.  How would I check?  I do not have Admin priviledge.
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...
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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?
No.  CTE's are only in SQL 2005+
vincem1099Author Commented:
Thanks, I didn't realize the version

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now