[thelist] Oracle Tips (Was: Site Review Please)

[apatrick at oracular.com]

|> Rudy_Limeback at maritimelife.ca wrote:
|> <|tip|>
|>    SELECT "Dear "||COALESCE(preferred_name, first_name, "Sir or Madam")
|>      FROM yourTable
|> 
|> COALESCE will return the first non-null value in its list, so you can set
|> up a cascade of column or literal references in the preferred order
|> 
|> in oracle, the NVL function replaces COALESCE, and i think it only takes
|> two arguments, so i'm not sure how you would do three....   adam?

All you have to do is ask :-)

   SELECT "Dear "|| nvl( nvl( preferred_name, first_name ), "Sir or Madam" ) )
     FROM yourTable

|> <|/tip|>

<|tip type="I'm feeling saucy today"|>
Oracle provides some nice decision-making functions for SQL.  The include 
DECODE, CASE, NVL, NVL2 (just learned about NVL2 today).  

DECODE and CASE are essentially the same thing.  They provide If-Then-Else 
functionality.  Rather than try to explain them, here's a link to Oracle's 
documentation on the subject: 
<|wrap|>
http://oradoc.photo.net/ora816/server.816/a76989/express2.htm#1016155
<|/wrap|>

NVL provides "just in case" values for columns or variables that might be null.
NVL2 will return param 2 if  param 1 *is not* null and param 3 if param 1 *is* 
null.  Links to docs for NVL and NVL2 follow:
<|wrap|>
http://oradoc.photo.net/ora816/server.816/a76989/functi66.htm#91645
http://oradoc.photo.net/ora816/server.816/a76989/functi67.htm
<|/wrap|>

<|/tip|>