Non-Boolean Error on Basic Query

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

SQL Server 2008 Forums

Transact-SQL (2008)

Non-Boolean Error on Basic Query

Author

Topic

cant2ny
Starting Member

20 Posts

Posted - 2011-08-17 : 11:14:55

Hey everyone, can someone help me out with what I'm missing or what I'm doing wrong? The error message I'm getting is "An expression of non-boolean type specified in a context where a condition is expected, near ')'. The part it's occurring at is highlighted in red below; thanks in advance for any help.

select
ml.mstr_list_item_desc,
LV.LastVisit,
p.first_name,
p.last_name,
p.address_line_1,
p.city,
p.state,
p.zip,
p.home_phone,
p.date_of_birth
from person p
left Join (
Select Person_Id, LastVisit = Max(enc_timestamp)
From patient_encounter
Group By Person_Id )LV
On P.Person_Id = LV.Person_Id
inner join person_ud pu
on p.person_id = pu.person_id
inner join mstr_lists ml
on pu.ud_demo5_id = ml.mstr_list_item_id
Where
p.expired_ind = 'N'
AND (ml.mstr_list_item_id != '0F38A2FB-37BB-421E-91B0-FAB4F9A1EA8F'
AND ml.mstr_list_item_id != 'A6EFDD43-B459-480C-92EC-C63F81AF1FAE')

AND
(
LV.LastVisit < '2009-09-30'
OR
(select p.person_id
from person p
where NOT EXISTS
(select person_id from patient_encounter lv
where lv.person_id = p.person_id)
AND p.create_timestamp < '2009-09-30')
)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-08-17 : 11:18:43

SQL is reading this part
select p.person_id
from person p
where NOT EXISTS
(select person_id from patient_encounter lv
where lv.person_id = p.person_id)
AND p.create_timestamp < '2009-09-30')
as simply "select p.person_id" and is expecting a vaule to compare it to

Jim

Everyday I learn something that somebody else already knew

cant2ny
Starting Member

20 Posts

Posted - 2011-08-17 : 11:54:17

Sorry if this is a basic question, but is there anyway to get the subquery to work in the OR clause?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-08-17 : 12:00:03

Yes, but I am not sure what you're trying to accomplish with it. Do you need to compare that p.person_id to some other value, or do you just need to know that it doesn't exist in the patient_encounter table?

Jim

Everyday I learn something that somebody else already knew

cant2ny
Starting Member

20 Posts

Posted - 2011-08-17 : 12:01:15

Just that it doesn't exist in the patient_encounter table.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-08-17 : 12:30:26

Well here's a first crack at it

from person p
left Join (
				Select Person_Id, LastVisit = Max(enc_timestamp) 
				From patient_encounter
				Group By Person_Id 
           )LV 
On P.Person_Id = LV.Person_Id
inner join person_ud pu
on p.person_id = pu.person_id
inner join mstr_lists ml
on pu.ud_demo5_id = ml.mstr_list_item_id
Where 
p.expired_ind = 'N'
AND  (ml.mstr_list_item_id NOT IN ('0F38A2FB-37BB-421E-91B0-FAB4F9A1EA8F' ,'A6EFDD43-B459-480C-92EC-C63F81AF1FAE') 
AND (lv.Person_id is null or lv.lastVisit <'2009-09-30')

"lv.Person_id is null" means that p.person_id doesn't exist in the lv table

Jim

Everyday I learn something that somebody else already knew

cant2ny
Starting Member

20 Posts

Posted - 2011-08-17 : 12:46:49

Thanks Jim for your help,

That was my original query and I changed it to the NOT EXISTS due to my test data not showing. Ended up being a problem with my test data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources