Solved

Error Message from SQL query

Posted on 2008-10-07
6
248 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

813 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now