NVL - Revision history

Peter at 11:41, 28 October 2012

2012-10-28T11:41:01Z

Connor at 15:13, 16 July 2012

2012-07-16T15:13:02Z

Connor at 10:19, 16 July 2012

2012-07-16T10:19:57Z

Connor at 10:19, 16 July 2012

2012-07-16T10:19:35Z

Connor: Created page with "
Compatibility
COALESCE(f1, f2)

'''Engine''' '''..."
2012-07-16T10:15:55Z

Created page with "<table align='right' border='1'> <caption>Compatibility</caption> <tr><th colspan='3'>COALESCE(f1, f2)</th></tr> <tr><td align='center'>'''Engine'''</td><td align='center'>'''..."

New page
<table align='right' border='1'>
<caption>Compatibility</caption>
<tr><th colspan='3'>COALESCE(f1, f2)</th></tr>
<tr><td align='center'>'''Engine'''</td><td align='center'>'''OK'''</td><td align='center'>'''Alternative'''</td></tr>
<tr><td align='left'>ingres</td><td>Yes</td><td></td></tr>
<tr><td align='left'>mysql</td><td>Yes</td><td>[[IFNULL(f1, f2)]]</td></tr>
<tr><td align='left'>oracle</td><td>Yes</td><td>[[NVL(f1, f2)]]</td></tr>
<tr><td align='left'>postgres</td><td>Yes</td><td></td></tr>
<tr><td align='left'>sqlserver</td><td>Yes</td><td></td></tr>
</table>
<h1>COALESCE</h1>
<p>COALESCE takes any number of arguments and returns the first value that is not null. </p>
<p></p>
<pre style='width:85ex'>
COALESCE(x,y,z) = x if x is not NULL
COALESCE(x,y,z) = y if x is NULL and y is not NULL
COALESCE(a,y,z) = z if x and y are NULL but z is not NULL
COALESCE(x,y,z) = NULL if x and y and z are all NULL
</pre>

<div class='qu'>
COALESCE can be useful when you want to replace a NULL value with some other value.
In this example you show the name of the party for each MSP that has a party.
For the MSP with no party (such as Canavan, Dennis) you show the string None.
<source lang='sql' class='def'>
SELECT name, party
,COALESCE(party,'None') AS aff
FROM msp WHERE name LIKE 'C%'
</source>
</div>

<p>See also</p>
<ul>
<li>[[CASE |CASE function]]</li>
<li>[[COALESCE |COALESCE function]]</li>
</ul>

@@ Line 9: / Line 9: @@
 <tr><td align='left'>sqlserver</td><td>No</td><td>[[COALESCE |COALESCE(f1, f2)]]</td></tr>
 </table>
 <h1>NVL</h1>
 <p>NVL takes two arguments and returns the first value that is not null.</p>
@@ Line 38: / Line 39: @@
    <li>[[COALESCE |COALESCE function]]</li>
 </ul>

@@ Line 24: / Line 24: @@
 SELECT name, party
        ,NVL(party,'None') AS aff
-   FROM msp WHERE name LIKE 'C%'
+   FROM gisq.msp WHERE name LIKE 'C%'
 </source>
 <source lang='sql' class='def'>

@@ Line 1: / Line 1: @@
 <table align='right' border='1'>
 <caption>Compatibility</caption>
-<tr><th colspan='3'>COALESCE(f1, f2)</th></tr>
+<tr><th colspan='3'>NVL(f1, f2)</th></tr>
 <tr><td align='center'>'''Engine'''</td><td align='center'>'''OK'''</td><td align='center'>'''Alternative'''</td></tr>
-<tr><td align='left'>ingres</td><td>Yes</td><td></td></tr>
+<tr><td align='left'>ingres</td><td>No</td><td>[[COALESCE |COALESCE(f1, f2)]]</td></tr>
-<tr><td align='left'>mysql</td><td>Yes</td><td>[[IFNULL(f1, f2)]]</td></tr>
+<tr><td align='left'>mysql</td><td>No</td><td>[[COALESCE |COALESCE(f1, f2)]]</td></tr>
-<tr><td align='left'>oracle</td><td>Yes</td><td>[[NVL(f1, f2)]]</td></tr>
+<tr><td align='left'>oracle</td><td>Yes</td><td>[[COALESCE |COALESCE(f1, f2)]]</td></tr>
-<tr><td align='left'>postgres</td><td>Yes</td><td></td></tr>
+<tr><td align='left'>postgres</td><td>No</td><td>[[COALESCE |COALESCE(f1, f2)]]</td></tr>
-<tr><td align='left'>sqlserver</td><td>Yes</td><td></td></tr>
+<tr><td align='left'>sqlserver</td><td>No</td><td>[[COALESCE |COALESCE(f1, f2)]]</td></tr>
 </table>
-<h1>COALESCE</h1>
+<h1>NVL</h1>
-<p>COALESCE takes any number of arguments and returns the first value that is not null. </p>
+<p>NVL takes two arguments and returns the first value that is not null.</p>
 <p></p>
-<pre style='width:85ex'>
+<pre style='width:60ex'>
-  COALESCE(x,y,z) = x if x is not NULL
+   NVL(x,y) = x if x is not NULL
-  COALESCE(x,y,z) = y if x is NULL and y is not NULL
+   NVL(x,y) = y if x is NULL
-  COALESCE(a,y,z) = z if x and y are NULL but z is not NULL
-  COALESCE(x,y,z) = NULL if x and y and z are all NULL
 </pre>
-<div class='qu'>
+<div class='ht'>
-COALESCE can be useful when you want to replace a NULL value with some other value.
+NVL can be useful when you want to replace a NULL value with some other value.
 In this example you show the name of the party for each MSP that has a party.
 For the MSP with no party (such as Canavan, Dennis) you show the string None.
 <source lang='sql' class='def'>
 SELECT name, party