NVL conversions for various data types. Branching statements in the SELECT statement

NVL function

The NVL function is usually the most commonly used. The function takes two parameters: NVL (expr1, expr2). If the first parameter of 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 can 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 you generate a value using a function, it is assigned an alias. The query results will look like this:

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 function (n [, m])

TRUNC returns the number n, truncated to m decimal places. The m parameter can be omitted - in this 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, the function returns 1. If n is negative, -1 is returned. If equal to 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- there is no division by 0 error.

POWER (n, m) function

The POWER function raises n to the m power. The degree can be fractional or 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 receive exceptional situation... For example:

SELECT POWER (-100, 1/2) X2

FROM DUAL

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

SQRT (n) function

This function returns Square root from the 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 (where n must be greater than zero). Example:

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

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 number.

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

Laboratory work. Changing the format of displayed numbers

Changes to the format of numeric values ​​in Oracle SQL, TO_CHAR function for working with numeric values.

Exercise:

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

Rice. 3.4 -1

In this case, the data should be sorted in such a way that the first lines are displayed for the employees with the highest salary.

Note:

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

Solution:

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 for converting a string to a date TO_DATE (string, format). Possible values formats have already been discussed above, so I will give several 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".

String to string conversion function numerical value TO_NUMBER (string, format). The most common format values ​​are listed in the table, so let's look at the use of this function by examples. Examples:

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

SELECT TO_NUMBER ("500,000", "999G999") FROM DUAL will return 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 dates from the 20th century to the 21st century by specifying only the last two digits of the year.

If the last two digits of the current year are 00 through 49, then the returned year 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 returned year has the same first two digits as the current year.

NVL function

The NVL function is usually the most commonly used. The function takes two parameters: NVL (expr1, exp2). If the first parameter of 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 is contained 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.

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

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

NVL conversions for different data types

The NVL function is used to convert an undefined value to an actual value: NVL ( expression1, expression 2), where:

expression1- The original or calculated value, which may be undefined.

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

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

Convert NVL for different types:

NUMBER - NVL (numeric column, 9).

CHAR or VARCHAR2 - NVL (symbols | column,"Not available").

Laboratory work. Applying the NVL function

NVL function for working 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 (COMMISSION_PCT column) 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 (showing values ​​starting at line 51)

Solution:

The corresponding request code can be like this:

SELECT first_name AS “First name”, last_name As “Last name”, NVL (COMMISSION_PCT, 0) As “Commission rate” FROM hr.employees.

The elections are on the way, which means that today we will elect the governor. Or rather, to appoint a barrier to the passage of candidates to the next round. But first you need to define the terms.

What is guaranteed choice in SQL? Suppose that in the query condition for the table, a comparison is made between a field and a variable. Depending on the value of this variable, the query may or may not return rows from the table. If the value of the variable falls out that rows from the table are not returned, then for this case it is necessary to specially generate a predetermined left result. That is, in any case, the general request 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 am citing 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, "True Chtodadut Wiszajlovich", "editor of the newspaper", 745 from dual union all select 4, "Gracious Lucifera Feoktistovna", "abbess", 234 from dual union all select 5, "Khrensgory Ktotakoy Niktoneznaevich", " rural school teacher ", 3 from dual) select * from t; alter table election add primary key(id);
The initial query that determines the candidates' entry into the next round is extremely simple:
select * from election where votes>: bound
Let's assume that the passing threshold is 8000 votes. Combining this number with: bound, we get

But what if the passing threshold is 10,000, which is more than the maximum number of votes cast? Then it is obvious that none of the aforementioned candidates will qualify for 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 to assign it:

Method 1. UNION ALL tables with self-aggregated

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 when the table does not have a unique NOT NULL field
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 large a barrier of 10,000

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

That's all for now. All characters name matches with real people considered random.

The solution to some other typical SQL problems can be viewed

When composing 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). The expression Coalesce solves this problem perfectly. In this article you will find full description Coalesce sql expressions, usage details, and examples.

Outputting non-empty values ​​with Coalesce. Peculiarities

Consider Sql Coalesce usage features:

1. Allows specifying any number of arguments (unlike Isnull / Nvl / Nvl2, which have a limited number of arguments).
2. Can take subqueries as an argument.
3. Returns a result equal to the first a value other than Null, or Null if different from Null values will not be found.
4. Sql Coalesce can be used in a Select clause to select a non-empty value, and also in Where to clarify that a set of columns with empty values not allowed (/ allowed).
5. This expression is the same as applying the Case expression, which tests each argument sequentially against the condition When argument1 is not null then argument1. Basically, Coalesce is a shortcut for ease of use, and in many DBMSs, query optimizers rewrite the Coalesce expression to Case.
6. Sql Coalesce functions are available in all leading relational

Coalesce syntax

Anyone who has ever used Coalesce when writing sql queries knows that the syntax of this expression extremely simple. Enough in parentheses specify the arguments tested for Null, separated by commas. If we assume that the arguments are named arg1, arg2, ... argN, then the syntax for Coalesce will be as follows:

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

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

Preparing tables

To better understand Coalesce sql description, let's 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 Basic_characteristic table contain information on the main characteristics of the property - Extension, Depth, Area, Scope, Height.

We examined the syntax of Coalesce sql, description, features of use, and let's go directly to the examples.

Examples of using

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

And get the result:

For the objects "Building 1", "Land plot 1" and "Construction 2", both values ​​of the area were filled, but the area was specified in the priority, since we indicated it first in the list of arguments. The Coalesce expression found the first non-empty value and dumped it, stopping further argument scans. This construction the request is correct, because the specified area is more definite than the declared one. If we had specified the declared area as the first argument, then when this field of the table was filled, it would have 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 lines in which the values ​​of the list of fields are empty (or vice versa, include in the result only those values ​​where the list of fields is empty). This situation occurs everywhere: for example, at the 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" come up - either before the check, or when sending an employee on vacation / business trip / sick leave.

Let's select from the table with the main characteristics real estate objects 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.