Despite never having seen Cary speak, I love that he always includes Teacher in his bio. Those that present are in fact teachers, but with that inclusion, I think Cary takes it a step further.
(I plan on changing that streak this year, see Tools, Tools, Tools!)
So here's what I said, maybe it can help some newbie out there get started. Me, I encourage people to avoid the statement suggesting the CASE statement instead...mostly for it's readability (naturally I have seen that abused as well, oh well).
Here's a mock statement:
First, format the dang thing. Put some white space in there.
DECODE( my_expression, 0, 0, NULL, NULL, ( another_expresssion / my_expression ) )We're not exactly tuning, but readability goes a long way.
Now, let's look at the picture defined in the docs:
Just for simplicity's sake, I'm going to spell it out:
DECODE( POSITION_1, POSITION_2, POSITION_3, POSITION_4, POSITION_5, POSITION_6 )At least to me, that helps. Up to 4 places, this probably isn't necessary, but as it gets long (and I've seen some whoppers), it's good to be able to draw it out.
What does that mean? If POSITION_1 = POSITION_2, then use the value from POSITION_3. If POSITION_1 = POSITION_4, use the value from POSITION_5. The default will be POSITION_6.
Written out in CASE format, it would look like this:
CASEI write this up because I got the "You should be a teacher" comment at the end, which I took as a great compliment.
WHEN my_expression /* POSITION_1 */ = 0 /* POSITION_2 */
THEN 0 /* POSITION_3 */
WHEN my_expression /* POSITION_1 */ IS NULL /* POSITION_4 */
ELSE my_expression / another_expression /* POSITION_6 */