Link to home
Start Free TrialLog in
Avatar of vincem1099
vincem1099Flag for United States of America

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of vincem1099

ASKER

Not sure about the compatibility mode.  How would I check?  I do not have Admin priviledge.
SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks, I didn't realize the version