Archive for June 20th, 2009
From Stack Overflow:
Are there non obvious differences between
The obvious differences are that
COALESCEwill return the first non-
NULLitem in its parameter list whereas
NVLonly takes two parameters and returns the first if it is not
NULL, otherwise it returns the second.
It seems that
NVLmay just be abase caseversion of
Am I missing something?
In case of two agruments of same type, these functions are basically the same: they both return first non-
However, they are implemented differently:
NVLalways evaluates both arguments
COALESCEdoesn’t evaluate anything after it finds the first non-
This behavior can be utilized.
Here’s a real world example from a restaurant management system I developed about 10 years ago.
A bill that is presented to a customer consists of the items he or she ordered.
Majority of the dishes ordered are plain menu items: something that has its price printed in the menu.
However, there are some special things which are not included to the menu: a customer may want to order something exclusive and pay for it.
The manager should estimate the cost of ingredients, time and effort to make this dish, then calculate the price and name it: everything in 30 seconds.
These things are of course come overpriced, but the customers that make such orders don’t seem to care much about money.
In the bill that appears as
special order. When a manager enters it into the computer, she should also enter the price she had calculated.
These prices then get back to the server and are stored in a table (not the one where regular menu prices are stored).
At the end of month, a report to the month’s receipts should be generated.
Here are the tables containing the data relevant to the reports. They of course are simplified, but the principle remains the same.
Read the rest of this entry »