NVL transformations for various data types.

When compiling sql queries, a situation often arises when, when fetching data, it is necessary to compare the values ​​of several columns and display the one that contains the data (not empty). This task is perfectly solved by the expression Coalesce. In this article you will find complete description Coalesce sql expressions, usage details, and examples.

Displaying non-empty values ​​with Coalesce. Peculiarities

Consider Sql Coalesce usage features:

1. Allows any number of arguments (unlike Isnull/Nvl/Nvl2, which have a limited number of arguments).
2. Can accept subqueries as arguments.
3. Returns a result equal to first non-Null value, or Null if no non-Null value is found.
4. Sql Coalesce can be used in a Select clause to select a non-empty value, and also in a Where to specify that a set of columns with empty values not allowed (/allowed).
5. This expression is equivalent to applying the Case expression, which checks each argument in sequence for the condition When argument1 is not null then argument1. In essence, Coalesce is a "shortcut" created for ease of use, and in many DBMS query optimizers rewrite the Coalesce expression with Case.
6. Sql Coalesce functions are available in all leading relational

Syntax

Everyone who has ever used Coalesce when compiling sql queries knows that the syntax given expression extremely simple. Enough in parentheses specify the arguments to be checked for Null separated by commas. If we assume that the arguments have the names arg1, arg2, ... argN, then the syntax of Coalesce will be as follows:

Coalesce(arg1, arg2, ... argN).

Let's prepare several tables to study the mechanism of this expression.

Table preparation

To better understand Coalesce sql description, we will create two tables in the database containing information on real estate objects.

Let the first table Area contain the names of real estate objects and their area. The area can be specified (area_yt) or declared (area_decl).

Let the second table Basic_characteristic contain information on the main characteristic of the property - length (Extension), depth (Depth), area (Area), volume (Scope), height (Height).

We examined the syntax of Coalesce sql, description, usage features and go directly to the examples.

Examples of using

The syntax of the Coalesce expression is extremely simple, but it is important to remember that the result of the command execution will be the FIRST non-empty value found from the argument list. This remark is very great importance, so the arguments in the expression must be arranged in order of their importance. The easiest way to understand the principle is from the area table. Make a query that selects the name of the property, as well as the value of the area:

And get the result:

For the objects "Building 1", "Land 1" and "Construction 2", both values ​​of the area were filled in, but the specified area turned out to be the priority, since we indicated it first in the list of arguments. The Coalesce expression found the first non-null value and outputted it, ceasing to look at any further arguments. This construction request is correct, because the specified area is more specific than the declared one. If we had specified the declared area as the first argument, then if this field of the table was filled, it would be in priority.

In addition to being used in Select, very often the Coalesce expression is used with where condition. It allows you to cut off from the result those rows in which the values ​​of the list of fields are empty (or vice versa, to include in the result only those values ​​where the list of fields is empty). This situation occurs everywhere: for example, at an enterprise, when registering a new employee, only basic information about him was entered into the database, and filling detailed information left for later. Gradually, "gaps" emerge - either before the check, or when sending an employee on vacation / business trip / sick leave.

Let's select real estate objects from the table with the main characteristics, for which none of the characteristic values ​​is filled:

We hope that our detailed Coalesce sql description helped you understand all the features of using this expression, as well as deal with important nuances.

NVL function

The NVL function is generally the most commonly used. The function receives two parameters: NVL(expr1, expr2). If the first parameter expr1 is not NULL, then the function returns its value. If the first parameter is NULL, then the function returns the value of the second parameter expr2 instead.

Consider practical example. The COMM field in the EMP table may contain NULL values. When executing a query like:

SELECT EMPNO, ENAME, COMM, NVL(COMM, 0) NVL_COMM

FROM SCOTT.EMP

the NULL value will be replaced with zero. Note that if a value is generated using a function, it is assigned an alias. The query results will look like:

CEIL(n) function

The CEIL function returns the smallest integer greater than or equal to the number n passed as a parameter. For example:

SELECT CEIL(100) X1, CEIL(-100) X2, CEIL(100.2) X3 , CEIL(-100.2) X4

FROM DUAL

TRUNC(n[,m])

The TRUNC function returns the number n truncated to m decimal places. The m parameter may be omitted, in which case n is truncated to an integer.

SELECT TRUNC(100.25678) X1, TRUNC(-100.25678) X2, TRUNC(100.99) X3,

TRUNC(100.25678, 2) X4

FROM DUAL

SIGN(n) function

The SIGN function determines the sign of a number. If n is positive, then the function returns 1. If negative, -1 is returned. If it is zero, then 0 is returned. For example:

SELECT SIGN(100.22) X1, SIGN(-100.22) X2, SIGN(0) X3

FROM DUAL

An interesting feature of this function is the ability to transfer m equal to zero This does not result in a division by 0 error.

POWER(n, m) function

The POWER function raises the number n to the power m. The degree can be fractional and negative, which significantly expands the capabilities of this function.

SELECT POWER(10, 2) X1, POWER(100, 1/2) X2,

POWER(1000, 1/3) X3, POWER(1000, -1/3) X4

FROM DUAL

In some cases, when calling this function, you may encounter exception. For example:

SELECT POWER(-100, 1/2) X2

FROM DUAL

AT this case an attempt is made to calculate the square root of a negative number, which will result in an ORA-01428 "Argument out of range" error.

Function SQRT(n)

This function returns Square root from number n. For example:

SELECT SQRT(100) X

FROM DUAL

EXP(n) and LN(n) functions

The EXP function raises e to the power of n, and the LN function calculates the natural logarithm of n (with n must be greater than zero). Example:

SELECT EXP(2) X1, LN(1) X2, LN(EXP(2)) X3

Elections are coming up, which means that today we will choose the governor. To be more precise, to set a barrier for candidates to pass to the next round. But first we need to define terms.

What is guaranteed selection in SQL? Let's say that in the query condition to the table, a comparison of some field with some variable is performed. Depending on the value of this variable, the query may or may not return rows from the table. If such a value of the variable drops out that the rows from the table are not returned, then for this case it is necessary to specially generate a predefined left result. That is, in any case, the general query must be guaranteed to return something. The term itself is taken from here. However, the task is complicated by the fact (and maybe vice versa, it is simplified) that instead of one simple cell with a value, we need to be guaranteed to return a full-fledged string.

I bring the data of the Central Election Commission. The first round of voting ended with the following results

create table election as with t (id, name, profession, votes) as (select 1, "Incorruptible Amoral Chistorukovich", "prosecutor", 9867 from dual union all select 2, "Effective Budget Osvoilovich", "businessman", 8650 from dual union all select 3, "Truthful Chtodadut Napisailovich", "newspaper editor", 745 from dual union all select 4, "Good-looking Lucifera Feoktistovna", "priest", 234 from dual union all select 5, "Hrensgory Who is this Niktonesnaevich", " rural school teacher", 3 from dual) select * from t; alter table election add primary key(id);
The initial query that determines the candidates for the next round is extremely simple:
select * from election where votes > :bound
Assume that the barrier to entry is 8,000 votes. Binding this number with :bound, we get

But what if the threshold is 10,000, which is more than the maximum number of votes? Then it is obvious that none of the above-named candidates makes it to the next round. In this case, a dictatorship is established and the cat Colonel automatically becomes the governor of the region. Here are some of the ways it is assigned:

Method 1. UNION ALL tables with aggregated self

With t as (select * from election where votes > :bound) select id, name, profession, votes from t union all select 0, "Colonel", "cat", null from t having count(*) = 0 order by votes desc

Method 2. UNION ALL tables with DUAL
with t as (select * from election where votes > :bound) select id, name, profession, votes from t union all select 0, "colonel", "cat", null from dual where not exists (select null from t) order by votes desc

Method 3. LEFT JOIN tables with DUAL
select nvl(e.id, 0) id, nvl2(e.id, e.name, "Colonel") name, nvl2(e.id, e.profession, "cat") profession, e.votes from dual d left join election e on e.votes >
for cases where there is no unique NOT NULL field in the table
select nvl2(e.rowid, e.id, 0) id, nvl2(e.rowid, e.name, "Colonel") name, nvl2(e.rowid, e.profession, "cat") profession, e.votes from dual d left join election e on e.votes > :bound order by e.votes desc

Method 4. Model with a cat.
select id, name, profession, votes from election where votes > :bound model dimension by (rownum rn) measures (id, name, profession, votes) rules (name = nvl2(id, name, "Colonel"), profession = nvl2 (id, profession, "cat"), id = nvl(id, 0)) order by votes desc

Below guaranteed result with a bind with too much barrier 10,000

It is clear that if you set the initial bar to 8000, then these queries will also work correctly.

That's all for now. All matches of character names with real people consider random.

The solution of some other typical SQL tasks can be viewed

TO_CHAR function with numbers

Functions for converting data to other data types. TO_CHAR(number) converts a number to text. TO_NUMBER(string) converts text to a number.

SELECT TO_CHAR (123) FROM DUAL will return the string 123, SELECT TO_NUMBER (`12345") FROM DUAL will return the number 12345.

Laboratory work. Changing the format of output numbers

Numeric format changes in Oracle SQL, TO_CHAR function for working with numeric values.

Exercise:

Write a query that displays information about the first name, last name, and salary of employees from the hr.employees table in the format shown in Figure 1. 3.4-1:

Rice. 3.4 -1

At the same time, the data should be sorted in such a way that the rows for employees with the highest salary are displayed first.

Note:

Some salary values ​​in Fig. 3.4-1 have been changed so they may not match your values.

Decision:

SELECT first_name AS "First Name", last_name As "Last Name", TO_CHAR(SALARY, "L999999999.99") As "Salary" FROM hr.employees ORDER BY SALARY DESC.

TO_NUMBER and TO_DATE functions

Function to convert string to date TO_DATE(string, format). Possible values formats have already been discussed above, so I will give a few examples of using this function. Examples:

SELECT TO_DATE("01/01/2010", `DD.MM.YYYY") FROM DUAL will return the date `01.01.2010";

SELECT TO_DATE("01.JAN.2010",`DD.MON.YYYY") FROM DUAL will return the date `01.01.2009";

SELECT TO_DATE("15-01-10",`DD-MM-YY") FROM DUAL will return the date `01/15/2010'.

Function to convert string to numerical value TO_NUMBER(string, format). The most common format values ​​are listed in the table, so let's consider the use of this function using examples. Examples:

SELECT TO_NUMBER(`100") FROM DUAL will return the number 100 SELECT TO_NUMBER(`0010.01", "9999D99") FROM DUAL will return the number 10.01;

SELECT TO_NUMBER("500,000", "999G999") FROM DUAL will return the number 500000.

RR element in date format

The RR date and time format element is similar to the YY date and time format element, but it provides additional flexibility for storing date values ​​in other centuries. The RR datetime format element allows you to store 20th century dates in the 21st century by specifying only the last two digits of the year.

If the last two digits of the current year are 00 to 49, then the year returned has the same first two digits as the current year.

If the last two digits of the current year are between 50 and 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.

If the last two digits of the current year are 00 through 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.

If the last two digits of the current year are between 50 and 99, then the year returned has the same first two digits as the current year.

NVL function

The NVL function is generally the most commonly used. The function receives two parameters: NVL(expr1, expr2). If the first parameter expr1 is not NULL, then the function returns its value. If the first parameter is NULL, then the function returns the value of the second parameter exp2 instead.

Example: Select NVL (supplier_city, n/a") from suppliers:

The SQL statement above will return n/" if the supplier_city field contains zero value. Otherwise, it will return the supplier_city value.

Another example of using the NVL function in Oracle/PLSQL is:

select supplier_id, NVL (supplier_desc, supplier_name) from suppliers.

This SQL statement will return supplier_name field if supplier_desc contains a null value. Otherwise it will return supplier_desc.

Last example: using the NVL function in Oracle/PLSQL is: select NVL(commission, 0) from sales;

This SQL statement returned the value 0 if commission the field contains a null value. Otherwise, it would return commissions field.

NVL transformations for various data types

To convert an undefined value to an actual value, use the NVL function: NVL ( expression1, expression2), where:

expression1- The original or computed value, which may be null.

expression2- The value that is substituted for the null value.

Note: The NVL function can be used to convert any data type, but the result will always be the same type as expression1.

NVL conversion for various types:

NUMBER-NVL (numeric column, 9).

CHAR or VARCHAR2 - NVL (characters|column,"Unavailable").

Laboratory work. Applying the NVL function

NVL function for dealing with null values ​​in Oracle SQL.

Exercise:

Write a query that displays information about the first and last names of employees from the hr.employees. table, as well as the commission rate (column COMMISSION_PCT) for the employee. In this case, for those employees for whom the commission is not defined, you need to display the value 0. The result of the query should be the same as shown in Fig. 3.5-1.

Rice. 3.5 -1 (values ​​shown starting from line 51)

Decision:

The corresponding request code might look like this:

SELECT first_name AS "First Name", last_name As "Last Name", NVL (COMMISSION_PCT, 0) As "Commission Rate" FROM hr.employees.