Guía de NULL en Firebird Comportamiento y dificultades de NULL en Firebird Paul Vinkenoog 22 de julio de 2005 - Versión de documento 0.2-es-2 Traducción al castellano: Víctor Zaragoza 2 Tabla de NULL. Este artículo explora el comportamiento de NULL en el SQL de Firebird, apunta fallos comunes y te enseña como manejar de manera segura expresiones que contengan NULL o resulten en NULL. Si solo refrescarte la memoria, salta directamente al sumario (que es realmente breve). Nota Algunas sentencias y ejemplos en esta guía han sido tomados de la Guía de arranque rápido, inicialmente publicado por IBPhoenix

41 14.1.1. The COALESCE function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 14.1.2. Firebird 1.0: the *NVL functions . . Comparisons such as “x > y” also return boolean results, but they are generally not called predicates, although this is mainly a matter of form. An expression like Greater( x, y ) that does exactly the Chapter 14. Converting to and from NULL 14.1. Substituting NULL with a value 14.1.1. The COALESCE function The COALESCE function in Firebird 1.5 and higher can convert NULL to most anything else. This enables

les champs non-NULL sont additionnés et la somme est divisée par le nombre de champs non-NULL . Il y a seulement une exception à cette règle: COUNT(*) renvoie le dénombrement de toutes les lignes, même NULL. Une autre chose à savoir est que COUNT(*) et COUNT(FieldName) ne renvoient jamais NULL: s'il n'y a pas d'enregistrement dans l'ensemble de données les deux fonctions renvoient 0. Ainsi, COUNT(FieldName) NULL, l'argument est passé à la fonction comme une chaîne vide. A l'intérieur de la fonction, il n'y a aucune manière de savoir si cet argument représente réellement une chaîne vide ou un NULL. Donc que

BIN_XOR() BIT_LENGTH() CAST() CEIL(), CEILING() CHAR_LENGTH(), CHARACTER_LENGTH() CHAR_TO_UUID() COALESCE() COS() COSH() COT() DATEADD() DATEDIFF() DECODE() EXP() EXTRACT() MILLISECOND WEEK FLOOR() dow dpower floor getExactTimestamp i64round i64truncate ln log log10 lower lpad ltrim mod *nullif *nvl pi rand right round, i64round rpad rtrim sdow sign sin sinh sqrt srand sright string2blob strlen substr BACKUP BIN_AND BIN_OR BIN_NOT BIN_SHL BIN_SHR BIN_XOR BLOCK BREAK CALLER CEIL CEILING CHAR_TO_UUID COALESCE 19 Руководство по языку SQL COLLATION COMMENT COMMON COS COSH COT DATA DATEADD DATEDIFF DECODE

. else ...поле Job изменилось... NULL в СУБД Firebird 12 Замещение NULL значением Функция COALESCE В Firebird 1.5 есть функция, которая может конвертировать NULL во что-то еще. Это позволит вам конструкции «if (MyExpression is null) then». Функция называется COALESCE, и вы можете вызвать ее так: COALESCE(Expr1, Expr2, Expr3, ...) COALESCE возвращает первое не-NULL выражение из списка аргументов. Если полное имя человека с помощью COALESCE из первого, среднего и последнего имени, предполагая, что среднее имя может иметь значение NULL: select FirstName || coalesce(' ' || MiddleName, '')

types Y Y Y Y Y Y Date and time types Y Y Y Y Y Y String types Y Y Y Y Y Y JSON type Experimental Experimental Experimental Experimental Experimental Experimental Control flow functions Y Y Y Y Y Y String String functions Y Y Y Y Y Y Numeric functions and operators Y Y Y Y Y Y Date and time functions Y Y Y Y Y Y Bit functions and operators Y Y Y Y Y Y Cast functions and operators Y Y Y Y Y Y Encryption compression functions Y Y Y Y Y Y Information functions Y Y Y Y Y Y JSON functions Experimental Experimental Experimental Experimental Experimental Experimental Aggregation functions Y Y Y Y Y Y Window functions

....................................................................................215 9.16.2. COALESCE ............................................................................................... ambiguity. For example, if you have defined a left unary operator named @, you cannot write X*@Y; you must write X* @Y to ensure that PostgreSQL reads it as two operator names not one. 4.1.4. Special Characters avoid division by zero in a WHERE clause: SELECT ... WHERE x > 0 AND y/x > 1.5; But this is safe: SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END; A CASE construct used in this fashion will

....................................................................................203 9.16.2. COALESCE ............................................................................................... ambiguity. For example, if you have defined a left unary operator named @, you cannot write X*@Y; you must write X* @Y to ensure that PostgreSQL reads it as two operator names not one. 4.1.4. Special Characters avoid division by zero in a WHERE clause: SELECT ... WHERE x > 0 AND y/x > 1.5; But this is safe: SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END; A CASE construct used in this fashion will

....................................................................................188 9.13.2. COALESCE ............................................................................................... ambiguity. For example, if you have defined a left unary operator named @, you cannot write X*@Y; you must write X* @Y to ensure that PostgreSQL reads it as two operator names not one. 4.1.4. Special Characters division by zero in a WHERE clause: SELECT ... WHERE x < > 0 AND y/x > 1.5; But this is safe: SELECT ... WHERE CASE WHEN x < > 0 THEN y/x > 1.5 ELSE false END; A CASE construct used in this fashion will

....................................................................................218 9.16.2. COALESCE ............................................................................................... ambiguity. For example, if you have defined a left unary operator named @, you cannot write X*@Y; you must write X* @Y to ensure that PostgreSQL reads it as two operator names not one. 4.1.4. Special Characters parameter-less aggregate functions as window functions, for example count(*) OVER (PARTITION BY x ORDER BY y). * is customarily not used for non- aggregate window functions. Aggregate window functions, unlike