LNNVL - The 'anything but' expression

View All Scripts Login to Run Script

Script Name LNNVL - The 'anything but' expression
Description LNNVL is nifty but probably underutilised. See how you can handle nulls with this 'anything but' style expression.
Area SQL General / Functions
Contributor swesley_perth
Created Wednesday November 23, 2016

Statement 1

create view basic_data as 
with data as 
 (select 1 id, 'Y' val from dual union all 
  select 2 id, 'N' val from dual union all 
  select 3 id, null val from dual) 
select *  
from data

View created.

Statement 2

Our basic table of data

select * from basic_data


ID VAL
1 Y
2 N
3  - 

3 rows selected.

Statement 3
Will return anything but Y, regardless of existance of nulls
How cool is LNNVL?
```
select * from basic_data 
where lnnvl(val = 'Y') 
```
```
ID VAL
2 N
3  - 

2 rows selected.
```
Statement 4
This attempt will not include any nulls, since 'null = x' returns false
```
select * from basic_data
where val != 'Y'
```
Statement 5
Will fail, can't have IN lists or OR expression within lnnvl
```
select * from basic_data
where lnnvl(val in ('Y','X')) 
```

ID	VAL
1	Y
2	N
3	-

ID	VAL
2	N
3	-

Statement 6

Alternative, verbose notation when IN list required.

select * from basic_data
where val not in ('Y','Z') or val is null

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database