Posted By

bbrumm on 03/08/17

Tagged

sql
Oracle

Versions (?)

Last Edited at 03/08/17 11:44am

Statistics

Viewed 1055 times

Favorited by 0 user(s)

Related snippets

Oracle NANVL Function

/ Published in: SQL

A few examples of the NANVL function.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML

CREATE TABLE nanvl_test (
num_val NUMBER(10,3),
f_val FLOAT(3),
bf_val BINARY_FLOAT,
bd_val BINARY_DOUBLE);
 
INSERT INTO nanvl_test (num_val, f_val, bf_val, bd_val)
VALUES (123.456, 234.567, 987.654, 135.789);
 
 
INSERT INTO nanvl_test (num_val, f_val, bf_val, bd_val)
VALUES (0, 0, 'NaN', 'NaN');
 
SELECT num_val, f_val, bf_val, bd_val,
NANVL(num_val, 9999) AS nan_num,
NANVL(f_val, 9999) AS nan_f,
NANVL(bf_val, 9999) AS nan_bf,
NANVL(bd_val, 9999) AS nan_bd
FROM nanvl_test;