CODEWITHSUNDEEP
t-sqlselectisnull
monkeypushbutton
monkeypushbutton

Reputation: 196

ISNULL not working in TSQL Select

I have a TSQL SELECT that can return a null. I tried using ISNULL to replace it with 0 but for some reason it is not working. The table selecting from has the following columns:

SELECT  ISNULL(penaltyPercent, 0.0) AS penaltyPercent  
FROM    dbo.Penalty  
WHERE   (penaltyDate = (SELECT     MAX(penaltyDate) AS date  
                        FROM       dbo.Penalty AS Penalty_1  
                        WHERE      (penaltyDate <= @date) AND (storeID = @storeID))) AND
        (storeID = @storeID)

When the date is before the first penalty date (when there should be 0 penalty), no result is returned. Not sure why this doesn't work. I have a work around but it is bugging me.

Here is a sample of the data being used:

storeID  penaltyDate             penaltyPercent  
182      10/1/2008 12:00:00 AM   0.020000  
182      11/1/2008 12:00:00 AM   0.040000  
182      12/1/2008 12:00:00 AM   0.070000

Upvotes: 0

Views: 8330

Answers (3)

Welbog
Welbog

Reputation: 60458

When you say "When the date is before the first penalty date", do you mean when the value of @date is less than the value returned from this query?

SELECT MIN(penaltyDate)
FROM Penalty

Because then your inner query is going to return null, and (if you're using ANSI nulls) this part will return false,

WHERE (penaltyDate = ...

Because comparisons with null always return null. So instead of selecting one row with a null value, you're selecting no rows.

Addendum:

To confirm this is the problem, change your outer query's first line to 

SELECT ISNULL(MAX(penaltyPercent),0.0) AS penaltyPercent

It will work because of what I've described above.

Upvotes: 4

eKek0
eKek0

Reputation: 23329

Do you acctually have data that it's date is before the first penaly date? If your query doesn't return records, your IsNull clause will do anything because it works against anything.

Upvotes: 0

P Daddy
P Daddy

Reputation: 29547

If @date < penaltyDate, then there is no row returned, so there is no value for ISNULL to act upon. You might want to select the penalty percent into a variable, and then select out to the result set ISNULL(@pentaltyPercent).

Upvotes: 2

Related Questions