Miscellaneous single-row Functions

Miscellaneous single-row Functions

The following single-row functions do not fall into any of the other single-row function categories:

Decodepurpose

DECODEComparesexprTo eachsearchValue one by one. IfexprIs equal tosearch, Then oracle returns the correspondingresult. If no match is found, then oracle returnsdefault. IfdefaultIs omitted, then oracle returns NULL.

IfexprAndsearchContain character data, then oracle compares them using nonpadded comparison semantics.expr,search, AndresultCan be any of the datatypesCHAR,VARCHAR2,NCHAR, OrNVARCHAR2. The string returned isVARCHAR2Datatype and is in the same character set as the firstresultParameter.

Thesearch,result, AnddefaultValues can be derived from expressions. Oracle evaluates eachsearchValue only before comparing itexpr, Rather than evaluating allsearchValues before comparing any of themexpr. Consequently, Oracle never evaluatessearchIf a previoussearchIs equalexpr.

Oracle automatically convertsexprAnd eachsearchValue to the datatype of the firstsearchValue before comparing. Oracle automatically converts the return value to the same datatype as the firstresult. If the firstresultHas the datatypeCHAROr if the firstresultIs null, then oracle converts the return value to the datatypeVARCHAR2.

InDECODEFunction, Oracle considers two nulls to be equivalent. IfexprIs null, then oracle returnsresultOf the firstsearchThat is also null.

The maximum number of components inDECODEFunction, includingexpr,searches,results, Anddefault, Is 255.

Examples

This example decodes the valuewarehouse_id. Ifwarehouse_idIs 1, then the function returns'Southlake'; Ifwarehouse_idIs 2, then it returns'San Francisco'; And so forth. Ifwarehouse_idIs not 1, 2, 3, or 4, then the function returns'Non-domestic'.

SELECT product_id,

       DECODE (warehouse_id, 1, 'Southlake', 

                             2, 'San Francisco', 

                             3, 'New Jersey', 

                             4, 'Seattle',

                                'Non-domestic') 

       "Location of inventory" FROM inventories

       WHERE product_id < 1775;

Nvlpurpose

NVLLets you replace a null (blank) with a string in the results of a query. Ifexpr1Is null, thenNVLReturnsexpr2. Ifexpr1Is not null, thenNVLReturnsexpr1. The argumentsexpr1Andexpr2Can have any datatype. If their datatypes are different, then oracle convertsexpr2To the datatypeexpr1Before comparing them.

The datatype of the return value is always the same as the datatypeexpr1, Unlessexpr1Is character data, in which case the return value's datatype isVARCHAR2And is in the character setexpr1.

Examples

The following example returns a list of employee names and commissions, substituting "Not applicable" if the employee has es no Commission:

SELECT last_name, NVL(TO_CHAR(commission_pct), 'Not Applicable')

   "COMMISSION" FROM employees

   WHERE last_name LIKE 'B%'

   ORDER BY last_name;

LAST_NAME                 COMMISSION

------------------------- ----------------------------------------

Baer                      Not Applicable

Baida                     Not Applicable

Banda                     .1

Bates                     .15

Bell                      Not Applicable

Bernstein                 .25

Bissot                    Not Applicable

Bloom                     .2

Bull                      Not Applicable

Greatestpurpose

GREATESTReturns the greatest of the listexprs. AllexprS after the first are implicitly converted to the datatype of the firstexprBefore the comparison. Oracle comparesexprS using nonpadded comparison semantics. character comparison is based on the value of the character in the database character set. one character is greater than another if it has a higher character set value. if the value returned by this function is character data, then its datatype is alwaysVARCHAR2.

Examples

The following statement selects the string with the greatest value:

SELECT GREATEST ('HARRY', 'HARRIOT', 'HAROLD')

   "Greatest" FROM DUAL;

Greatest

--------

HARRY

Leastpurpose

LEASTReturns the least of the listexprS. AllexprS after the first are implicitly converted to the datatype of the firstexprBefore the comparison. Oracle comparesexprS using nonpadded comparison semantics. If the value returned by this function is character data, then its datatype is alwaysVARCHAR2.

Examples

The following statement is an example of usingLEASTFunction:

SELECT LEAST('HARRY','HARRIOT','HAROLD') "LEAST"

     FROM DUAL;

LEAST 

------

HAROLD