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!
Course Of The Month
5 days, 7 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Why am I getting an error with this SELECT statement?
Posted on 2016-08-05
Here's my SELECT query:
When it runs, I get this error:
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ','.
Line 14 being: ChargeGroup.clientcode='AI
'DHDC','DCC','DMH','ECH','
'LMCH','MCGH','SDP','MERCY
'TPS','TTT','00077793111',
When I run it as ChargeGroup.clientcode='AI
Thoughts/
select a.* from
(
select b.encountercode, patient.patientid, practicepatientid, practiceaccountid, client.clientcode, practice.practicecode, practicename, patientfname, patientlname, patientdob, dbo.ufn_getSSN(patient.accountid) as patientssn, patientgender, patient.accountid, accountstatuscode, accountstatuscode2, patientadminstatuscode, patientfinancialstatuscode
, ( (case when account.practiceaccountid like ('%Johnson%') then 1 else 0 end) + (case when patient.patientlname like ('%Johnson%') then 1 else 0 end) + (case when patient.patientfname like ('%Johnson%') then 1 else 0 end) + (case when account.accountid like ('%Johnson%') then 1 else 0 end) + (case when b.encountercode like ('%Johnson%') then 1 else 0 end) ) as hits from
account (nolock)
join patient (nolock) on patient.accountid = account.accountid
join practice (nolock) on account.practiceid = practice.PracticeID
join client (nolock) on practice.clientid = client.clientid
LEFT JOIN (SELECT TOP 1 ChargeGroup.AccountID, ChargeGroup.encountercode FROM ChargeGroup (nolock)
where
ChargeGroup.encountercode='Johnson'
AND
ChargeGroup.clientcode='AIRCARE') b
ON Account.AccountID=b.AccountID
where client.clientcode in('AIRCARE','ALLEN','AMG','BCMH','BBGH','CMI','DALE','DHDC','DCC','DMH','ECH','FCH','GAH','DEMO','HRMC','DSCHD','DSCHD2','HOUCOHO','LMCH','MCGH','SDP','MERCY','OVSH','PF','PCMG','RCC','SMC','SMH','TONC','TRAD','TPS','TTT','00077793111','UGH','URMC','UTH','UTS','CPAP','CPAPNew','WCH','WMC')
) a
where hits > 0
order by hits desc, patientlname asc
When it runs, I get this error:
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ','.
Line 14 being: ChargeGroup.clientcode='AI
'DHDC','DCC','DMH','ECH','
'LMCH','MCGH','SDP','MERCY
'TPS','TTT','00077793111',
When I run it as ChargeGroup.clientcode='AI
Thoughts/
[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-
2
5 Comments
Active today
Perhaps line 14 should use the IN() predicate?
The given syntax
ChargeGroup.clientcode='AI
is incorrect.
Kent
The given syntax
ChargeGroup.clientcode='AI
is incorrect.
Kent
Active today
So, Kent, I tried this:
...and I got the same error. Did I do it correctly or do you see something else...?
Let me know.
Thanks!
select a.* from
(
select b.encountercode, patient.patientid, practicepatientid, practiceaccountid, client.clientcode, practice.practicecode, practicename, patientfname, patientlname, patientdob, dbo.ufn_getSSN(patient.accountid) as patientssn, patientgender, patient.accountid, accountstatuscode, accountstatuscode2, patientadminstatuscode, patientfinancialstatuscode
, ( (case when account.practiceaccountid like ('%Johnson%') then 1 else 0 end) + (case when patient.patientlname like ('%Johnson%') then 1 else 0 end) + (case when patient.patientfname like ('%Johnson%') then 1 else 0 end) + (case when account.accountid like ('%Johnson%') then 1 else 0 end) + (case when b.encountercode like ('%Johnson%') then 1 else 0 end) ) as hits from
account (nolock)
join patient (nolock) on patient.accountid = account.accountid
join practice (nolock) on account.practiceid = practice.PracticeID
join client (nolock) on practice.clientid = client.clientid
LEFT JOIN (SELECT TOP 1 ChargeGroup.AccountID, ChargeGroup.encountercode FROM ChargeGroup (nolock)
where
ChargeGroup.encountercode='Johnson'
AND
ChargeGroup.clientcode IN ('AIRCARE','ALLEN','AMG','BCMH','BBGH','CMI','DALE','DHDC','DCC','DMH','ECH','FCH','GAH','DEMO','HRMC','DSCHD','DSCHD2','HOUCOHO','LMCH','MCGH','SDP','MERCY','OVSH','PF','PCMG','RCC','SMC','SMH','TONC','TRAD','TPS','TTT','00077793111','UGH','URMC','UTH','UTS','CPAP','CPAPNew','WCH','WMC')) b
ON Account.AccountID=b.AccountID
where client.clientcode in('AIRCARE','ALLEN','AMG','BCMH','BBGH','CMI','DALE','DHDC','DCC','DMH','ECH','FCH','GAH','DEMO','HRMC','DSCHD','DSCHD2','HOUCOHO','LMCH','MCGH','SDP','MERCY','OVSH','PF','PCMG','RCC','SMC','SMH','TONC','TRAD','TPS','TTT','00077793111','UGH','URMC','UTH','UTS','CPAP','CPAPNew','WCH','WMC')
) a
where hits > 0
order by hits desc, patientlname asc
...and I got the same error. Did I do it correctly or do you see something else...?
Let me know.
Thanks!
Active today
Hi Bruce,
I've reformatted the original query to make it easier (for me) to read and changed the filter to use IN().
See if this one works.
Kent
I've reformatted the original query to make it easier (for me) to read and changed the filter to use IN().
See if this one works.
Kent
select a.* from
(
select
b.encountercode,
patient.patientid,
practicepatientid,
practiceaccountid,
client.clientcode,
practice.practicecode,
practicename,
patientfname,
patientlname,
patientdob,
dbo.ufn_getSSN(patient.accountid) as patientssn,
patientgender,
patient.accountid,
accountstatuscode,
accountstatuscode2,
patientadminstatuscode,
patientfinancialstatuscode,
(
(case when account.practiceaccountid like ('%Johnson%') then 1 else 0 end)
+ (case when patient.patientlname like ('%Johnson%') then 1 else 0 end)
+ (case when patient.patientfname like ('%Johnson%') then 1 else 0 end)
+ (case when account.accountid like ('%Johnson%') then 1 else 0 end)
+ (case when b.encountercode like ('%Johnson%') then 1 else 0 end)
) as hits
from account (nolock)
join patient (nolock) on patient.accountid = account.accountid
join practice (nolock) on account.practiceid = practice.PracticeID
join client (nolock) on practice.clientid = client.clientid
LEFT JOIN (SELECT TOP 1 ChargeGroup.AccountID, ChargeGroup.encountercode
FROM ChargeGroup (nolock)
where ChargeGroup.encountercode='Johnson'
AND ChargeGroup.clientcode in('AIRCARE','ALLEN','AMG','BCMH','BBGH','CMI','DALE','DHDC','DCC','DMH','ECH','FCH','GAH','DEMO','HRMC','DSCHD','DSCHD2','HOUCOHO','LMCH','MCGH','SDP','MERCY','OVSH','PF','PCMG','RCC','SMC','SMH','TONC','TRAD','TPS','TTT','00077793111','UGH','URMC','UTH','UTS','CPAP','CPAPNew','WCH','WMC')
) b
ON Account.AccountID=b.AccountID
where client.clientcode in('AIRCARE','ALLEN','AMG','BCMH','BBGH','CMI','DALE','DHDC','DCC','DMH','ECH','FCH','GAH','DEMO','HRMC','DSCHD','DSCHD2','HOUCOHO','LMCH','MCGH','SDP','MERCY','OVSH','PF','PCMG','RCC','SMC','SMH','TONC','TRAD','TPS','TTT','00077793111','UGH','URMC','UTH','UTS','CPAP','CPAPNew','WCH','WMC')
) a
where hits > 0
order by hits desc, patientlname asc
Active today
Kent! That did it! Thank you very much! My workplace has just given me my cape and my superhero soundtrack, so rock on!
Just to explain back to you what the problem was and why your fix worked...
As long as you're looking for a single value, the equal sign works just fine. But when you expand the number of values that you're looking for, you now have to incorporate the "IN" operator in order for the SELECT to fire correctly, yes?
Thanks again!
Just to explain back to you what the problem was and why your fix worked...
As long as you're looking for a single value, the equal sign works just fine. But when you expand the number of values that you're looking for, you now have to incorporate the "IN" operator in order for the SELECT to fire correctly, yes?
Thanks again!
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| SQL Server 2012 to SQL Server 2016 | 24 | 55 | |
| SQL Server sp_executesql / Change Tracking Version() results into variable | 4 | 30 | |
| Access PS SQLSERVER from powershell | 1 | 30 | |
| SQL State HYT00. Timeout expired proplem | 8 | 44 |
Introduction
In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses
Course of the Month5 days, 7 hours left to enroll
734 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.