NVLet COALESCE fonctions

Renvoie la valeur de la première expression qui n’est pas nulle dans une série d’expressions. Lorsqu’une valeur non nulle est trouvée, les expressions restantes de la liste ne sont pas évaluées.

NVLest identique àCOALESCE. Ce sont des synonymes. Cette rubrique explique la syntaxe et contient des exemples pour les deux.

Syntaxe

NVL( expression, expression, ... )

La syntaxe pour COALESCE est la même :

COALESCE( expression, expression, ... )

Si toutes les expressions régulières sont null, le résultat est null.

Ces fonctions sont utiles lorsque vous souhaitez renvoyer une valeur secondaire lorsqu’une valeur primaire est manquante ou nulle. Par exemple, une requête peut renvoyer le premier des trois numéros de téléphone disponibles : portable, domicile ou travail. L’ordre des expressions dans la fonction détermine l’ordre d’évaluation.

Arguments

expression

Expression, telle qu’un nom de colonne, à évaluer pour l’état null.

Type de retour

Amazon Redshift détermine le type de données de la valeur renvoyée en fonction des expressions d’entrée. Si les types de données des expressions d’entrée n’ont pas de type commun, une erreur est renvoyée.

Exemples

Si la liste contient des expressions entières, la fonction renvoie un entier.

SELECT COALESCE(NULL, 12, NULL);

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

Cet exemple, identique à l'exemple précédent, sauf qu'il utiliseNVL, renvoie le même résultat.

SELECT NVL(NULL, 12, NULL);

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

L’exemple suivant renvoie une chaîne de caractères.

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

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

L’exemple suivant génère une erreur car les types de données varient dans la liste d’expressions. Dans ce cas, la liste contient à la fois un type de chaîne et un type de nombre.

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

Dans cet exemple, vous créez un tableau avec les DATE colonnes START _ DATE et END _, insérez des lignes contenant des valeurs nulles, puis appliquez une NVL expression aux deux colonnes.

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

Le nom de colonne par défaut d'une NVL expression estCOALESCE. La requête suivante renvoie les mêmes résultats :

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

Dans les exemples de requêtes suivants, vous créez une table contenant des exemples d’informations sur les réservations d’hôtel et insérez plusieurs lignes. Certains enregistrements contiennent des valeurs nulles.

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

Insérez l’exemple de données suivant. Certains enregistrements n’ont pas de date check_out ou de montant 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 requête suivante renvoie une liste de dates. Si la date check_out n’est pas disponible, la date check_in est indiquée.

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

Voici les résultats. Notez que les deux derniers enregistrements indiquent la date check_in.

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

Si vous vous attendez à ce qu'une requête renvoie des valeurs nulles pour certaines fonctions ou colonnes, vous pouvez utiliser une NVL expression pour remplacer les valeurs nulles par une autre valeur. Par exemple, les fonctions d'agrégation, telles queSUM, renvoient des valeurs nulles au lieu de zéros lorsqu'elles n'ont aucune ligne à évaluer. Vous pouvez utiliser une NVL expression pour remplacer ces valeurs nulles par700.0. Au lieu de 485, la somme de funds_collected a pour résultat 1885, car les deux lignes contenant la valeur null sont remplacées par 700.

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