This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
|
dev:sql:functions:ifnull [2009/12/07 15:25] tdo created |
dev:sql:functions:ifnull [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| {{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | <html><div align="center"><span style="color:red">DRAFT</span></div></html> | ||
| - | ====== Omnidex SQL Function: $IFNULL ====== | ||
| {{page>:sql_bar&nofooter&noeditbtn}} | {{page>:sql_bar&nofooter&noeditbtn}} | ||
| + | ====== Omnidex SQL Function: $IFNULL ====== | ||
| ===== Description ===== | ===== Description ===== | ||
| - | $IFNULL specifies a return value for columns that contain null values. The column data type and the return value data type must match. | + | The $IFNULL function tests to see if the first expression produces a NULL, and if so, returns the second expression. |
| - | If column-value is NULL then return specified-value, else return column-value | + | This function returns the same datatype as the passed parameter. If all passed parameters are NULL, then this function returns a NULL. |
| - | Use of this function could change the results of a calculated average. A true NULL is generally not included in a count and thus not included in an average. A NULL altered with this function will be included in the count and the average. | ||
| ===== Syntax ===== | ===== Syntax ===== | ||
| - | $IFNULL(column_spec, specified-value) | + | $IFNULL ( expression_1, expression_2 ) |
| - | == column_spec == | ||
| - | Required. Column name that might contain a null value. | + | == expression_1 and expression_2 == |
| + | |||
| + | Required. An expression using SQL language that produces a value. A scalar expression is generally equivalent to a select item in the SQL SELECT statement, and can be a character expression, numeric expression or date expression. | ||
| - | == specified-value == | ||
| - | |||
| - | Required. The value to return if the column value is null. This value must be of the same data type as the column. | ||
| ===== Example ===== | ===== Example ===== | ||
| + | ==== Example 1: $IFNULL against columns ==== | ||
| - | ==== Example 1 ==== | + | <code> |
| - | <code SQL> | + | > select $ifnull(name, 'Name not given') identifier from individuals; |
| - | select company, | + | |
| - | $IFNULL(contact, 'No contact') | + | IDENTIFIER |
| - | from customers | + | -------------------------------------------------- |
| - | where state = 'ca' | + | MS. MARY LEWIS |
| + | MRS. ALICE R TOLLIVER | ||
| + | MR. SIDNEY K TOLLIVER | ||
| + | MRS. JENNIFER J HOPKINS | ||
| + | MR. CHARLIE HOPKINS | ||
| + | MS. KIMBERLY BLUE | ||
| + | KATHLEEN TUCKER | ||
| </code> | </code> | ||
| + | |||
| + | ====== Additional Resources ====== | ||
| + | |||
| + | See also: | ||
| + | {{page>dev:sql:functions:see_also_logic&noheader&nofooter&noeditbtn}} | ||
| + | |||
| + | |||
| {{page>:bottom_add&nofooter&noeditbtn}} | {{page>:bottom_add&nofooter&noeditbtn}} | ||
| + | |||
| + | ===== | ||
| + | |||