**SQL Apprentice Question**

This is taken from a large but not very complex SQL statement.

,SUM (CASE ELUBECOUPONS.TICKETID WHEN NULL THEN 0 ELSE 1 END)

when I execute the statement with the one above the answer is 0 rows.

If I change that statement to:

,SUM (CASE WHEN ELUBECOUPONS.TICKETID IS NULL THEN 0 ELSE 1 END)

the answer is 624,510 rows

Same database, I only changed this one result column.

I clearly don't understand something about the case statement.

**Celko Answers**

What is the basic rule about NULLs? They cannot be compared to

anything, even eaqch other! You mean to use the other form of CASE

expression:

SUM (CASE WHEN ElubeCoupons.ticket_nbr IS NULL THEN 0 ELSE 1 END)

Since SUM() drops out NULLs, you could use this with numeric ticket

numbers.

SUM (SIGN (ElubeCoupons.ticket_nbr))

Little data modeling thing; a "_nbr" implies a sequence or other

generating rule for the numeric or pseudo-numeric value. "_id" just

says that the value is unique. That is why we talk about ticket

numbers and not ticket identifiers.

## No comments:

Post a Comment