Script Help Please!

Question

Post reply

Script Help Please!

martin.kerr 34088

SSCommitted

Points: 1752
More actions
September 26, 2011 at 10:31 am

#257914

Hi All,
I am trying to use this select statement:
select * from [MCM_DQ_PASS1B].[dbo].[View_Pass1bAccounts] where dqflag = 'M' or dqflag = null and expr2 = null and pass2 = null
But it is not selecting what i think it should (i.e. All 'M's or Nulls and Nulls and Nulls), It is giving me records that are not null. Any ideas appreciated.
Many Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

nothing is ever equal to or not equal to null; since null is undefined, nothing can match it, even another null.

you have to us the IS function instead:

SELECT

*

FROM

[MCM_DQ_PASS1B].[dbo].[View_Pass1bAccounts]

WHERE

dqflag = 'M'

OR dqflag IS NULL

AND expr2 IS NULL

AND pass2 IS NULL

or you can eliminate the nulls with an expression:

SELECT

*

FROM

[MCM_DQ_PASS1B].[dbo].[View_Pass1bAccounts]

WHERE

dqflag = 'M'

OR ISNULL(dqflag,'') =''

AND ISNULL(expr2,'') =''

AND ISNULL(pass2,'') =''

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

calvo SSChampion Points: 12930 More actions · Answer 2

martin.kerr 34088 (9/26/2011)
Hi All,
I am trying to use this select statement:
select * from [MCM_DQ_PASS1B].[dbo].[View_Pass1bAccounts] where dqflag = 'M' or dqflag = null and expr2 = null and pass2 = null
But it is not selecting what i think it should (i.e. All 'M's or Nulls and Nulls and Nulls), It is giving me records that are not null. Any ideas appreciated.
Many Thanks

To add on to what Lowell explained, keep in mind that using parenthesis to group your where clause differently will effect the result sets you get.

For example you have

Where dqflag = 'M'

or dqflag = null

and expr2 = null

and pass2 = null

You can group a null dqflag with null expr2. Depending on your expected results and business logic, this grouping of filters might be relevant.

Where dqflg = 'M'

OR (dqflag is null

AND expr2 is null)

and pass2 is null

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

martin.kerr 34088 SSCommitted Points: 1752 More actions · Answer 3

Thanks for your responses; I have tried all the SELECTs you have suggested but I am still receiving records back which contain a value in the 'Pass2' column.

Any more ideas

HowardW One Orange Chip Points: 29109 More actions · Answer 4

martin.kerr 34088 (9/27/2011)
Thanks for your responses; I have tried all the SELECTs you have suggested but I am still receiving records back which contain a value in the 'Pass2' column.
Any more ideas

Be careful with your use of OR's.

Using Lowell's suggestion:

SELECT

*

FROM

[MCM_DQ_PASS1B].[dbo].[View_Pass1bAccounts]

WHERE

dqflag = 'M'

OR dqflag IS NULL

AND expr2 IS NULL

AND pass2 IS NULL

Do you actually mean:

SELECT

*

FROM

[MCM_DQ_PASS1B].[dbo].[View_Pass1bAccounts]

WHERE

(dqflag = 'M'

OR dqflag IS NULL)

AND expr2 IS NULL

AND pass2 IS NULL

They're different as OR's are evaluated last, so you're effectively saying all of the AND conditions are true or the OR condition

martin.kerr 34088 SSCommitted Points: 1752 More actions · Answer 5

Yes that is the one i am looking for, thanks for your help

R.P.Rozema SSChampion Points: 12317 More actions · Answer 6

HowardW (9/27/2011) They're different as OR's are evaluated last, so you're effectively saying all of the AND conditions are true or the OR condition

Please read this page on Operator Precedence in T-SQL: http://msdn.microsoft.com/en-us/library/ms190276.aspx. It will show you the default order that operators are evaluated in. If in an expression you want/need to divert from that default order you'll have to use brackets ( and ) to group the expressions you want evaluated first.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

HowardW One Orange Chip Points: 29109 More actions · Answer 7

R.P.Rozema (9/27/2011)
HowardW (9/27/2011) They're different as OR's are evaluated last, so you're effectively saying all of the AND conditions are true or the OR condition
Please read this page on Operator Precedence in T-SQL: http://msdn.microsoft.com/en-us/library/ms190276.aspx. It will show you the default order that operators are evaluated in. If in an expression you want/need to divert from that default order you'll have to use brackets ( and ) to group the expressions you want evaluated first.

Was this directed at me?

This article correctly explains the LOGICAL OPERATOR PRECEDENCE:

http://msdn.microsoft.com/en-us/library/ms186992.aspx

The article you linked is only for arithmetic and bitwise operators doesn't make sense in relation to the query above.

R.P.Rozema SSChampion Points: 12317 More actions · Answer 8

Sorry, double error: posting the wrong link to the wrong person. Thanks for posting the correct link.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

HowardW One Orange Chip Points: 29109 More actions · Answer 9

HowardW

One Orange Chip

Points: 29109

September 27, 2011 at 3:25 am

#1386993

No worries!

yubo1 SSChasing Mays Points: 612 More actions · Answer 10

HI You can try to use sql-code below

select * from [MCM_DQ_PASS1B].[dbo].[View_Pass1bAccounts] where dqflag = 'M' or dqflag is null and expr2 is null and pass2 is null

The Value 'null' can't be compared with '='

must use 'is'