Where clause on a bit column

Question

Where clause on a bit column

tmmutsetse

Hall of Fame

Points: 3669
More actions
December 6, 2018 at 7:23 am

#369351

Hi Experts,
I want results based on a where clause , all columns in the where clause is bit.I want the query to return where e.g dr.Is_VIP ='1'
when i run the below it returns empty results,which is wrong.
Please help
SELECT dp.UniqueId as Email_Address,dp.UserID,dr.Btag as Tagname,dp.Brand
FROM dbo.Role as dr with (nolock)
JOIN dbo.Stats as dp with (nolock) on dr.Surr_Key = dp.Surr_Key
Where dr.Is_VIP = '1'
AND dr. Is_HighRisk ='1'
AND dr.Is_BonusRestricted ='1'
AND dr.Is_UnlimitedTXNsPerDay ='1'
Thank you

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

tmmutsetse - Thursday, December 6, 2018 7:23 AM
Hi Experts,
I want results based on a where clause , all columns in the where clause is bit.I want the query to return where e.g dr.Is_VIP ='1'
when i run the below it returns empty results,which is wrong.
Please help
SELECT dp.UniqueId as Email_Address,dp.UserID,dr.Btag as Tagname,dp.Brand
FROM dbo.Role as dr with (nolock)
JOIN dbo.Stats as dp with (nolock) on dr.Surr_Key = dp.Surr_Key
Where dr.Is_VIP = '1'
AND dr. Is_HighRisk ='1'
AND dr.Is_BonusRestricted ='1'
AND dr.Is_UnlimitedTXNsPerDay ='1'
Thank you

It is virtually impossible to offer much help here because you didn't provide any details. Are those 4 columns in your where predicates using the bit datatype? You should use 1 instead of '1' but it would still work as you would get an implicit conversion.

To get some real help please the first link in my signature.

Also, why the nolock hints? You can and will randomly get missing and/or duplicate rows as well as whole host of other fun things.

https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 2

When you have a query that isn't returning enough results a good way to diagnose it is to remove constraints on the query until you get some rows back.
So does:
SELECT dp.UniqueId as Email_Address,dp.UserID,dr.Btag as Tagname,dp.Brand FROM dbo.Role as dr with (nolock) JOIN dbo.Stats as dp with (nolock) on dr.Surr_Key = dp.Surr_Key
return any rows? If it does then does:
SELECT dp.UniqueId as Email_Address,dp.UserID,dr.Btag as Tagname,dp.Brand FROM dbo.Role as dr with (nolock) JOIN dbo.Stats as dp with (nolock) on dr.Surr_Key = dp.Surr_Key Where dr.Is_VIP = '1'
return any rows? If that does then does:
SELECT dp.UniqueId as Email_Address,dp.UserID,dr.Btag as Tagname,dp.Brand FROM dbo.Role as dr with (nolock) JOIN dbo.Stats as dp with (nolock) on dr.Surr_Key = dp.Surr_Key Where dr.Is_VIP = '1' AND dr. Is_HighRisk ='1'
return any rows, etc...

Also, you do not need quotes around the value in a bit column.
So instead of: dr.Is_VIP = '1' you just need: dr.Is_VIP = 1

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 3

tmmutsetse - Thursday, December 6, 2018 7:23 AM
Hi Experts,
I want results based on a where clause , all columns in the where clause is bit.I want the query to return where e.g dr.Is_VIP ='1'
when i run the below it returns empty results,which is wrong.
Please help
SELECT dp.UniqueId as Email_Address,dp.UserID,dr.Btag as Tagname,dp.Brand
FROM dbo.Role as dr with (nolock)
JOIN dbo.Stats as dp with (nolock) on dr.Surr_Key = dp.Surr_Key
Where dr.Is_VIP = '1'
AND dr. Is_HighRisk ='1'
AND dr.Is_BonusRestricted ='1'
AND dr.Is_UnlimitedTXNsPerDay ='1'
Thank you

Do you actually have any rows where the other 3 criteria columns contain a "1"? The way your code is written, all 4 columns in the WHERE clause must contain a "1" to be returned.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

tmmutsetse - Thursday, December 6, 2018 7:23 AM
Hi Experts,
I want results based on a where clause , all columns in the where clause is bit.I want the query to return where e.g dr.Is_VIP ='1'
when i run the below it returns empty results,which is wrong.
Please help
SELECT dp.UniqueId as Email_Address,dp.UserID,dr.Btag as Tagname,dp.Brand
FROM dbo.Role as dr with (nolock)
JOIN dbo.Stats as dp with (nolock) on dr.Surr_Key = dp.Surr_Key
Where dr.Is_VIP = '1'
AND dr. Is_HighRisk ='1'
AND dr.Is_BonusRestricted ='1'
AND dr.Is_UnlimitedTXNsPerDay ='1'
Thank you

If the columns are declared as BIT, you should not use single quotes in your WHERE clause:
Where dr.Is_VIP = 1, etc.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin