Why am I getting an error with this SELECT statement?
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/
Who is Participating?
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
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!
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!
Hi Bruce,
That's correct. The two valid syntaxes are:
WHERE a = b
WHERE a in ('A', 'B')
Note that behind the covers, the second form actually evaluates as
WHERE (a = 'A' or a = 'B')
There is no performance penalty (or gain) using either one.
Good Luck!
Kent
That's correct. The two valid syntaxes are:
WHERE a = b
WHERE a in ('A', 'B')
Note that behind the covers, the second form actually evaluates as
WHERE (a = 'A' or a = 'B')
There is no performance penalty (or gain) using either one.
Good Luck!
Kent
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
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
Open in new window