CODEWITHSUNDEEP
sqlt-sql
Ryan Burnham
Ryan Burnham

Reputation: 2669

Odd SQL Results

So i have the following query

Select id, [First], [Last] , [Business] as contactbusiness, (Case When ([Business] != '' or [Business] is not null) 
        Then [Business] Else 'No Phone Number' END) from contacts

The results look like

id  First   Last    contactbusiness (No column name)
2   John    Smith       
3   Sarah   Jane    0411 111 222    0411 111 222
6   John    Smith   0411 111 111    0411 111 111
8                   NULL            No Phone Number
11  Ryan    B       08 9999 9999    08 9999 9999
14  David   F       NULL            No Phone Number

I'd expect record 2 to also show No Phone Number

If i change the "[Business] is not null" to [Business] != null then i get the correct results 

id  First   Last    contactbusiness (No column name)
2   John    Smith                   No Phone Number
3   Sarah   Jane    0411 111 222    0411 111 222
6   John    Smith   0411 111 111    0411 111 111
8                   NULL            No Phone Number
11  Ryan    B       08 9999 9999    08 9999 9999
14  David   F       NULL            No Phone Number

Normally you need to use is not null rather than != null. whats going on here?

Upvotes: 2

Views: 86

Answers (3)

Manatherin
Manatherin

Reputation: 4187

Your logic, as mentioned, is off. You could use 

SELECT id, [First], [Last] , [Business] AS contactbusiness, 
    COALESCE(NULLIF([Business], ''), 'No Phone Number')
FROM contacts

for SQL that's a bit more condensed

The reason for why If i change the "[Business] is not null" to [Business] != null then i get the correct results works is that [Business] != null is always false.

As you mentioned, SQL checks null values with the is operator and equality comparisons of nulls always fail (experiment with select 'hi' where (null = null) and select 'hi' where (null != null)). The use of your OR statment and short circuiting meant that:

  1. When the phone number is there the first condition [Business] != '' is true. So the OR statment is true and the phone number is used
  2. When the first condition fails, the 2nd condition [Business] != null is also false. So the OR statement is false and the "No Phone Number" is shown

Upvotes: 2

dugas
dugas

Reputation: 12433

Your logic is wrong. You need to use an AND when checking the two negatives:

Case When ([Business] != '' AND [Business] is not null

Upvotes: 2

Ben Lee
Ben Lee

Reputation: 53319

You need to use AND, not OR in your conditional:

[Business] != '' and [Business] is not null

It's confusing because you are using negatives. I'd just flip the whole condition and use positives (in which case you would use OR):

(Case When ([Business] == '' or [Business] is null) 
    Then 'No Phone Number' Else [Business] END)

Upvotes: 3

Related Questions