NVLe COALESCE funzioni

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è identico aCOALESCE. Sono sinonimi. Questo argomento illustra la sintassi e contiene esempi per entrambe.

Sintassi

NVL( expression, expression, ... )

La sintassi per 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 è lo stesso dell'esempio precedente, tranne per il fatto che utilizzaNVL, 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, create una tabella con DATE colonne START _ DATE e END _, inserite righe che includono valori nulli, quindi applicate un'NVLespressione 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 di colonna predefinito per un'NVLespressione è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 nulli per determinate funzioni o colonne, è possibile utilizzare un'NVLespressione per sostituire i valori null con altri valori. Ad esempio, le funzioni aggregate, ad esempioSUM, restituiscono valori nulli anziché zeri quando non hanno righe da valutare. È possibile utilizzare un'NVLespressione per sostituire questi valori nulli 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