An Expression of Non-Boolean Type error

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 2005 Forums

Transact-SQL (2005)

An Expression of Non-Boolean Type error

Author

Topic

krista80
Starting Member

29 Posts

Posted - 2011-01-28 : 10:19:22

Hi. I get this error "an expression of non-boolean type specified in a context where a condition is expected" when I try to run this query code:

SELECT r.FirstNumSet, r.SecondNumSet, r.ThirdNumSet, r.FourthNumSet, r.FifthNumSet
FROM [TestDatabase].[dbo].[Results] as r
WHERE r.FirstNumSet, r.SecondNumSet, r.ThirdNumSet, r.FourthNumSet, r.FifthNumSet <> (SELECT i.FirstNumSet, i.SecondNumSet, i.ThirdNumSet, i.FourthNumSet, i.FifthNumSet
FROM [TestDatabase].[dbo].[Information] as i)

Can anyone help me out with this? Thanks!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-28 : 10:34:38

There's a few things wrong with this query. Could you describe in words what you are trying to accomplish exactly?

Jim

Everyday I learn something that somebody else already knew

krista80
Starting Member

29 Posts

Posted - 2011-01-28 : 10:53:53

I am trying to see the numbers from the Results table that don't match the numbers from the Information table. The numbers are from a CSV file and are broken up into individual sets (like an account number). So what exactly I want to do is, I want to take the lines of numbers from the csv file and validate them by checking to see if they are in the Information table. If there are no matches, then I want the unmatched number lines to be selected. Those number lines will also be inserted into the Results table. The way I have it now is I save the csv information (already broken up) into the results table without validating them. I am trying right now to get the syntax right for the displaying of the unmatched numbers. But I need to have ALL the sets of numbers matched to have a valid number.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-28 : 10:57:09

How about this then.

SELECT r.FirstNumSet, r.SecondNumSet, r.ThirdNumSet, r.FourthNumSet, r.FifthNumSet
FROM [TestDatabase].[dbo].[Results] as r
WHERE NOT EXISTS
		( select 1 
           from [TestDatabase].[dbo].[Information] as i
		  where
				r.FirstNumSet  = i.FirstNumSet
			and r.SecondNumSet = i.SecondNumSet
			and r.ThirdNumSet  = i.ThirdNumSet
			and r.FourthNumSet = i.FourthNumSet
			and r.FifthNumSet  = i.FifthNumSet
		)

Jim

Everyday I learn something that somebody else already knew

krista80
Starting Member

29 Posts

Posted - 2011-01-28 : 11:02:35

That works! Thanks very much!

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-01-28 : 11:03:50

Your syntax is wrong.

ANSI sql would allow multiple columns in an IN or NOT IN.
(ie You could replace <> with NOT IN.)

T-sql only allows one column with an IN so you are left with Jim's suggestion or


--  2
SELECT r.FirstNumSet, r.SecondNumSet, r.ThirdNumSet, r.FourthNumSet, r.FifthNumSet
FROM [TestDatabase].[dbo].[Results] as r
	LEFT JOIN [TestDatabase].[dbo].[Information] as i
		ON r.FirstNumSet = i.FirstNumSet
			AND r.SecondNumSet = i.SecondNumSet
			AND r.ThirdNumSet = i.ThirdNumSet
			AND r.FourthNumSet = i.FourthNumSet
			AND r.FifthNumSet  = i.FifthNumSet
WHERE i.FirstNumSet IS NULL

-- or 3
SELECT FirstNumSet, SecondNumSet, ThirdNumSet, FourthNumSet, FifthNumSet
FROM [TestDatabase].[dbo].[Results]
EXCEPT
SELECT FirstNumSet, SecondNumSet, ThirdNumSet, FourthNumSet, FifthNumSet
FROM [TestDatabase].[dbo].[[Information]

Look at the query plans for all three queries and see which one is best for your data.

krista80
Starting Member

29 Posts

Posted - 2011-01-28 : 12:11:06

They all helped. So thank you very much, jimf and Ifor.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources