vincem1099
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')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, I didn't realize the version
ASKER