GPSPOW
asked on
Syntax error in SQL code..How to find errors easily
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?
Thanks
Glen
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)
Thanks
Glen
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the suggestions
'' 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.DateT
a. IF the field dbo.SchAppointments.DateTi
b. by using this construction:
WHERE (dbo.SchAppointments.DateT
AND dbo.SchAppointments.DateTi
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"