oralce query handling null values

2010-07-06  来源:本站原创  分类:Database  人气:189 

oralce query handling null values

1, the null value on the front

select * from table order by column asc / desc nulls first

2, the null value on the final surface

select * from table order by column asc / desc nulls last

3
SQL to handle null values to provide a number of special functions to handle null values. ISNULL () function can be converted to a valid null value; the NULLIF () function can specify the conditions to generate a null value.
1). Use the ISNULL () function to handle null values
ISNULL () function is replaced with the specified value of null values.
Syntax:
ISNULL (check_expression, replacement_value)
Parameters:
check_expression: will be checked for NULL value for the expression. check_expression can be any type.
replacement_value: In check_expression for the NULL value, the value will be replaced with replacement_value NULL value. replacement_value check_expresssion must have the same type.
Return Type: Return and check_expression the same type.
Note: If check_expression not NULL value, then returns the value of the expression; otherwise replacement_value.

4
2). Use the NULLIF () function to handle null values
NULLIF () function function: If a data table should use NULL values in places other data, then you can use the NULLIF () function will replace these inconsistent values of null values.
Syntax:
NULLIF (expression, expression)
Parameters:
expression: constant, column name, function, subquery, or arithmetic operators, bitwise operators, and any combination of string operators. Return type: return type and the same as the first expression. If two expressions are not equal, NULLIF returns the first expression value. If equal, NULLIF returns the first expression of type null.
Note: If two expressions are equal and the result expression is NULL, NULLIF is equivalent to CASE's search function.

相关文章
  • oralce query handling null values 2010-07-06

    oralce query handling null values 1, the null value on the front select * from table order by column asc / desc nulls first 2, the null value on the final surface select * from table order by column asc / desc nulls last 3 SQL to handle null values t

  • iBatis for the handling of null values 2010-03-29

    Ibatis as a database persistence layer to use people who have experience, Ibatis handling of NULL values is not elegant. This article discusses the NULL value of the common treatment options. JdbcType should be enumerated in the type java.sql.Types i

  • oracle null values ​​sort query data 2011-07-11

    sqlserver considered null minimum. Ascending: null default value in the front row. To the back row is: order by case when col is null then 1 else 0 end, col Descending order: null value defaults to be the last. To the top surface, then: order by case

  • Oracle database using the specified value instead of null values ​​in query results 2011-05-22

    Two cases: 1 replacement value is for display only 2 substitute values ​​in the calculation Case 1: NULL value is not displayed by default, so it can not distinguish between NULL values ​​and empty strings. Then you can use: SET NULL replacement valu

  • freemarker Quick Start + null values many ways 2010-04-30

    Introduction: Why use templates? With JSTL, you need to freemarker? Template nothing to do with the container, the same can be applied to non-Web application environment. ftl file that does not need the compiled after changes, this is different from

  • Database NULL values ​​in the design and operation of the field 2011-01-17

    NULL values ​​in the database as the value of a particular operation in the daily operations with particular attention. If used improperly, in certain conditions, lead to the result set of SQL execution errors. But this error is not caused by the dat

  • Oracle database, the only constraint in the handling NULL 2010-04-01

    According to the definition of NULL, NULL that is unknown, it is more the result of two NULL is neither equal nor unequal, the result is still unknown. According to this definition, the existence of multiple NULL values should not be the only constra

  • MYSQL.MS SQL. ORACLE handle NULL values summary 2010-08-06

    Description: Inquiries need to assign a null value for the custom value. 1.MSSQL: ISNULL () 2.Oracle: NVL () 3.Mysql: IFNULL () In SQL Server Oracle MySQL as the database to identify how to do a certain value NULL? 1.MSSQL: ISNULL () Grammar ISNULL (

  • Select the result with the specified value instead of null values ​​in the 2011-05-22

    Two possibilities: 1. Replacement value is only for display 2. Alternative calculation of the value of participation Case 1: What is the NULL value is not displayed by default, so it can not distinguish between NULL values ​​and empty strings. The so

  • Does not allow NULL values ​​assigned to NOT NULL column "TBSPACEID = 3, TABLEID = 34, COLNO = 29" 2010-11-15

    Does not allow NULL values ​​assigned to NOT NULL column "TBSPACEID = 3, TABLEID = 34, COLNO = 29". SELECT C. TABSCHEMA, C. TABNAME, C. COLNAME FROM SYSCAT.TABLES AS T, SYSCAT.COLUMNS AS C WHERE T. TBSPACEID = 3 AND T. TABLEID = 34 AND C. COLNO

  • DB2 in handling empty values and NULL values 2010-09-16

    1. Input parameters for the character type, and allows for the empty, you can use COALESCE (inputParameter,'') to convert NULL''; 2. Input type integer, and allow for the empty, you can use COALESCE (inputParameter, 0), replaced the idle 0; 3. Input

  • MSSQL query the null 2011-04-15

    isnull function with the case when then end 1 in a query table records in the specified field value is null The most direct idea: select * from table where field = null Test results: No error, but nothing is displayed. The correct answer: select * fr

  • FreeMarker handle null values to judge the skills 2010-07-28

    Recently, when using the query needs to read the page Session of the query terms used in the page $ (Session.messvo.title) I was to messvo object as a query parameter in stored in the Session. But when initialized, and not the value of Session, so Ba

  • Solve emptyText null values in 2010-03-30

    When the object is set emptyText values do not enter a value of the current object, Ext set the default value will emptyText reached background, it can be resolved: In the form submission method add the following: var submitValues = form.getForm ().

  • Oracle NULL values sort of common approach 2010-02-26

    1, the default address in the Order by Oracle that the null when the default is maximum, so if it is ranked in ascending order ASC final, DESC is descending behind the front two, use the nvl function nvl function can be input parameters is empty conv

  • Null values in solving emptyText 2010-03-30

    When the object set emptyText values do not enter the current value of the object, Ext default settings will emptyText reached the background value, can be resolved: Method presented in the form add: var submitValues = form.getForm (). getValues ();

  • Simply replace the URL query string parameter values in the code 2010-04-11

    Demand is to just have a URL in the query part of a specified parameter replaced by the new, if the parameter does not exist, would add a new key on the end of the query. Provided that: 1, the input string may be empty, if not empty then ensure that

  • The drop-down box to allow null values 2010-05-27

    LSComboBoxItem.cs using System; using System.Net; using System.Windows; using System.Windows.Controls; using System.Windows.Documents; using System.Windows.Ink; using System.Windows.Input; using System.Windows.Media; using System.Windows.Media.Animat

  • FreeMarker tips on dealing with null values 2010-08-11

    The following quote the official description: Quote The FreeMarker template language doesn't know the Java language null at all. It doesn't have null keyword, and it can't test if something is null or not. 1. To determine whether there exists keyword

  • oracle null values sort of problem 2010-08-17

    In order for the data when the null value of the field sometimes want standing in the front row at the back can sometimes wish to adopt the following keywords to control order by field name asc / desc nulls first / last