need help with my sql query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

need help with my sql query

Author

Topic

Pasi
Posting Yak Master

166 Posts

Posted - 2014-04-02 : 16:10:07

Hi ,

I am getting error" Column 'person.practice_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." for the query below, can someone pls help me to restructure this?

I am trying to select only last names that does not contain 'test'.
I don't think I need the "where" clause here but not sure how to do it with the "CASE".

Thanks a lot!!!

code:
select distinct
count(CASE when p.last_name = 'test' then NULL else p.last_name end)
person_id, p.practice_id, enc_timestamp,* from person p
inner join patient_encounter pe on pe.person_id =p.person_id
inner join provider_mstr pm on pm.provider_id =pe.rendering_provider_id
where p.last_name not in (select last_name from person where last_name like '%test%')

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-02 : 16:51:45

I didn't follow the logic, but based on your statement that you are trying to select only last names that does not contain 'test', wouldn't this be sufficient?

SELECT
	last_name,
	practice_id,
	col3, col4, etc
FROM
	person
WHERE
	last_name NOT LIKE '%test%'

Pasi
Posting Yak Master

166 Posts

Posted - 2014-04-02 : 16:53:54

Thanks James but I wanted to use CASE but not sure how to do it?
Pasi

Pasi
Posting Yak Master

166 Posts

Posted - 2014-04-02 : 16:56:18

it looks like when you CASE it takes longer to retrieve data than regular where statement?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources