?
Solved

Syntax error in SQL code..How to find errors easily

Posted on 2014-04-08
3
Medium Priority
?
358 Views
Last Modified: 2014-04-26
I have the following Select statement and when I execute I get a syntax error:  Error in list of function arguments:  'AS' not recognized.

I have reviewed the code and cannot find it.

Can someone look at it?  And is there an easy way to detect errors in a SQL statement?

SELECT     TOP (100) PERCENT 

dbo.AbstractData.UnitNumber as  [Patient ID], 
COALESCE (dbo.AbstractData.Sex, 'O') as Gender, 
CONVERT(char, dbo.AbstractData.BirthDateTime, 110) AS [Date of Birth], 
dbo.NHSN_DESCR_ICD9.[NHSN OPER] AS [NHSN Procedure Code], CONVERT(varchar, dbo.SchOrPatCases.OperationDateTime, 110) AS [Date of Procedure], 
CASE LEFT(dbo.AbstractData.PatientClass, 1) WHEN 'I' THEN 'N' ELSE 'Y' END AS Outpatient, DATEDIFF(N, dbo.SchPatTimesOp.Op5DateTime, dbo.SchPatTimesOp.Op6DateTime) 
                      / 60 AS [Duration hours], 
DATEDIFF(N, dbo.SchPatTimesOp.Op5DateTime, dbo.SchPatTimesOp.Op6DateTime) % 60 AS [Duration minutes], 
CASE dbo.SchOrPatCaseActualOps.OpWound WHEN 'CL' THEN 'C' WHEN 'CC' THEN 'CC' WHEN 'CO' THEN 'CO' WHEN 'D' THEN 'D' ELSE 'U' END AS [Wound Class], 
CASE SUBSTRING(dbo.SchOrPatCasePostOpDetails.AnesthesiaPreOpAsaID, 3, 1) 
                      WHEN '1' THEN '1' WHEN '2' THEN '2' WHEN '3' THEN '3' WHEN '4' THEN '4' WHEN '5' THEN '5' ELSE SUBSTRING(dbo.SchOrPatCasePostOpDetails.AnesthesiaPreOpAsaID, 4, 1) 
                      END AS [ASA Score], 
CASE COALESCE (CHARINDEX('SCOP', dbo.SchOrPatCaseActualOps.OpDescription), 0) WHEN 0 THEN 'N' ELSE 'Y' END AS SCOPE, 
dbo.SchOrPatCaseActualOps.OpSurgeonID AS SurgeonCode, 
CASE LEFT(dbo.AbstractData.AdmitSourceID, 1) WHEN 'E' THEN 'Y' ELSE 'N' END AS Emergency, '' AS [Closure Tech], 
CASE dbo.SchOrPatCasePostOpDetails.AnestheticTypeID WHEN 'GEN' THEN 'Y' ELSE 'N' END AS [General ansthesia], CASE COALESCE (dbo.AbsQueriesMult.Response, 'N') 
                      WHEN 'N' THEN 'N' ELSE 'Y' END AS Trauma, 
CASE WHEN dbo.NHSN_DESCR_ICD9.SPINAL IS NULL 
                      THEN 'N' ELSE dbo.NHSN_DESCR_ICD9.SPINAL END AS [Spinal Level], 
CASE WHEN dbo.NHSN_DESCR_ICD9.HPRO IS NULL 
                      THEN '' ELSE dbo.NHSN_DESCR_ICD9.HPRO END AS [Type of HPRO], 
CASE WHEN dbo.NHSN_DESCR_ICD9.KPRO IS NULL 
                      THEN '' ELSE dbo.NHSN_DESCR_ICD9.KPRO END AS [Type of KPRO], 
CONVERT(int, dbo.AdmVisitOrders.HeightInFeet) AS [Height in Feet], CONVERT(int, 
                      dbo.AdmVisitOrders.HeightInInches) AS [Height in Inches], CAST(dbo.AdmVisitOrders.HeightInCentimeters / 100.00 AS Decimal(6, 3)) AS [Height in Meters], 
CAST(dbo.AdmVisitOrders.WeightInPounds AS Decimal(5, 2)) AS [Weight in Pounds], CAST(dbo.AdmVisitOrders.WeightInKilograms AS Decimal(5, 2)) AS [Weight in Kilograms], 
 '' AS [Duration of Labor], 
'' AS EMPTY2, 
CASE COALESCE (dbo.AbsDrgDiagnoses.Diagnosis, 'N') WHEN 'N' THEN 'N' ELSE 'Y' END AS [Diabetes Mellitus], 
CASE WHEN dbo.NHSN_DESCR_ICD9.APPROACH IS NULL THEN '' ELSE dbo.NHSN_DESCR_ICD9.APPROACH END AS [Type of Approach], 

CASE WHEN dbo.NHSN_DESCR_ICD9.HPRO_KPRO IS NULL THEN '' ELSE dbo.NHSN_DESCR_ICD9.HPRO_KPRO END AS [Type of HPRO or KPRO], 
 CASE WHEN dbo.NHSN_DESCR_ICD9.JOINT IS NULL THEN '' ELSE dbo.NHSN_DESCR_ICD9.JOINT END AS [Type TOTAL Joint Repl], 
 CASE WHEN dbo.NHSN_DESCR_ICD9.HEMI IS NULL THEN '' ELSE dbo.NHSN_DESCR_ICD9.HEMI END AS [Type of HEMI Joint Repl], 
 CASE WHEN dbo.NHSN_DESCR_ICD9.RESURFACE IS NULL THEN '' ELSE dbo.NHSN_DESCR_ICD9.RESURFACE END AS Resurfacing, 
'' AS Comments,
                       '' AS Custom1, '' AS Custom2, '' AS Custom3, '' AS Custom4, '' AS Custom5, '' AS Custom6, '' AS Custom7, '' AS Custom8, '' AS Custom9, 
                      '' AS Custom10, '' AS Custom11, '' AS Custom12, '' AS Custom13, '' AS Custom14, '' AS Empty4, '' AS Empty5, 
'' AS [ICD-9-CM] as ProcCode, 
                      '' AS [Medicare Beneficiary], 
'' AS Custom15, '' AS Custom16, '' AS Custom17, '' AS Custom18, '' AS Custom19, '' AS Custom20, '' AS Custom21, 
                      '' AS Custom22, '' AS Custom23, '' AS Custom24, '' AS Custom25, '' AS Custom26, '' AS Custom27, '' AS Custom28, '' AS Custom29, '' AS Custom30, 
                      '' AS Custom31, '' AS Custom32, '' AS Custom33, '' AS Custom34, '' AS Custom35, '' AS Custom36, '' AS Custom37, '' AS Custom38, '' AS Custom39, 
                      '' AS Custom40, '' AS Custom41, '' AS Custom42, '' AS Custom43, '' AS Custom44, '' AS Custom45, '' AS Custom46, '' AS Custom47, '' AS Custom48, 
                      '' AS Custom49, '' AS Custom50, 
dbo.NHSN_DESCR_ICD9.NHSN AS EXPR2, dbo.SchAppointments.DateTime, 
                      dbo.NHSN_DESCR_ICD9.ICD9CM AS EXPR1
FROM         dbo.AdmVisitOrders RIGHT OUTER JOIN
                      dbo.SchAppointmentOrOperations RIGHT OUTER JOIN
                      dbo.SchAppointments ON dbo.SchAppointmentOrOperations.AppointmentID = dbo.SchAppointments.AppointmentID LEFT OUTER JOIN
                      dbo.SchOrPatCases LEFT OUTER JOIN
                      dbo.SchPatTimesOp ON dbo.SchOrPatCases.PatientCaseID = dbo.SchPatTimesOp.CaseID LEFT OUTER JOIN
                      dbo.SchOrPatCasePostOpDetails ON dbo.SchOrPatCases.PatientCaseID = dbo.SchOrPatCasePostOpDetails.PatientCaseID LEFT OUTER JOIN
                      dbo.SchOrPatCaseActualOps ON dbo.SchOrPatCases.PatientCaseID = dbo.SchOrPatCaseActualOps.PatientCaseID ON 
                      dbo.SchAppointments.VisitID = dbo.SchOrPatCases.VisitID ON dbo.AdmVisitOrders.VisitID = dbo.SchAppointments.VisitID LEFT OUTER JOIN
                      dbo.AbstractData INNER JOIN
                      dbo.AbsOperationProcedures ON dbo.AbstractData.VisitID = dbo.AbsOperationProcedures.VisitID INNER JOIN
                      dbo.NHSN_DESCR_ICD9 ON dbo.AbsOperationProcedures.ProcedureCode = dbo.NHSN_DESCR_ICD9.ICD9CM LEFT OUTER JOIN
                      dbo.AbsQueriesMult ON dbo.AbsOperationProcedures.VisitID = dbo.AbsQueriesMult.VisitID AND 
                      dbo.AbsQueriesMult.GroupResponse = 'Ed.TRAMEC1' LEFT OUTER JOIN
                      dbo.AbsDrgDiagnoses ON dbo.AbstractData.VisitID = dbo.AbsDrgDiagnoses.VisitID ON dbo.SchAppointments.VisitID = dbo.AbstractData.VisitID
WHERE     (dbo.SchAppointments.DateTime BETWEEN '2014-02-01' AND '2014-02-28') AND (dbo.AbsOperationProcedures.OperationSeqID = 1) AND 
                      (dbo.AbsDrgDiagnoses.DiagnosisSeqID = 1) AND (dbo.AbsOperationProcedures.ProcedureSeqID = 1) AND 
                      (dbo.NHSN_DESCR_ICD9.[NHSN OPER] NOT IN ('NU', 'NO', 'OTH', 'INV')) AND (dbo.SchAppointmentOrOperations.SurgeonID IS NOT NULL)

Open in new window


Thanks

Glen
0
Comment
Question by:GPSPOW
[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
3 Comments
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 2000 total points
ID: 39987308
Take a look at line #40

'' AS [ICD-9-CM] as ProcCode,
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39987413
yes, assuming that row should be

'' AS [ICD-9-CM], '' as ProcCode,


just as an additional observation, please re-consider how you are using BETWEEN for that date range.

WHERE (dbo.SchAppointments.DateTime BETWEEN '2014-02-01' AND '2014-02-28')

a. IF the field dbo.SchAppointments.DateTime does actually hold times other than 00:00:00 then your existing date range will miss appointments at 2014-02-28 09:00, 2014-02-28 09:30, ... 2014-02-28 17:00 etc.

b. by using this construction:
WHERE (dbo.SchAppointments.DateTime >= '20140201'
       AND dbo.SchAppointments.DateTime < '20140301')
you will get all appointments on the last day of February without having to know if its a leap year (i.e. you avoid having to figure out the last day of a month) and without having to worry about time withoin a day

c. the most reliable date literal in SQL Server is YYYYMMDD

for more on between see: "Beware of Between"
0
 

Author Comment

by:GPSPOW
ID: 40024943
Thanks for the suggestions
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

765 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