Solved

Error Message from SQL query

Posted on 2008-10-07
6
250 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:vincem1099
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 167 total points
ID: 22661144
your statement is ok
are you sure that you are using sql server 2005 with compatiablity mode 90?
0
 
LVL 7

Author Comment

by:vincem1099
ID: 22661172
Not sure about the compatibility mode.  How would I check?  I do not have Admin priviledge.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 166 total points
ID: 22661209
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Author Comment

by:vincem1099
ID: 22661253
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
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 167 total points
ID: 22661681
No.  CTE's are only in SQL 2005+
0
 
LVL 7

Author Closing Comment

by:vincem1099
ID: 31503912
Thanks, I didn't realize the version
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question