SQL COALESCE

I find it difficult to use this function. Its use is really limited.

The function coalesce(esp1,esp2....) is actually an expansion of NVL(esp, replacement) in some language.

Both function checks for NULL. NVL() is translated to if "esp" is null then use "replacement" otherwise use "esp". Coalesce() extends the "if" to many evaluations and has no "replacement" unless you specifically define one expression that is always true.

What then is the use of coalesce()? Basically, it allows you to return a value from a list of expressions in the order that you defines it. It will pick up the value of the first expression that return a NOTNULL.

In a more English term, the function allows you to choose the first field from a list of fields that is not empty.

In practice, I can't find a real use of it. At least not for the field of work I am in. However, I can imagine a practical use. For example, I have a list of prices for a given part. The prices are select in the order of preferences refurbished, local, regional and worldwide. If the higher preference price is not available then the next lower price preference is used. Usually, the different prices are not compiled. Rather, it is taken from joins that refers to different tables which are updated independently. Thus it is normally unknown if a particular preference has a value. Obviously, there must be another coalesce to indicate where the source of the price is from.

Another example is as follows.

I have a list of free gifts which I need to keep track of the stock. When customer indicates a choice. It is updated into the database at the particular choice field. I am then able to simply "count" the different choices made by customers. At the same time, I am able to view the choices made by individual customer. Two actions with just one update.

Choosing dates in SQL

If there is a datetime field in your database, chances is that you would want to choose a period between dates. There are more than one way to choose a period.

First I would show the wrong way.

Select mydate from mytable where datefield > '1/1/1900' and datefield < '1/31/1900'

This SQL is not wrong by itself. However, without time qualification, the default time is 00:00:00. It then posts a problem. ">" actually means greater than. This means that the date 1/1/1900 00:00:00 is excluded. "<" refers to less than. This means that the date 1/31/1900 00:00:00 and above will not be included too. In English, this condition means "exclusive" term.

A more correct way is as follows.

select my date from mytable where datefield >= '1/1/1900' and datefield < '2/1/1900'

Another way to select a period is as follows.

select my date from mytable where datefield between '1/1/1900' and '1/31/1900'

I am not sure if that is correct too since the time definition on the second date is missing. To be more exact the second date should be 1/31/1900 23:59:59.