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, '')

Browse previously entered commands Ctrl + R Reverse search for previously entered commands Ctrl + Y Pastes the text in buffer man pages man shows the system’s manual pages. This is the command we use before setting the timestamp will show the same time for execution. echo 'export HISTTIMEFORMAT="%d/%m/%y %T "' >> ~/.bashrc source ~/.bashrc ... ... history Sort files by size You can use -S or –sort=size management tool. These are the ways to install it in Fedora and in Debian/Ubuntu $ sudo dnf install htop -y $ sudo apt-get install htop To know more about htop, please read the man page. $ man htop More about

Browse previously entered commands Ctrl + R Reverse search for previously entered commands Ctrl + Y Pastes the text in buffer 1.21 man pages man shows the system’s manual pages. This is the command before setting the timestamp will show the same time for execution. echo 'export HISTTIMEFORMAT="%d/%m/%y %T "' >> ~/.bashrc source ~/.bashrc ... ... history 3.20 Sort files by size You can use -S or –sort=size command 39 Linux command line for you and me Documentation, Release 0.1 $ sudo dnf install htop -y $ sudo apt-get install htop To know more about htop, please read the man page. $ man htop 7.9 More

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