Expr

Purpose

Syntax

Form I

In addition to the schema of a user, schema can also be "PUBLIC" (double quotation marks required), in which case it must qualify a public synonym for a table, view, or snapshot. Qualifying a public synonym with "PUBLIC" is only supported in Data Manipulation Language commands, not Data Definition Language commands.

The pseudocolumn can be either LEVEL, ROWID, or ROWNUM. You can only use a pseudocolumn with a table, rather than with a view or snapshot. For more information on pseudocolumns, see the section "Pseudocolumns" .

ROWLABEL is a column automatically created by Trusted Oracle7 in every table in the database. If you are using Trusted Oracle7, the expression ROWLABEL returns the row's label. If you are not using Trusted Oracle7, the expression ROWLABEL always returns NULL. For information on using labels and ROWLABEL, see Trusted Oracle7 Server Administrator's Guide.

Examples

emp.ename 'this is a text string' 10

Form II

Examples

:employee_name INDICATOR :employee_name_indicator_var

:department_location 

Form III

For information on SQL functions, see the section "SQL Functions" .

Examples

LENGTH('BLAKE') ROUND(1234.567*43) SYSDATE

Form IV

For information on user functions, see the section "User Functions" .

Examples

circle_area(radius) payroll.tax_rate(empno) scott.payrol.tax_rate(dependents, empno)@ny

Form V

Note that some combinations of functions are inappropriate and are rejected. For example, the LENGTH function is inappropriate within a group function.

Examples

('CLARK' || 'SMITH') LENGTH('MOOSE') * 57 SQRT(144) + 72 my_fun(TO_CHAR(sysdate,'DD-MMM-YY')

Decoded Expression

To evaluate this expression, Oracle7 compares expr to each search value one by one. If expr is equal to a search, Oracle7 returns the corresponding result. If no match is found, Oracle7 returns default, or, if default is omitted, returns null. If expr and search contain character data, Oracle7 compares them using non-padded comparison semantics. For information on these semantics, see the section "Datatype Comparison Rules" .

The search, result, and default values can be derived from expressions. Oracle7 evaluates each search value only before comparing it to expr, rather than evaluating all search values before comparing any of them with expr. Consequently, Oracle7 never evaluates a search if a previous search is equal to expr.

Oracle7 automatically converts expr and each search value to the datatype of the first search value before comparing. Oracle7 automatically converts the return value to the same datatype as the first result. If the first result has the datatype CHAR or if the first result is null, then Oracle7 converts the return value to the datatype VARCHAR2. For information on datatype conversion, see the section "Data Conversion" .

In a DECODE expression, Oracle7 considers two nulls to be equivalent. If expr is null, Oracle7 returns the result of the first search that is also null.

The maximum number of components in the DECODE expression, including expr, searches, results, and default is 255.

Example

This expression decodes the value DEPTNO. If DEPTNO is 10, the expression evaluates to 'ACCOUNTING'; if DEPTNO is 20, it evaluates to 'RESEARCH'; etc. If DEPTNO is not 10, 20, 30, or 40, the expression returns 'NONE'.

DECODE (deptno,10,	'ACCOUNTING', 

				20,	'RESEARCH', 

				30,	'SALES', 

				40,	'OPERATION', 

					'NONE') 

List of Expressions

An expression list can contain up to 254 expressions.

Examples

(10, 20, 40) 

('SCOTT', 'BLAKE', 'TAYLOR') 

(LENGTH('MOOSE') * 57, -SQRT(144) + 72, 69) 

Usage Notes

This simple expression evaluates to 4 and has datatype NUMBER (the same datatype as its components):

2*2 

The following expression is an example of a more complex expression that uses both functions and operators. The expression adds seven days to the current date, removes the time component from the sum, and converts the result to CHAR datatype:

TO_CHAR(TRUNC(SYSDATE+7)) 

You can use expressions in any of these places:

• the select list of the SELECT command

• a condition of the WHERE and HAVING clauses

• the CONNECT BY, START WITH, and ORDER BY clauses

• the VALUES clause of the INSERT command

• the SET clause of the UPDATE command

SET ename = 'smith' 

This SET clause has the expression LOWER(ENAME) instead of the quoted string 'smith':

SET ename = LOWER(ename) 

Related Topics