0

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:

  • storeID --> int
  • penaltyPercent --> decimal(9,2)
  • penaltyDate --> dateTime
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
Improve this question
2
  • format your answer, it is clearly in that way Commented Mar 24, 2009 at 17:41
  • Thanks everyone, from a relative SQL noob. Commented Mar 24, 2009 at 18:11

3 Answers 3

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

The inner query doesn't return null, it returns an empty result set.
@P Daddy: I tested it out with SQL Server 2000. To my surprise, it does actually return null in this situation. I expected the inner query to return no records, but it returns null. The outer query returns no records, though.
2

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).

Improve this answer

Comments

0

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.