Difference between revisions of "NVL"
From SQLZOO
(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'>'''...") |
|||
| (3 intermediate revisions by one user not shown) | |||
| Line 1: | Line 1: | ||
<table align='right' border='1'> | <table align='right' border='1'> | ||
<caption>Compatibility</caption> | <caption>Compatibility</caption> | ||
| − | <tr><th colspan='3'> | + | <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='center'>'''Engine'''</td><td align='center'>'''OK'''</td><td align='center'>'''Alternative'''</td></tr> | ||
| − | <tr><td align='left'>ingres</td><td> | + | <tr><td align='left'>ingres</td><td>No</td><td>[[COALESCE |COALESCE(f1, f2)]]</td></tr> |
| − | <tr><td align='left'>mysql</td><td> | + | <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>[[ | + | <tr><td align='left'>oracle</td><td>Yes</td><td>[[COALESCE |COALESCE(f1, f2)]]</td></tr> |
| − | <tr><td align='left'>postgres</td><td> | + | <tr><td align='left'>postgres</td><td>No</td><td>[[COALESCE |COALESCE(f1, f2)]]</td></tr> |
| − | <tr><td align='left'>sqlserver</td><td> | + | <tr><td align='left'>sqlserver</td><td>No</td><td>[[COALESCE |COALESCE(f1, f2)]]</td></tr> |
</table> | </table> | ||
| − | <h1> | + | |
| − | <p> | + | <h1>NVL</h1> |
| + | <p>NVL takes two arguments and returns the first value that is not null.</p> | ||
<p></p> | <p></p> | ||
| − | <pre style='width: | + | <pre style='width:60ex'> |
| − | + | NVL(x,y) = x if x is not NULL | |
| − | + | NVL(x,y) = y if x is NULL | |
| − | + | ||
| − | + | ||
</pre> | </pre> | ||
| − | <div class=' | + | <div class='ht'> |
| − | + | 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. | 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. | + | For the MSP with no party (such as Canavan, Dennis) you show the string None. |
| + | <source lang='sql' class='def e-oracle'> | ||
| + | SELECT name, party | ||
| + | ,NVL(party,'None') AS aff | ||
| + | FROM gisq.msp WHERE name LIKE 'C%' | ||
| + | </source> | ||
<source lang='sql' class='def'> | <source lang='sql' class='def'> | ||
SELECT name, party | SELECT name, party | ||
| Line 35: | Line 39: | ||
<li>[[COALESCE |COALESCE function]]</li> | <li>[[COALESCE |COALESCE function]]</li> | ||
</ul> | </ul> | ||
| + | |||
| + | {{Languages}} | ||
Latest revision as of 11:41, 28 October 2012
| NVL(f1, f2) | ||
|---|---|---|
| Engine | OK | Alternative |
| ingres | No | COALESCE(f1, f2) |
| mysql | No | COALESCE(f1, f2) |
| oracle | Yes | COALESCE(f1, f2) |
| postgres | No | COALESCE(f1, f2) |
| sqlserver | No | COALESCE(f1, f2) |
NVL
NVL takes two arguments and returns the first value that is not null.
NVL(x,y) = x if x is not NULL NVL(x,y) = y if x is NULL
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.
SELECT name, party ,NVL(party,'None') AS aff FROM gisq.msp WHERE name LIKE 'C%'
SELECT name, party ,COALESCE(party,'None') AS aff FROM msp WHERE name LIKE 'C%'
See also
| Language: | English • Deutsch |
|---|
