Funzioni NVL e COALESCE

PDFRSS

Restituisce il valore della prima espressione che non è null in una serie di espressioni. Quando viene trovato un valore non null, le restanti espressioni nell'elenco non vengono valutate.

NVL è identica a COALESCE. Sono sinonimi. Questo argomento illustra la sintassi e contiene esempi per entrambe.

Sintassi

NVL( expression, expression, ... )

La sintassi di COALESCE è la stessa:

COALESCE( expression, expression, ... )

Se tutte le espressioni sono null, il risultato è null.

Queste funzioni sono utili quando si desidera restituire un valore secondario quando manca un valore primario o è null. Ad esempio, una query potrebbe restituire il primo dei tre numeri di telefono disponibili: cellulare, casa o ufficio. L'ordine delle espressioni nella funzione determina l'ordine di valutazione.

Argomenti

espressione

Un'espressione, come ad esempio un nome di colonna, da valutare per lo stato null.

Tipo restituito

Amazon Redshift determina il tipo di dati del valore restituito in base alle espressioni di input. Se i tipi di dati delle espressioni di input non hanno un tipo comune, viene restituito un errore.

Esempi

Se l'elenco contiene espressioni intere, la funzione restituisce un numero intero.

SELECT COALESCE(NULL, 12, NULL);

coalesce
--------------
12

Questo esempio, che è uguale all'esempio precedente tranne per il fatto che utilizza NVL, restituisce lo stesso risultato.

SELECT NVL(NULL, 12, NULL);

coalesce
--------------
12

Nell'esempio seguente viene restituito un tipo di stringa.

SELECT COALESCE(NULL, 'Amazon Redshift', NULL);

coalesce
--------------
Amazon Redshift

L'esempio seguente genera un errore perché i tipi di dati variano nell'elenco delle espressioni. In questo caso, nell'elenco sono presenti sia un tipo di stringa che un tipo numerico.

SELECT COALESCE(NULL, 'Amazon Redshift', 12);
ERROR: invalid input syntax for integer: "Amazon Redshift"

Per questo esempio, si crea una tabella con le colonne START_DATE e END_DATE, si inseriscono alcune righe che includono valori null, quindi si applica un'espressione NVL alle due colonne.

create table datetable (start_date date, end_date date);           
insert into datetable values ('2008-06-01','2008-12-31');
insert into datetable values (null,'2008-12-31');
insert into datetable values ('2008-12-31',null);

select nvl(start_date, end_date)
from datetable
order by 1;
               
coalesce
------------
2008-06-01
2008-12-31
2008-12-31

Il nome della colonna predefinita per un'espressione NVL è COALESCE. La seguente query restituisce gli stessi risultati:

select coalesce(start_date, end_date)
from datetable
order by 1;

Per le seguenti query di esempio, si crea una tabella con informazioni di esempio sulla prenotazione alberghiera e si inseriscono diverse righe. Alcuni record contengono valori null.

create table booking_info (booking_id int, booking_code character(8), check_in date, check_out date, funds_collected numeric(12,2));

Inserire i seguenti dati di esempio. Alcuni record non hanno una data di check_out o un importo funds_collected.

insert into booking_info values (1, 'OCEAN_WV', '2023-02-01','2023-02-03',100.00);
insert into booking_info values (2, 'OCEAN_WV', '2023-04-22','2023-04-26',120.00);
insert into booking_info values (3, 'DSRT_SUN', '2023-03-13','2023-03-16',125.00);
insert into booking_info values (4, 'DSRT_SUN', '2023-06-01','2023-06-03',140.00);
insert into booking_info values (5, 'DSRT_SUN', '2023-07-10',null,null);
insert into booking_info values (6, 'OCEAN_WV', '2023-08-15',null,null);

La seguente query restituisce un elenco di date. Se la data di check_out non è disponibile, elenca la data di check_in.

select coalesce(check_out, check_in)
from booking_info
order by booking_id;

Di seguito sono riportati i risultati. Si noti che gli ultimi due record mostrano la data di check_in.

coalesce
------------
2023-02-03
2023-04-26	
2023-03-16	
2023-06-03	
2023-07-10	
2023-08-15	

Se si prevede che una query restituisca valori null per determinate funzioni o colonne, è possibile utilizzare un'espressione NVL per sostituire i valori null con qualche altro valore. Ad esempio, le funzioni di aggregazione, come SUM, restituiscono valori null anziché zero quando non hanno righe da valutare. È possibile utilizzare un'espressione NVL per sostituire questi valori null con 700.0. Invece di 485, il risultato della somma di funds_collected è 1885 perché due righe con valori null vengono sostituite con 700.

select sum(nvl(funds_collected, 700.0)) as sumresult from booking_info;
               
sumresult
------
 1885