Пустые значения
Пустое значение показывает, что элемент, кортеж или ячейка пуста. Пустое значение ячейки показывает либо то, что данные для указанной ячейки не удалось обнаружить в базовой таблице фактов, либо то, что кортеж для заданной ячейки представляет сочетание элементов, неприменимое для куба.
Хотя пустое значение отличается от нулевого значения, в большинстве случаев оно интерпретируется как ноль.
Следующий запрос иллюстрирует поведение пустых и нулевых значений:
WITH //A calculated Product Category that always returns 0 MEMBER [Product].[Category].[All Products].ReturnZero AS 0 //Will return true for any null value MEMBER MEASURES.ISEMPTYDemo AS ISEMPTY([Measures].[Internet Tax Amount]) //Will true for any null or zero value //To be clear: the expression 0=null always returns true in MDX MEMBER MEASURES.IsZero AS [Measures].[Internet Tax Amount]=0 SELECT <[Measures].[Internet Tax Amount],MEASURES.ISEMPTYDemo,MEASURES.IsZero>ON COLUMNS, [Product].[Category].[Category].ALLMEMBERS ON ROWS FROM [Adventure Works] WHERE([Date].[Calendar].[Calendar Year].&[2001]) О пустых значениях необходимо знать следующее.
-
Функция IsEmpty возвращает значение TRUE , только если ячейка, определяемая кортежем, указанным в функции, пуста. В противном случае функция возвращает ЗНАЧЕНИЕ FALSE.
Заметка Функция IsEmpty не может определить, возвращает ли выражение элемента значение NULL. Чтобы определить, возвращается ли пустой член из выражения, используйте оператор IS.
Пустые значения в инструкциях и кубах многомерных выражений
В инструкциях многомерных выражений можно находить пустые значения и затем выполнять определенные вычисления над ячейками с допустимыми данными (то есть не пустыми). Исключение пустых значений из расчетов важно, поскольку определенные вычисления (например поиск среднего) могут быть неточными при учете пустых значений ячеек.
Если пустые значения хранятся среди данных основной таблицы фактов, по умолчанию они будут преобразованы в нули при обработке куба. Параметр «Обработка null» можно использовать для меры, чтобы контролировать, преобразуются ли факты NULL в 0, преобразуются в пустое значение или даже вызывают ошибку во время обработки. Если пустые ячейки не должны отображаться в результатах запроса, следует создавать запросы, вычисляемые элементы или инструкции скриптов многомерных выражений, которые исключают пустые значения или заменяют их другим значением.
Для удаления пустых строк или столбцов из запроса можно использовать инструкцию NON EMPTY перед определением набора осей. Например, следующий запрос возвращает только категорию продуктов Bikes, так как это единственная категория товаров, которая была в продаже в календарном году 2001:
//Comment out the following line to display all the empty rows for other Categories
FROM [Adventure Works]
В более общем случае для удаления пустых кортежей из набора можно использовать функцию NonEmpty. Следующий запрос показывает две вычисляемые меры, первая из которых показывает количество категорий продуктов, а вторая — количество категорий продуктов со значениями для меры [Internet Tax Amount] и календарного года 2001:
MEMBER MEASURES.CategoryCount AS
MEMBER MEASURES.NonEmptyCategoryCountFor2001 AS
,([Date].[Calendar].[Calendar Year].&[2001], [Measures].[Internet Tax Amount])
FROM [Adventure Works]
Дополнительные сведения см. в разделе NonEmpty (многомерные выражения).
Пустые значения и операторы сравнения
Когда в данных содержатся пустые значения, логические операторы и операторы сравнения могут потенциально возвращать третий результат EMPTY вместо значений TRUE и FALSE. Эта тройственная логика является источником многих проблем в приложениях. В таблицах ниже содержатся сведения о результатах сравнений пустых значений.
В следующей таблице показаны результаты выполнения оператора AND над двумя логическими операндами.
| И | TRUE | EMPTY | FALSE |
|---|---|---|---|
| TRUE | TRUE | FALSE | FALSE |
| ПУСТОЙ | FALSE | EMPTY | FALSE |
| FALSE | FALSE | FALSE | FALSE |
В следующей таблице показаны результаты выполнения оператора OR над двумя логическими операндами.
| ИЛИ | TRUE | FALSE |
|---|---|---|
| TRUE | TRUE | TRUE |
| ПУСТОЙ | TRUE | TRUE |
| FALSE | TRUE | FALSE |
В следующей таблице показаны результаты отрицания (перемены знака) логического операнда оператором NOT.
| Логическое выражение, над которым выполняется оператор NOT | Результат |
|---|---|
| TRUE | FALSE |
| EMPTY | EMPTY |
| FALSE | TRUE |
Замена 0 на Null
В запросах Insert Values и Update, константа 0 или параметр со значением 0 заменяется на Null при записи в поле, если поле является ссылкой и поддерживает запись Null.
Работает только для константы 0. Значения выражений и запросов результат которых равен 0 не преобразовывается. Например 0+0 или даже (0).
Это замена не работает в хранимых процедурах.
Procedure OnCreate; Var Name : String; Group : Integer; Begin Name := 'test'; Group := 0; // При выполнении этого запроса Group будет заменено на Null (Insert Into Clients(Name, Group) Values(:Name, :Group)); // При выполнении этого запроса Group будет заменено на Null (Update Clients Set Group=:Group Where Name=:Name); // При выполнении этого запроса 0 будет заменено на Null (Update Clients Set Group=0 Where Name=:Name); // При выполнении этих запросов Group НЕ БУДЕТ заменено на Null (Update Clients Set Group=(:Group) Where Name=:Name); (Update Clients Set Group=:Group+0 Where Name=:Name); (Insert Into Clients(Name, Group) Select :Name, :Group); End;
Это сделано, что бы в программе не работать со значениями Null. К сожалению значения Null обязательны для корректной работы внешних ключей и JOIN-ов.
При извлечении значения Null из базы данных, он автоматически заменяется на 0, 0c, » или NullDatetime.
Procedure OnCreate; Var Name : String; Group : Integer; Begin Name := 'test'; // При выполнении этого запроса Null будет заменено на 0 Group := (Single Group From Clients Where Name=:Name); End;
Можно сделать замену 0 на Null и для выражений, но это снизит производительность.
NULL-значения в SQL
Достаточно часто встречаются такие случаи, когда в таблице имеются записи с не заданными значениями какого-либо из полей, потому что значение поля неизвестно или его просто нет. В таких случаях SQL позволяет указать в поле NULL-значение. Строго говоря, NULL-значение вовсе не представлено в поле. Когда значение поля есть NULL — это значит, что программа базы данных специальным образом помечает поле, как не содержащее какого-либо значения для данной строки (записи).
Дело обстоит не так в случае простого приписывания полю значения «нуль» или «пробел», которые база данных трактует как любое другое значение. Поскольку NULL не является значением как таковым, он не имеет типа данных. NULL может размещаться в поле любого типа. Тем не менее, NULL, как NULL-значение, часто используется в SQL.
Предположим, появился покупатель, которому еще не назначен продавец. Чтобы констатировать этот факт, нужно ввести значение NULL в поле snum, а реальное значение включить туда позже, когда данному покупателю будет назначен продавец.
SQL IS NULL
Поскольку NULL фиксирует пропущенные значения, результат любого сравнения при наличии NULL-значений неизвестен. Когда NULL-значение сравнивается с любым значением, даже с NULL-значением, результат просто неизвестен. Булево значение «неизвестно» ведет себя также, как «ложь» — строка, на которой предикат принимает значение «неизвестно», не включается в результат запроса – при одном важном исключении: NOT от лжи есть истина (NOT (false)=true), тогда как NOT от неизвестного значения есть также неизвестное значение. Следовательно, такое выражение как «city = NULL» или «city IN (NULL)» является неизвестным независимо от значения city.
Часто необходимо различать false и unknown – строки, содержащие значения столбца, не удовлетворяющие предикату, и строки, которые содержат NULL. Для этой цели SQL располагает специальным оператором IS, который используется с ключевым словом NULL для локализации NULL-значения. SQL IS NULL. Пример. Вывести все поля из талицы Customers, значения поля city которых равны NULL:
SELECT * FROM Customers WHERE city IS NULL
В данном случае выходных данных не будет, поскольку в поле city нет NULL-значений.
SQL IS NOT NULL
Условие IS NOT NULL используется в запросах для выборки записей со значениями не равных значению NULL SQL IS NOT NULL. Пример. Вывести все поля из талицы Customers, значения поля city которых НЕ равны NULL:
Как заменить null на 0 в sql
Чтобы заменить значение NULL на 0 в SQL, можно использовать функцию COALESCE . Эта функция принимает несколько аргументов и возвращает первый не NULL аргумент. Если все аргументы NULL , функция вернет NULL . Вот пример использования COALESCE для замены значений NULL на 0 :
SELECT COALESCE(column_name, 0) FROM table_name; В этом запросе column_name — имя столбца, значения которого нужно заменить, а table_name — имя таблицы, в которой находится столбец. Функция COALESCE заменит все значения NULL в столбце на 0 . Если значение столбца не NULL , то функция вернет его без изменений.
Также можно использовать оператор IS NULL для проверки на NULL и замены его на 0 . Вот пример:
SELECT CASE WHEN column_name IS NULL THEN 0 ELSE column_name END FROM table_name; Этот запрос также заменит значения NULL на 0 . Если значение столбца не NULL , то запрос вернет его без изменений.