Sql Functions

Built in SQL Functions: Character / String Functions: ASCII FUNCTION..................................................................................................................................................... ....4 ASCIISTR FUNCTION.................................................................................................................................... .............5 CHR FUNCTION................................................................................................................................................... ........5 COMPOSE FUNCTION.......................................................................................................................................... ......5 CONCAT FUNCTION......................................................................................................................................... ..........6 || OPERATOR........................................................................................................................................................ .........6 CONVERT FUNCTION................................................................................................................................ ................7 DECOMPOSE FUNCTION....................................................................................................................... ...................8 DUMP FUNCTION............................................................................................................................................ ............8 INITCAP FUNCTION.................................................................................................................................. .................9 INSTR FUNCTION...................................................................................................................................................... ..9 LENGTH FUNCTION......................................................................................................................................... ........10 LOWER FUNCTION................................................................................................................................................. ..10 LPAD FUNCTION.................................................................................................................................. .....................11 LTRIM FUNCTION............................................................................................................................................ .........11 REPLACE FUNCTION...................................................................................................................................... .........12 RPAD FUNCTION............................................................................................................................................ ...........13 RTRIM FUNCTION................................................................................................................................... .................13 SOUNDEX FUNCTION........................................................................................................................ ......................14 SUBSTR FUNCTION.............................................................................................................................. ....................15 TRANSLATE FUNCTION................................................................................................................................ ..........15 TRIM FUNCTION............................................................................................................................................. ..........16 UPPER FUNCTION.................................................................................................................................................... .17 VSIZE FUNCTION.............................................................................................................................................. ........17 BIN_TO_NUM FUNCTION.................................................................................................................................. ......18 CAST FUNCTION....................................................................................................................................... ................18 CHARTOROWID FUNCTION........................................................................................................... .......................19 FROM_TZ FUNCTION........................................................................................................................ ......................19 HEXTORAW FUNCTION.................................................................................................................... ......................20 NUMTODSINTERVAL FUNCTION.................................................................................................................. ........20 NUMTOYMINTERVAL FUNCTION..................................................................................................... ...................21 TO_CHAR FUNCTION............................................................................................................................................... 21 TO_CLOB FUNCTION..................................................................................................................................... ..........24 TO_DATE FUNCTION......................................................................................................................... ......................24 TO_MULTI_BYTE FUNCTION................................................................................................................................ .26

1

none GE Confidential

TO_NCLOB FUNCTION.............................................................................................................................. ..............26 TO_NUMBER FUNCTION...................................................................................................................................... ...27 TO_DSINTERVAL FUNCTION................................................................................................................ .................27 TO_SINGLE_BYTE FUNCTION............................................................................................................................ ...28 TO_LOB FUNCTION............................................................................................................................................ ......28 TO_TIMESTAMP FUNCTION............................................................................................................. .....................28 TO_TIMESTAMP_TZ FUNCTION............................................................................................................... ............29 TO_YMINTERVAL FUNCTION........................................................................................................................... .....30 BFILENAME FUNCTION................................................................................................................................. .........31 CARDINALITY FUNCTION (NEW IN ORACLE 10).................................................................................... .........31 CASE STATEMENT........................................................................................................................................ ............32 COALESCE FUNCTION............................................................................................................................... .............34 DECODE FUNCTION......................................................................................................................................... ........34 GROUP_ID FUNCTION.............................................................................................................................. ...............37 NANVL FUNCTION (NEW IN ORACLE 10).............................................................................................. .............37 NVL FUNCTION......................................................................................................................................................... .38 NVL2 FUNCTION................................................................................................................................... ....................39 SYS_CONTEXT FUNCTION...................................................................................................................... ...............40 UID FUNCTION..................................................................................................................................... .....................42 USER FUNCTION........................................................................................................................................ ...............42 USERENV FUNCTION..................................................................................................................................... ..........43 ABS FUNCTION............................................................................................................................................... ...........44 ACOS FUNCTION.................................................................................................................................................. .....44 ASIN FUNCTION....................................................................................................................................... .................45 ATAN FUNCTION.......................................................................................................................................... .............45 ATAN2 FUNCTION....................................................................................................................................... ..............45 AVG FUNCTION..................................................................................................................................... ....................46 BIN_TO_NUM FUNCTION.................................................................................................................................. ......47 BITAND FUNCTION................................................................................................................................................ ...47 CEIL FUNCTION....................................................................................................................................... .................48 CORR FUNCTION......................................................................................................................................... .............48 COS FUNCTION......................................................................................................................................................... .49 COSH FUNCTION............................................................................................................................... .......................49 COVAR_POP FUNCTION........................................................................................................................... ...............49 COVAR_SAMP FUNCTION.............................................................................................................................. .........50 COUNT FUNCTION.................................................................................................................................................... 50 CUME_DIST FUNCTION......................................................................................................................................... ..52 DENSE_RANK FUNCTION.......................................................................................................................... .............53 EXP FUNCTION............................................................................................................................................... ...........54 EXTRACT FUNCTION............................................................................................................................................... 54

2

none GE Confidential

FLOOR FUNCTION................................................................................................................................................ ....55 GREATEST FUNCTION.......................................................................................................................................... ...55 LEAST FUNCTION.............................................................................................................................................. .......56 LN FUNCTION............................................................................................................................................. ...............56 LOG FUNCTION................................................................................................................................................ .........57 MAX FUNCTION....................................................................................................................................... .................57 MEDIAN FUNCTION (NEW IN ORACLE 10)................................................................................. .......................60 MIN FUNCTION......................................................................................................................................................... .60 MOD FUNCTION............................................................................................................................................ ............61 POWER FUNCTION............................................................................................................................................ .......61 RANK FUNCTION......................................................................................................................................... .............62 REMAINDER FUNCTION (NEW IN ORACLE 10)............................................................................... .................63 ROUND FUNCTION (WITH NUMBERS).................................................................................................... ............63 ROUND FUNCTION (WITH DATES)................................................................................................................ .......64 SIGN FUNCTION............................................................................................................................................ ............65 SIN FUNCTION.............................................................................................................................................. .............65 SINH FUNCTION............................................................................................................................................ ............66 SQRT FUNCTION......................................................................................................................................... ..............66 STDDEV FUNCTION............................................................................................................................................ ......67 SUM FUNCTION................................................................................................................................................ .........67 TAN FUNCTION........................................................................................................................................................ ..69 TANH FUNCTION...................................................................................................................................................... .69 TRUNC FUNCTION (WITH DATES)...................................................................................................... .................70 TRUNC FUNCTION (WITH NUMBERS).......................................................................................... ......................71 VAR_POP FUNCTION.................................................................................................................................... ............71 VAR_SAMP FUNCTION....................................................................................................................................... ......71 VARIANCE FUNCTION.................................................................................................................................. ...........72 ADD_MONTHS FUNCTION................................................................................................................................ ......72 CURRENT_DATE FUNCTION.............................................................................................................................. ....73 CURRENT_TIMESTAMP FUNCTION.................................................................................................................. ...74 TZ_OFFSET FUNCTION.......................................................................................................................... .................74 DBTIMEZONE FUNCTION.................................................................................................................................... ...75 FROM_TZ FUNCTION........................................................................................................................ ......................76 LAST_DAY FUNCTION........................................................................................................................ .....................77 LOCALTIMESTAMP FUNCTION....................................................................................................... .....................77 NEW_TIME FUNCTION.................................................................................................................................... ........78 NEXT_DAY FUNCTION....................................................................................................................................... ......79 ROUND FUNCTION (WITH DATES)................................................................................................................ .......79 SESSIONTIMEZONE FUNCTION........................................................................................................................... .80 MONTHS_BETWEEN FUNCTION.................................................................................................... ......................81

3

none GE Confidential

SYSTIMESTAMP FUNCTION............................................................................................................................. ......82 TO_CHAR FUNCTION............................................................................................................................................... 83 SYSDATE FUNCTION......................................................................................................................................... .......85 TO_DATE FUNCTION......................................................................................................................... ......................86 TRUNC FUNCTION (WITH DATES)...................................................................................................... .................87 SQLCODE FUNCTION........................................................................................................................ ......................88 SQLERRM FUNCTION....................................................................................................................................... .......89 UID FUNCTION..................................................................................................................................... .....................90 USER FUNCTION........................................................................................................................................ ...............91

1. Character / String Functions: Ascii Function In Oracle/PLSQL, the ascii function returns the NUMBER code that represents the specified character. The syntax for the ascii function is: ascii( single_character ) single_character is the specified character to retrieve the NUMBER code for. If more than one character is entered, the function will return the value for the first character and ignore all of the characters after the first.

For example: ascii('t')

would return 116.

ascii('T')

would return 84.

ascii('T2')

would also return 84.

To view a listing of the ascii values, click here

4

none GE Confidential

AsciiStr Function In Oracle/PLSQL, the asciistr function converts a string in any character set to an ASCII string using the database character set. The syntax for the asciistr function is: asciistr( string ) string is a string in any character set that you want converted to an ASCII string in the database character set.

For example: asciistr('A B C Ä Ê')

would return 'A B C \00C4 \00CA'

asciistr('A B C Õ Ø')

would return 'A B C \00D5 \00D8'

asciistr('A B C Ä Ê Í Õ Ø')

would return 'A B C \00C4 \00CA \00CD \00D5 \00D8'

Chr Function In Oracle/PLSQL, the chr function is the opposite of the ascii function. It returns the character based on the NUMBER code. The syntax for the chr function is: chr( number_code ) number_code is the NUMBER code used to retrieve the character.

For example: chr(116);

would return 't'.

chr(84);

would return 'T'.

To view a listing of the ascii values, click here.

Compose Function In Oracle/PLSQL, the compose function returns a Unicode string. The syntax for the compose function is:

5

none GE Confidential

compose( string ) string is the input value used to create the Unicode string. It can be a char, varchar2, nchar, nvarchar2, clob, or nclob.

Below is a listing of unistring values that can be combined with other characters in the compose function. Unistring Value

Resulting character

unistr('\0300')

grave accent ( ` )

unistr('\0301')

acute accent ( ´ )

unistr('\0302')

circumflex ( ^ )

unistr('\0303')

tilde ( ~ )

unistr('\0308')

umlaut ( ¨ )

For example: compose('o' || unistr('\0308') )

would return ö

compose('a' || unistr('\0302') )

would return â

compose('e' || unistr('\0301') )

would return é

Concat Function In Oracle/PLSQL, the concat function allows you to concatenate two strings together. The syntax for the concat function is: concat( string1, string2 ) string1 is the first string to concatenate. string2 is the second string to concatenate.

For example: concat('Tech on', ' the Net');

would return 'Tech on the Net'.

concat('a', 'b')

would return 'ab'.

|| Operator In Oracle/PLSQL, the || operator allows you to concatenate 2 or more strings together.

6

none GE Confidential

The syntax for the || operator is: string1 || string2 || string_n string1 is the first string to concatenate. string2 is the second string to concatenate. string_n is the nth string to concatenate.

For example: 'Tech on' || ' the Net'

would return 'Tech on the Net'.

'a' || 'b' || 'c' || 'd'

would return 'abcd'.

Convert Function In Oracle/PLSQL, the convert function converts a string from one character set to another. The syntax of the convert function is: convert( string1 , char_set_to , [ char_set_from ] ) string1 is the string to be converted. char_set_to is the character set to convert to. char_set_from is the character set to convert from.

Character sets include: Character Set

Description

US7ASCII

US 7-bit ASCII character set

WE8DEC

West European 8-bit character set

WE8HP

HP West European Laserjet 8-bit character set

F7DEC

DEC French 7-bit character set

WE8EBCDIC500

IBM West European EBCDIC Code Page 500

WE8PC850

IBM PC Code Page 850

WE8ISO8859P1

ISO 8859-1 West European 8-bit character set

7

none GE Confidential

For example: convert('A B C D E Ä Ê Í Õ Ø', 'US7ASCII', 'WE8ISO8859P1')

would return 'A B C D E A E I ? ?'

Decompose Function In Oracle/PLSQL, the decompose function accepts a string and returns a Unicode string. The syntax for the decompose function is: decompose( string ) string is the string that will be decomposed.

For example: decompose('Très bien')

would return 'Tre`s bien'

decompose('é')

would return 'e´'

decompose('olé')

would return 'ole´'

Dump Function In Oracle/PLSQL, the dump function returns a varchar2 value that includes the datatype code, the length in bytes, and the internal representation of the expression. The syntax for the dump function is: dump( expression, [return_format], [start_position], [length] ) expression is the expression to analyze. return_format is optional. It determines the format of the return value. This parameter can be any of the following values: Value

Explanation

8

octal notation

10

decimal notation

16

hexadecimal notation

17

single characters

1008

octal notation with the character set name

1010

decimal notation with the character set name

1016

hexadecimal notation with the character set name

1017

single characters with the character set name

8

none GE Confidential

start_position and length are optional parameters. They determines which portion of the internal representation to display. If these parameters are omitted, the dump function will display the entire internal representation in decimal notation.

For example: dump('Tech')

would return 'Typ=96 Len=4: 84,101,99,104'

dump('Tech', 10)

would return 'Typ=96 Len=4: 84,101,99,104'

dump('Tech', 16)

would return 'Typ=96 Len=4: 54,65,63,68'

dump('Tech', 1016)

would return 'Typ=96 Len=4 CharacterSet=US7ASCII: 54,65,63,68'

dump('Tech', 1017)

would return 'Typ=96 Len=4 CharacterSet=US7ASCII: T,e,c,h'

Initcap Function In Oracle/PLSQL, the initcap function sets the first character in each word to uppercase and the rest to lowercase. The syntax for the initcap function is: initcap( string1 ) string1 is the string argument whose first character in each word will be converted to uppercase and all remaining characters converted to lowercase.

For example: initcap('tech on the net');

would return 'Tech On The Net'

initcap('GEORGE BURNS'); would return 'George Burns'

Instr Function In Oracle/PLSQL, the instr function returns the location of a substring in a string. The syntax for the instr Oracle function is: instr( string1, string2, [ start_position ], [ nth_appearance ] ) string1 is the string to search. string2 is the substring to search for in string1. start_position is the position in string1 where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1.

9

none GE Confidential

nth_appearance is the nth appearance of string2. This is optional. If omitted, it defaults to 1.

Note: If string2 is not found in string1, then the instr Oracle function will return 0.

For example: instr('Tech on the net', 'e')

would return 2; the first occurrence of 'e'

instr('Tech on the net', 'e', 1, 1)

would return 2; the first occurrence of 'e'

instr('Tech on the net', 'e', 1, 2)

would return 11; the second occurrence of 'e'

instr('Tech on the net', 'e', 1, 3)

would return 14; the third occurrence of 'e'

instr('Tech on the net', 'e', -3, 2)

would return 2.

Length Function In Oracle/PLSQL, the length function returns the length of the specified string. The syntax for the length function is: length( string1 ) string1 is the string to return the length for. If string1 is NULL, then the function returns NULL.

For example: length(NULL)

would return NULL.

length('')

would return NULL.

length('Tech on the Net')

would return 15.

length('Tech on the Net ')

would return 16.

Lower Function In Oracle/PLSQL, the lower function converts all letters in the specified string to lowercase. If there are characters in the string that are not letters, they are unaffected by this function. The syntax for the lower function is: lower( string1 ) string1 is the string to convert to lowercase.

10

none GE Confidential

For example: lower('Tech on the Net');

would return 'tech on the net'

lower('GEORGE BURNS 123 ');

would return 'george burns 123 '

Lpad Function In Oracle/PLSQL, the lpad function pads the left-side of a string with a specific set of characters. The syntax for the lpad function is: lpad( string1, padded_length, [ pad_string ] ) string1 is the string to pad characters to (the left-hand side). padded_length is the number of characters to return. If the padded_length is smaller than the original string, the lpad function will truncate the string to the size of padded_length. pad_string is optional. This is the string that will be padded to the left-hand side of string1. If this parameter is omitted, the lpad function will pad spaces to the left-side of string1.

For example: lpad('tech', 7);

would return ' tech'

lpad('tech', 2);

would return 'te'

lpad('tech', 8, '0');

would return '0000tech'

lpad('tech on the net', 15, 'z');

would return 'tech on the net'

lpad('tech on the net', 16, 'z');

would return 'ztech on the net'

Ltrim Function In Oracle/PLSQL, the ltrim function removes all specified characters from the left-hand side of a string. The syntax for the ltrim function is: ltrim( string1, [ trim_string ] ) string1 is the string to trim the characters from the left-hand side. trim_string is the string that will be removed from the left-hand side of string1. If this parameter is omitted, the ltrim function will remove all leading spaces from string1.

11

none GE Confidential

For example: ltrim(' tech');

would return 'tech'

ltrim(' tech', ' ');

would return 'tech'

ltrim('000123', '0');

would return '123'

ltrim('123123Tech', '123');

would return 'Tech'

ltrim('123123Tech123', '123');

would return 'Tech123'

ltrim('xyxzyyyTech', 'xyz');

would return 'Tech'

ltrim('6372Tech', '0123456789');

would return 'Tech'

The ltrim function may appear to remove patterns, but this is not the case as demonstrated in the following example. ltrim('xyxzyyyTech', 'xyz');

would return 'Tech'

It actually removes the individual occurrences of 'x', 'y', and 'z', as opposed to the pattern of 'xyz'.

The ltrim function can also be used to remove all leading numbers as demonstrated in the next example. ltrim( '6372Tech', '0123456789');

would return 'Tech'

In this example, every number combination from 0 to 9 has been listed in the trim_string parameter. By doing this, it does not matter the order that the numbers appear in string1, all leading numbers will be removed by the ltrim function.

Replace Function In Oracle/PLSQL, the replace function replaces a sequence of characters in a string with another set of characters. The syntax for the replace function is: replace( string1, string_to_replace, [ replacement_string ] ) string1 is the string to replace a sequence of characters with another set of characters. string_to_replace is the string that will be searched for in string1. replacement_string is optional. All occurrences of string_to_replace will be replaced with replacement_string in string1. If the replacement_string parameter is omitted, the replace function simply removes all occurrences of string_to_replace, and returns the resulting string. For example: replace('123123tech', '123');

would return 'tech'

replace('123tech123', '123');

would return 'tech'

12

none GE Confidential

replace('222tech, '2', '3');

would return '333tech'

replace('0000123', '0');

would return '123'

replace('0000123', '0', ' ');

would return '

123'

Rpad Function In Oracle/PLSQL, the rpad function pads the right-side of a string with a specific set of characters. The syntax for the rpad function is: rpad( string1, padded_length, [ pad_string ] ) string1 is the string to pad characters to (the right-hand side). padded_length is the number of characters to return. If the padded_length is smaller than the original string, the rpad function will truncate the string to the size of padded_length. pad_string is optional. This is the string that will be padded to the right-hand side of string1. If this parameter is omitted, the rpad function will pad spaces to the right-side of string1.

For example: rpad('tech', 7);

would return 'tech '

rpad('tech', 2);

would return 'te'

rpad('tech', 8, '0');

would return 'tech0000'

rpad('tech on the net', 15, 'z');

would return 'tech on the net'

rpad('tech on the net', 16, 'z');

would return 'tech on the netz'

Rtrim Function In Oracle/PLSQL, the rtrim function removes all specified characters from the right-hand side of a string. The syntax for the rtrim function is: rtrim( string1, [ trim_string ] ) string1 is the string to trim the characters from the right-hand side. trim_string is the string that will be removed from the right-hand side of string1. If this parameter is omitted, the rtrim function will remove all trailing spaces from string1.

For example: rtrim('tech ');

would return 'tech'

13

none GE Confidential

rtrim('tech ', ' ');

would return 'tech'

rtrim('123000', '0');

would return '123'

rtrim('Tech123123', '123');

would return 'Tech'

rtrim('123Tech123', '123');

would return '123Tech'

rtrim('Techxyxzyyy', 'xyz');

would return 'Tech'

rtrim('Tech6372', '0123456789');

would return 'Tech'

The rtrim function may appear to remove patterns, but this is not the case as demonstrated in the following example. rtrim('Techxyxzyyy', 'xyz');

would return 'Tech'

It actually removes the individual occurrences of 'x', 'y', and 'z', as opposed to the pattern of 'xyz'.

The rtrim function can also be used to remove all trailing numbers as demonstrated in the next example. rtrim('Tech6372', '0123456789');

would return 'Tech'

In this example, every number combination from 0 to 9 has been listed in the trim_string parameter. By doing this, it does not matter the order that the numbers appear in string1, all trailing numbers will be removed by the rtrim function.

Soundex Function In Oracle/PLSQL, the soundex function returns a phonetic representation (the way it sounds) of a string. The syntax for the soundex function is: soundex( string1 ) string1 is the string whose phonetic value will be returned.

The Soundex algorithm is as follows:

1.

The soundex return value will always begin with the first letter of string1.

2.

The soundex function uses only the first 5 consonants to determine the NUMERIC portion of the return value, except if the first letter of string1 is a vowel.

3.

The soundex function is not case-sensitive. What this means is that both uppercase and lowercase characters will generate the same soundex return value.

14

none GE Confidential

For example: soundex('tech on the net');

would return 'T253'

soundex('TECH ON THE NET');

would return 'T253'

soundex('apples');

would return 'A142'

soundex('apples are great');

would return 'A142'

soundex('applus');

would return 'A142'

Substr Function In Oracle/PLSQL, the substr functions allows you to extract a substring from a string. The syntax for the substr function is: substr( string, start_position, [ length ] ) string is the source string. start_position is the position for extraction. The first position in the string is always 1. length is optional. It is the number of characters to extract. If this parameter is omitted, substr will return the entire string.

Note: If start_position is 0, then substr treats start_position as 1 (ie: the first position in the string). If start_position is a positive number, then substr starts from the beginning of the string. If start_position is a negative number, then substr starts from the end of the string and counts backwards. If length is a negative number, then substr will return a NULL value.

For example: substr('This is a test', 6, 2)

would return 'is'

substr('This is a test', 6)

would return 'is a test'

substr('TechOnTheNet', 1, 4)

would return 'Tech'

substr('TechOnTheNet', -3, 3)

would return 'Net'

substr('TechOnTheNet', -6, 3)

would return 'The'

substr('TechOnTheNet', -8, 2)

would return 'On'

Translate Function 15

none GE Confidential

In Oracle/PLSQL, the translate function replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time. For example, it will replace the 1st character in the string_to_replace with the 1st character in the replacement_string. Then it will replace the 2nd character in the string_to_replace with the 2nd character in the replacement_string, and so on. The syntax for the translate function is: translate( string1, string_to_replace, replacement_string ) string1 is the string to replace a sequence of characters with another set of characters. string_to_replace is the string that will be searched for in string1. replacement_string - All characters in the string_to_replace will be replaced with the corresponding character in the replacement_string.

For example: translate('1tech23', '123', '456);

would return '4tech56'

translate('222tech, '2ec', '3it');

would return '333tith'

Trim Function In Oracle/PLSQL, the trim function removes all specified characters either from the beginning or the ending of a string. The syntax for the trim function is: trim( [ leading | trailing | both [ trim_character ] ] string1 ) leading - remove trim_string from the front of string1. trailing - remove trim_string from the end of string1. both - remove trim_string from the front and end of string1. If none of these are chosen (ie: leading, trailing, both), the trim function will remove trim_string from both the front and end of string1.

trim_character is the character that will be removed from string1. If this parameter is omitted, the trim function will remove all leading and trailing spaces from string1. string1 is the string to trim.

For example:

16

none GE Confidential

trim(' tech ')

would return 'tech'

trim(' ' from ' tech ')

would return 'tech'

trim(leading '0' from '000123')

would return '123'

trim(trailing '1' from 'Tech1')

would return 'Tech'

trim(both '1' from '123Tech111')

would return '23Tech'

Upper Function In Oracle/PLSQL, the upper function converts all letters in the specified string to uppercase. If there are characters in the string that are not letters, they are unaffected by this function. The syntax for the upper function is: upper( string1 ) string1 is the string to convert to uppercase.

For example: upper('Tech on the Net');

would return 'TECH ON THE NET'

upper('george burns 123 ');

would return 'GEORGE BURNS 123 '

VSize Function In Oracle/PLSQL, the vsize function returns the number of bytes in the internal representation of an expression. The syntax for the vsize function is: vsize( expression ) expression is the string to evaluate.

For example: vsize('Tech on the net')

would return 15

vsize('Tech on the net ')

would return 16

vsize(null)

would return

vsize('')

would return

vsize(' ')

would return 1

2. Conversion Functions: 17

none GE Confidential

Bin_To_Num Function In Oracle/PLSQL, the bin_to_num function converts a bit vector to a number. The syntax for the bin_to_num function is: bin_to_num( expr1, expr2, ... expr_n) expr1, expr2, ... expr_n must be either 0 or 1 values. They represent bits in a bit vector.

For example: bin_to_num(1)

would return 1

bin_to_num(1,0)

would return 2

bin_to_num(1,1)

would return 3

bin_to_num(1,1,1,0)

would return 14

bin_to_num(1,1,1,1)

would return 15

Cast Function In Oracle/PLSQL, the cast function converts one datatype to another. The syntax for the cast function is: cast ( { expr | ( subquery ) | MULTISET ( subquery ) } AS type_name )

The following casts are allowed: TO

FROM char, varchar2

number

datetime / interval

raw

rowid, urowid

char, varchar2

X

X

X

X

X

number

X

X

datetime / interval

X

raw

X

nchar, nvarchar2

X X

18

none GE Confidential

rowid, urowid

X

nchar, nvarchar2

X X

X

X

X

X

For example: select cast( '22-Aug-2003' AS varchar2(30) ) from dual; This would convert the date (ie: 22-Aug-2003) into a varchar2(30) value.

CharToRowid Function In Oracle/PLSQL, the chartorowid function converts a char, varchar2, nchar, or nvarchar2 to a rowid. The syntax for the chartorowid function is: chartorowid( value1 ) value1 is the value to convert to a rowid. The format of the rowid is: BBBBBBB.RRRR.FFFFF where: BBBBBBB is the block in the database file; RRRR is the row in the block; FFFFF is the database file.

For example: select * from suppliers where rowid = chartorowid('AAABoqAADAAAAwPAAA'); This would return a unique row from the suppliers table.

From_Tz function In Oracle/PLSQL, the from_tz function converts a TIMESTAMP value (given a TIME ZONE) to a TIMESTAMP WITH TIME ZONE value. The syntax for the from_tz function is: from_tz( timestamp_value, time_zone_value )

19

none GE Confidential

timestamp_value is the value to convert to a TIMESTAMP WITH TIME ZONE value. time_zone_value is the time zone used to convert timestamp_value to a TIMESTAMP WITH TIME ZONE value.

For example: If you executed the following SQL statement: select from_tz(TIMESTAMP '2005-09-11 01:50:42', '5:00') from dual; You would now get the following result: 11-Sep-05 01.50.42.000000 AM +05:00

If you executed the same SQL statement, but set the time zone to '-7:00' as follows: select from_tz(TIMESTAMP '2005-09-11 01:50:42', '-7:00') from dual; You would now get the following result: 11-Sep-05 01.50.42.000000 AM -07:00

HexToRaw Function In Oracle/PLSQL, the hextoraw function converts a hexadecimal value into a raw value. The syntax for the hextoraw function is: hextoraw( char ) char is the hexademical value to convert to a raw value.

For example: hextoraw('45D')

would return '045D' as a raw value

hextoraw('7E')

would return '7E' as a raw value

NumToDSInterval Function In Oracle/PLSQL, the numtodsinterval function converts a number to an INTERVAL DAY TO SECOND literal.

20

none GE Confidential

The syntax for the numtodsinterval function is: numtodsinterval( number, expression ) number is the number to convert to an interval. expression is the unit. It must be one of the following values: DAY, HOUR, MINUTE, or SECOND.

For example: numtodsinterval(150, 'DAY')

would return '+000000150'

numtodsinterval(1500, 'HOUR')

would return '+000000062'

numtodsinterval(15000, 'MINUTE')

would return '+000000010'

numtodsinterval(150000, 'SECOND')

would return '+000000001'

NumToYMInterval Function In Oracle/PLSQL, the numtoyminterval function converts a number to an INTERVAL YEAR TO MONTH literal. The syntax for the numtoyminterval function is: numtoyminterval( number, expression ) number is the number to convert to an interval. expression is the unit. It must be one of the following values: YEAR or MONTH.

For example: numtoyminterval(100000000, 'MONTH')

would return '+0083'

numtoyminterval(100000, 'YEAR')

would return '+0001'

To_Char Function In Oracle/PLSQL, the to_char function converts a number or date to a string. The syntax for the to_char function is: to_char( value, [ format_mask ], [ nls_language ] ) value can either be a number or date that will be converted to a string. format_mask is optional. This is the format that will be used to convert value to a string.

21

none GE Confidential

nls_language is optional. This is the nls language used to convert value to a string.

Examples - Numbers The following are number examples for the to_char function. to_char(1210.73, '9999.9')

would return '1210.7'

to_char(1210.73, '9,999.99')

would return '1,210.73'

to_char(1210.73, '$9,999.00')

would return '$1,210.73'

to_char(21, '000099')

would return '000021'

Examples - Dates The following is a list of valid parameters when the to_char function is used to convert a date to a string. These parameters can be used in many combinations. Parameter

Explanation

YEAR

Year, spelled out

YYYY

4-digit year

YYY YY Y

Last 3, 2, or 1 digit(s) of year.

IYY IY I

Last 3, 2, or 1 digit(s) of ISO year.

IYYY

4-digit year based on the ISO standard

Q

Quarter of year (1, 2, 3, 4; JAN-MAR = 1).

MM

Month (01-12; JAN = 01).

MON

Abbreviated name of month.

MONTH

Name of month, padded with blanks to length of 9 characters.

RM

Roman numeral month (I-XII; JAN = I).

WW

Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.

W

Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.

IW

Week of year (1-52 or 1-53) based on the ISO standard.

D

Day of week (1-7).

DAY

Name of day.

DD

Day of month (1-31).

DDD

Day of year (1-366).

22

none GE Confidential

DY

Abbreviated name of day.

J

Julian day; the number of days since January 1, 4712 BC.

HH

Hour of day (1-12).

HH12

Hour of day (1-12).

HH24

Hour of day (0-23).

MI

Minute (0-59).

SS

Second (0-59).

SSSSS

Seconds past midnight (0-86399).

FF

Fractional seconds.

The following are date examples for the to_char function. to_char(sysdate, 'yyyy/mm/dd');

would return '2003/07/09'

to_char(sysdate, 'Month DD, YYYY');

would return 'July 09, 2003'

to_char(sysdate, 'FMMonth DD, YYYY');

would return 'July 9, 2003'

to_char(sysdate, 'MON DDth, YYYY');

would return 'JUL 09TH, 2003'

to_char(sysdate, 'FMMON DDth, YYYY');

would return 'JUL 9TH, 2003'

to_char(sysdate, 'FMMon ddth, YYYY');

would return 'Jul 9th, 2003'

You will notice that in some examples, the format_mask parameter begins with "FM". This means that zeros and blanks are suppressed. This can be seen in the examples below. to_char(sysdate, 'FMMonth DD, YYYY');

would return 'July 9, 2003'

to_char(sysdate, 'FMMON DDth, YYYY');

would return 'JUL 9TH, 2003'

to_char(sysdate, 'FMMon ddth, YYYY');

would return 'Jul 9th, 2003'

The zeros have been suppressed so that the day component shows as "9" as opposed to "09".

Frequently Asked Questions Question: Why doesn't this sort the day's of the week in order? select ename, hiredate, to_char((hiredate),'fmDay') "Day" from emp order by "Day"; Answer: The fmDay parameter will return the name of the Day and not the numeric value of the day. Try the following:

23

none GE Confidential

select ename, hiredate, to_char((hiredate),'fmDD') "Day" from emp order by "Day";

To_Clob Function In Oracle/PLSQL, the to_clob function converts a LOB value from the national character set to the database character set. The syntax for the to_clob function is: to_clob( expression ) expression can either be a lob_column, char, varchar2, nchar, nvarchar2, clob or nclob value.

For example: select to_clob(nclob_column) from suppliers; This example would convert the value in the field called nclob_column to a CLOB value.

To_Date Function In Oracle/PLSQL, the to_date function converts a string to a date. The syntax for the to_date function is: to_date( string1, [ format_mask ], [ nls_language ] ) string1 is the string that will be converted to a date. format_mask is optional. This is the format that will be used to convert string1 to a date. nls_language is optional. This is the nls language used to convert string1 to a date.

The following is a list of options for the format_mask parameter. These parameters can be used in many combinations. Parameter

Explanation

YEAR

Year, spelled out

YYYY

4-digit year

24

none GE Confidential

YYY YY Y

Last 3, 2, or 1 digit(s) of year.

IYY IY I

Last 3, 2, or 1 digit(s) of ISO year.

IYYY

4-digit year based on the ISO standard

RRRR

Accepts a 2-digit year and returns a 4-digit year. A value between 0-49 will return a 20xx year. A value between 50-99 will return a 19xx year.

Q

Quarter of year (1, 2, 3, 4; JAN-MAR = 1).

MM

Month (01-12; JAN = 01).

MON

Abbreviated name of month.

MONTH

Name of month, padded with blanks to length of 9 characters.

RM

Roman numeral month (I-XII; JAN = I).

WW

Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.

W

Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.

IW

Week of year (1-52 or 1-53) based on the ISO standard.

D

Day of week (1-7).

DAY

Name of day.

DD

Day of month (1-31).

DDD

Day of year (1-366).

DY

Abbreviated name of day.

J

Julian day; the number of days since January 1, 4712 BC.

HH

Hour of day (1-12).

HH12

Hour of day (1-12).

HH24

Hour of day (0-23).

MI

Minute (0-59).

SS

Second (0-59).

SSSSS

Seconds past midnight (0-86399).

FF

Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. For example, 'FF4'.

AM, A.M., PM, or P.M. Meridian indicator AD or A.D

AD indicator

BC or B.C.

BC indicator

TZD

Daylight savings information. For example, 'PST'

TZH

Time zone hour.

25

none GE Confidential

TZM

Time zone minute.

TZR

Time zone region.

For example: to_date('2003/07/09', 'yyyy/mm/dd')

would return a date value of July 9, 2003.

to_date('070903', 'MMDDYY')

would return a date value of July 9, 2003.

to_date('20020315', 'yyyymmdd')

would return a date value of Mar 15, 2002.

To_Multi_Byte Function In Oracle/PLSQL, the to_multi_byte function returns a character value with all of the single-byte characters converted to multibyte characters. To use this function, your database character set contains both singlebyte and multibyte characters. The syntax for the to_multi_byte function is: to_multi_byte( char ) char can be a char, varchar2, nchar, or nvarchar2 value. This function will return its result in the same character set as char.

For example: select to_multi_byte('Tech on the net') from dual; The SQL statement above would return a multibyte character value.

To_NClob Function In Oracle/PLSQL, the to_nclob function converts a LOB value to a NCLOB value. The syntax for the to_nclob function is: to_nclob( expression ) expression can be a char, varchar2, nchar, nvarchar2, clob or nclob value.

For example: select to_nclob(lob_column) from suppliers;

26

none GE Confidential

This example would convert the value in the field called lob_column to an NCLOB value.

To_Number Function In Oracle/PLSQL, the to_number function converts a string to a number. The syntax for the to_number function is: to_number( string1, [ format_mask ], [ nls_language ] ) string1 is the string that will be converted to a number. format_mask is optional. This is the format that will be used to convert string1 to a number. nls_language is optional. This is the nls language used to convert string1 to a number.

For example: to_number('1210.73', '9999.99')

would return the number 1210.73

to_number('546', '999')

would return the number 546

to_number('23', '99')

would return the number 23

Since the format_mask and nls_language parameters are optional, you can simply convert a text string to a numeric value as follows: to_number('1210.73'')

would return the number 1210.73

To_DSInterval Function In Oracle/PLSQL, the to_dsinterval function converts a string to an INTERVAL DAY TO SECOND type. The syntax for the to_dsinterval function is: to_dsinterval( character [ nls_parameter ] ) character is the value to convert to an INTERVAL DAY TO SECOND type. It can be a char, varchar2, nchar, or nvarchar2 value. nls_parameter is a decimal character and group separator value.

For example: to_dsinterval('150 08:30:00')

would return '+000000150'

27

none GE Confidential

to_dsinterval('80 12:30:00')

would return '+000000080'

to_dsinterval('95 18:30:00')

would return '+000000095'

To_Single_Byte Function In Oracle/PLSQL, the to_single_byte function returns a character value with all of the multibyte characters converted to single-byte characters. To use this function, your database character set contains both singlebyte and multibyte characters. The syntax for the to_single_byte function is: to_single_byte( char ) char can be a char, varchar2, nchar, or nvarchar2 value. This function will return its result in the same character set as char.

For example: select to_single_byte('Tech on the net') from dual; The SQL statement above would return a single-byte character value.

To_Lob Function In Oracle/PLSQL, the to_lob function converts LONG or LONG RAW values to LOB values. The syntax for the to_lob function is: to_lob( long_column ) long_column can be a LONG or LONG RAW value.

For example: insert into companies (lob_column) select to_lob(long_column) from suppliers; This example would convert the value in the field called long_column to a LOB value.

To_Timestamp Function In Oracle/PLSQL, the to_timestamp function converts a string to a timestamp.

28

none GE Confidential

The syntax for the to_timestamp function is: to_timestamp( string1, [ format_mask ] [ 'nlsparam' ] ) string1 is the string that will be converted to a timestamp. format_mask is optional. This is the format that will be used to convert string1 to a timestamp.

The following is a list of options for the format_mask parameter. These parameters can be used in many combinations. Parameter

Explanation

YYYY

4-digit year

MM

Month (01-12; JAN = 01).

MON

Abbreviated name of month.

MONTH

Name of month, padded with blanks to length of 9 characters.

DD

Day of month (1-31).

HH

Hour of day (1-12).

HH12

Hour of day (1-12).

HH24

Hour of day (0-23).

MI

Minute (0-59).

SS

Second (0-59).

For example: to_timestamp('2003/12/13 10:13:18', 'YYYY/MM/DD HH:MI:SS') would return '13-DEC-03 10.13.18.000000000 AM' as a timestamp value.

to_timestamp('2003/DEC/13 10:13:18', 'YYYY/MON/DD HH:MI:SS') would also return '13-DEC-03 10.13.18.000000000 AM' as a timestamp value.

To_Timestamp_Tz Function In Oracle/PLSQL, the to_timestamp_tz function converts a string to a timestamp with time zone. The syntax for the to_timestamp_tz function is: to_timestamp_tz( string1 , [ format_mask ] [ 'nlsparam' ] )

29

none GE Confidential

string1 is the string that will be converted to a timestamp with time zone. format_mask is optional. This is the format that will be used to convert string1 to a timestamp with time zone.

The following is a list of options for the format_mask parameter These parameters can be used in many combinations. Parameter

Explanation

YYYY

4-digit year

MM

Month (01-12; JAN = 01).

MON

Abbreviated name of month.

MONTH

Name of month, padded with blanks to length of 9 characters.

DD

Day of month (1-31).

HH

Hour of day (1-12).

HH12

Hour of day (1-12).

HH24

Hour of day (0-23).

MI

Minute (0-59).

SS

Second (0-59).

TZM

Time zone minute.

TZH

Time zone hour.

For example: to_timestamp_tz('2003/12/13 10:13:18 -8:00', 'YYYY/MM/DD HH:MI:SS TZH:TZM') would return '13-DEC-03 10.13.18.000000000 AM -08:00' as a timestamp with time zone value.

to_timestamp_tz('2003/DEC/13 10:13:18 -8:00', 'YYYY/MON/DD HH:MI:SS TZH:TZM') would also return '13-DEC-03 10.13.18.000000000 AM -08:00' as a timestamp with time zone value.

To_YMInterval Function In Oracle/PLSQL, the to_yminterval function converts a string to an INTERVAL YEAR TO MONTH type. The syntax for the to_yminterval function is: to_yminterval( character ) character is the value to convert to an INTERVAL YEAR TO MONTH type. It can be a char, varchar2, nchar, or nvarchar2 value.

30

none GE Confidential

For example: to_yminterval('03-11')

would return 3 years 11 months as an INTERVAL YEAR TO MONTH type

to_yminterval('01-05')

would return 1 year 5 months as an INTERVAL YEAR TO MONTH type

to_yminterval('00-01')

would return 0 years 1 month as an INTERVAL YEAR TO MONTH type

The to_yminterval function is most commonly used to add an interval to a date field. For example, you may wish to add 1 year and 5 months to an order date. select order_date, order_date + to_yminterval('01-05') from orders; This SQL statement would return the order date, as well as the order date plus 1 year and 5 months.

3. Advanced Functions: BFilename Function In Oracle/PLSQL, the bfilename function returns a BFILE locator for a physical LOB binary file. The syntax for the bfilename function is: bfilename( 'directory', 'filename' ) directory is a directory object that serves as an alias for the full path to where the file is located on the file server. filename is the name of the file on the file server.

For example: First, we need to create a directory object called exampleDir that points to /example/totn on the file server. CREATE DIRECTORY exampleDir AS '/example/totn'; Then we can use the exampleDir directory object in the bfilename function as follows: SELECT bfilename('exampleDir', 'totn_logo.jpg') FROM dual;

Cardinality Function (NEW in Oracle 10)

31

none GE Confidential

In Oracle/PLSQL, the cardinality function returns the number of elements in a nested table. The syntax for the cardinality function is: cardinality( nested table column ) nested table column is the column in the nested table that you wish to return the cardinality for.

Note: If the nested table is empty, the cardinality function will return NULL. If the nested table is a null collection, the cardinality function will return NULL.

For example: select supplier_id, cardinality(location) from suppliers;

Case Statement In Oracle 9i, you can use the case statement within an SQL statement. It has the functionality of an IFTHEN-ELSE statement. The syntax for the case statement is: CASE expression WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 ... WHEN condition_n THEN result_n ELSE result END expression is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n) condition_1 to condition_n must all be the same datatype. Conditions are evaluated in the order listed. Once a condition is found to be true, the case statement will return the result and not evaluate the conditions any further. result_1 to result_n must all be the same datatype. This is the value returned once a condition is found to be true.

Note:

32

none GE Confidential

If no condition is found to be true, then the case statement will return the value in the ELSE clause. If the ELSE clause is omitted and no condition is found to be true, then the case statement will return NULL. You can have up to 255 comparisons in a case statement. Each WHEN ... THEN clause is considered 2 comparisons.

For Example: You could use the case statement in an SQL statement as follows: select table_name, CASE owner WHEN 'SYS' THEN 'The owner is SYS' WHEN 'SYSTEM' THEN 'The owner is SYSTEM' ELSE 'The owner is another value' END from all_tables;

The above case statement is equivalent to the following IF-THEN-ELSE statement: IF owner = 'SYS' THEN result := 'The owner is SYS'; ELSIF owner = 'SYSTEM' THEN result := 'The owner is SYSTEM''; ELSE result := 'The owner is another value'; END IF;

The case statement will compare each owner value, one by one.

One thing to note is that the ELSE clause within the case statement is optional. You could have omitted it. Let's take a look at the SQL statement above with the ELSE clause omitted. Your SQL statement would look as follows: select table_name, CASE owner WHEN 'SYS' THEN 'The owner is SYS' WHEN 'SYSTEM' THEN 'The owner is SYSTEM' END from all_tables; With the ELSE clause omitted, if no condition was found to be true, the case statement would return NULL.

33

none GE Confidential

Coalesce Function In Oracle/PLSQL, the coalesce function returns the first non-null expression in the list. If all expressions evaluate to null, then the coalesce function will return null. The syntax for the coalesce function is: coalesce( expr1, expr2, ... expr_n )

For Example: You could use the coalesce function in an SQL statement as follows: SELECT coalesce( address1, address2, address3 ) result FROM suppliers;

The above coalesce statement is equivalent to the following IF-THEN-ELSE statement: IF address1 is not null THEN result := address1; ELSIF address2 is not null THEN result := address2; ELSIF address3 is not null THEN result := address3; ELSE result := null; END IF;

The coalesce function will compare each value, one by one.

Decode Function In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement. The syntax for the decode function is: decode( expression , search , result [, search , result]... [, default] ) expression is the value to compare. search is the value that is compared against expression.

34

none GE Confidential

result is the value returned, if expression is equal to search. default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).

For Example: You could use the decode function in an SQL statement as follows: SELECT supplier_name, decode(supplier_id, 10000, 'IBM', 10001, 'Microsoft', 10002, 'Hewlett Packard', 'Gateway') result FROM suppliers;

The above decode statement is equivalent to the following IF-THEN-ELSE statement: IF supplier_id = 10000 THEN result := 'IBM'; ELSIF supplier_id = 10001 THEN result := 'Microsoft'; ELSIF supplier_id = 10002 THEN result := 'Hewlett Packard'; ELSE result := 'Gateway'; END IF;

The decode function will compare each supplier_id value, one by one.

Frequently Asked Questions Question: One of our viewers wanted to know how to use the decode function to compare two dates (ie: date1 and date2), where if date1 > date2, the decode function should return date2. Otherwise, the decode function should return date1. Answer: To accomplish this, use the decode function as follows: decode((date1 - date2) - abs(date1 - date2), 0, date2, date1) The formula below would equal 0, if date1 is greater than date2:

35

none GE Confidential

(date1 - date2) - abs(date1 - date2)

Question: I would like to know if it's possible to use decode for ranges of numbers, ie 1-10 = 'category 1', 11-20 = 'category 2', rather than having to individually decode each number. Answer: Unfortunately, you can not use the decode for ranges of numbers. However, you can try to create a formula that will evaluate to one number for a given range, and another number for the next range, and so on. For example: SELECT supplier_id, decode(trunc ((supplier_id - 1) / 10),

0, 1, 2,

'category 1', 'category 2', 'category 3', 'unknown') result

FROM suppliers; In this example, based on the formula: trunc ((supplier_id - 1) / 10 The formula will evaluate to 0, if the supplier_id is between 1 and 10. The formula will evaluate to 1, if the supplier_id is between 11 and 20. The formula will evaluate to 2, if the supplier_id is between 21 and 30. and so on...

Question: I need to write a decode statement that will return the following: If yrs_of_service < 1 then return 0.04 If yrs_of_service >= 1 and < 5 then return 0.04 If yrs_of_service > 5 then return 0.06 How can I do this? Answer: You will need to create a formula that will evaluate to a single number for each one of your ranges. For example: SELECT emp_name, decode(trunc (( yrs_of_service + 3) / 4),

0, 1,

0.04, 0.04, 0.06) as perc_value

FROM employees; Helpful Tip: One of our viewers suggested combining the SIGN function with the DECODE function as follows: The date example above could be modified as follows:

36

none GE Confidential

DECODE(SIGN(date1-date2), 1, date2, date1) The SIGN/DECODE combination is also helpful for numeric comparisons e.g. Sales Bonuses DECODE(SIGN(actual-target), -1, 'NO Bonus for you', 0,'Just made it', 1, 'Congrats, you are a winner')

Group_ID Function In Oracle/PLSQL, the group_id function assigns a number to each group resulting from a GROUP BY clause. The group_id function is most commonly used to identify duplicated groups in your query results. For each unique group, the group_id function will return 0. When a duplicated group is found, the group_id function will return a value >0. The syntax for the group_id function is: select column1, column2, ... column_n, GROUP_ID() from tables where predicates GROUP BY column1, column2, ... column_n;

For example: select sum(salary), department, bonus, GROUP_ID() from employees where bonus > 100 GROUP BY department, ROLLUP (department, bonus);

You could use the HAVING clause to eliminated duplicated groups as follows: select sum(salary), department, bonus, GROUP_ID() from employees where bonus > 100 GROUP BY department, ROLLUP (department, bonus) HAVING GROUP_ID() < 1;

NANVL Function (NEW in Oracle 10) In Oracle/PLSQL, the NANVL function lets you substitute a value for a floating point number such as BINARY_FLOAT or BINARY_DOUBLE, when a Nan (Not a number) value is encountered. This is most commonly used to convert Nan (Not a number) values into either NULL or 0. The syntax for the NANVL function is: NANVL( value, replace_with )

37

none GE Confidential

value is the BINARY_FLOAT or BINARY_NUMBER to test for a Nan (Not a number). replace_with is the value returned if value is Nan (not a number).

Example #1: select NANVL(binary1, 0) from test_table; The SQL statement above would return 0 if the binary1 field contained a Nan (Not a number) value. Otherwise, it would return the binary1 value.

Example #2: select NANVL(binary1, NULL) from test_table; The SQL statement above would return NULL if the binary1 field contained a Nan (Not a number) value. Otherwise, it would return the binary1 value.

NVL Function In Oracle/PLSQL, the NVL function lets you substitute a value when a null value is encountered. The syntax for the NVL function is: NVL( string1, replace_with ) string1 is the string to test for a null value. replace_with is the value returned if string1 is null.

Example #1: select NVL(supplier_city, 'n/a') from suppliers; The SQL statement above would return 'n/a' if the supplier_city field contained a null value. Otherwise, it would return the supplier_city value.

Example #2: select supplier_id, NVL(supplier_desc, supplier_name) from suppliers;

38

none GE Confidential

This SQL statement would return the supplier_name field if the supplier_desc contained a null value. Otherwise, it would return the supplier_desc.

Example #3: select NVL(commission, 0) from sales; This SQL statement would return 0 if the commission field contained a null value. Otherwise, it would return the commission field.

Frequently Asked Questions Question: I tried to use the NVL function through VB to access Oracle DB. To be precise, select NVL(DIstinct (emp_name),'AAA'),................ from................. I got an oracle error when I use distinct clause with NVL, but when I remove distinct it works fine. Answer: It is possible to the use the DISTINCT clause with the NVL function. However, the DISTINCT must come before the use of the NVL function. For example: select distinct NVL(emp_name, 'AAA') from employees; Hope this helps!

Question: Is it possible to use the NVL function with more than one column with the same function call? To be clear, if i need to apply this NVL function to more than one column like this: NVL(column1;column2 ...... , here is the default value for all ) Answer: You will need to make separate NVL function calls for each column. For example: select NVL(table_name, 'not found'), NVL(owner, 'not found') from all_tables;

NVL2 Function In Oracle/PLSQL, the NVL2 function extends the functionality found in the NVL function. It lets you substitutes a value when a null value is encountered as well as when a non-null value is encountered. The syntax for the NVL2 function is: NVL2( string1, value_if_NOT_null, value_if_null )

39

none GE Confidential

string1 is the string to test for a null value. value_if_NOT_null is the value returned if string1 is not null. value_if_null is the value returned if string1 is null.

Example #1: select NVL2(supplier_city, 'Completed', 'n/a') from suppliers; The SQL statement above would return 'n/a' if the supplier_city field contained a null value. Otherwise, it would return the 'Completed'.

Example #2: select supplier_id, NVL2(supplier_desc, supplier_name, supplier_name2) from suppliers; This SQL statement would return the supplier_name2 field if the supplier_desc contained a null value. Otherwise, it would return the supplier_name field.

Sys_Context Function In Oracle/PLSQL, the sys_context function can be used to retrieve information about the Oracle environment. The syntax for the sys_context function is: sys_context( namespace, parameter, [ length ] ) namespace is an Oracle namespace that has already been created. If the namespace of 'USERENV' is used, attributes describing the current Oracle session can be returned. parameter is a valid attribute that has been set using the DBMS_SESSION.set_context procedure. length is optional. It is the length of the return value in bytes. If this parameter is omitted or if an invalid entry is provided, the sys_context function will default to 256 bytes.

The valid parameters for the namespace called 'USERENV' are as follows:

40

none GE Confidential

Parameter

Explanation

Return Length

AUDITED_CURSORID

Returns the cursor ID of the SQL that triggered the audit

N/A

AUTHENTICATION_DATA

Authentication data

256

AUTHENTICATION_TYPE

Describes how the user was authenticated. Can be one of the following values: Database, OS, Network, or Proxy

30

BG_JOB_ID

If the session was established by an Oracle background 30 process, this parameter will return the Job ID. Otherwise, it will return NULL.

CLIENT_IDENTIFIER

Returns the client identifier (global context)

64

CLIENT_INFO

User session information

64

CURRENT_SCHEMA

Returns the default schema used in the current schema

30

CURRENT_SCHEMAID

Returns the identifier of the default schema used in the current 30 schema

CURRENT_SQL

Returns the SQL that triggered the audit event

64

CURRENT_USER

Name of the current user

30

CURRENT_USERID

Userid of the current user

30

DB_DOMAIN

Domain of the database from the DB_DOMAIN initialization parameter

256

DB_NAME

Name of the database from the DB_NAME initialization parameter

30

ENTRYID

Available auditing entry identifier

30

EXTERNAL_NAME

External of the database user

256

FG_JOB_ID

If the session was established by a client foreground process, this parameter will return the Job ID. Otherwise, it will return NULL.

30

GLOBAL_CONTEXT_MEMORY The number used in the System Global Area by the globally accessed context

N/A

HOST

Name of the host machine from which the client has connected 54

INSTANCE

The identifier number of the current instance

IP_ADDRESS

IP address of the machine from which the client has connected 30

ISDBA

Returns TRUE if the user has DBA privileges. Otherwise, it will return FALSE.

30

LANG

The ISO abbreviate for the language

62

LANGUAGE

The language, territory, and character of the session. In the following format: language_territory.characterset

52

NETWORK_PROTOCOL

Network protocol used

256

NLS_CALENDAR

The calendar of the current session

62

NLS_CURRENCY

The currency of the current session

62

30

41

none GE Confidential

NLS_DATE_FORMAT

The date format for the current session

62

NLS_DATE_LANGUAGE

The language used for dates

62

NLS_SORT

BINARY or the linguistic sort basis

62

NLS_TERRITORY

The territory of the current session

62

OS_USER

The OS username for the user logged in

30

PROXY_USER

The name of the user who opened the current session on behalf of SESSION_USER

30

PROXY_USERID

The identifier of the user who opened the current session on behalf of SESSION_USER

30

SESSION_USER

The database user name of the user logged in

30

SESSION_USERID

The database identifier of the user logged in

30

SESSIONID

The identifier of the auditing session

30

TERMINAL

The OS identifier of the current session

10

For example: sys_context('USERENV', 'NLS_DATE_FORMAT')

would return 'RR-MM-DD'

sys_context('USERENV', 'NLS_SORT')

would return 'BINARY'

Uid function In Oracle/PLSQL, the uid function returns the id number for a user's session (the user who is currently logged in). The syntax for the uid function is: uid

For example: select uid into parm_uid from dual; The variable called parm_uid will now contain the id number for the user's session. The value could be 25, for example.

User function In Oracle/PLSQL, the user function returns the user_id from the current Oracle session.

42

none GE Confidential

The syntax for the user function is: user

For example: select user into parm_user_ID from dual; The variable called parm_user_ID will now contain the Oracle user ID from the current Oracle session.

UserEnv Function In Oracle/PLSQL, the userenv function can be used to retrieve information about the current Oracle session. Although this function still exists in Oracle for backwards compatibility, it is recommended that you use the sys_context function instead. The syntax for the userenv function is: userenv( parameter ) parameter is the value to return from the current Oracle session. The possible values are: Parameter

Explanation

CLIENT_INFO

Returns user session information stored using the DBMS_APPLICATION_INFO package

ENTRYID

Available auditing entry identifier

INSTANCE

The identifier number of the current instance

ISDBA

Returns TRUE if the user has DBA privileges. Otherwise, it will return FALSE.

LANG

The ISO abbreviate for the language

LANGUAGE

The language, territory, and character of the session. In the following format: language_territory.characterset

SESSIONID

The identifier of the auditing session

TERMINAL

The OS identifier of the current session

For example: userenv('ENTRYID')

would return FALSE

userenv('LANGUAGE')

would return 'AMERICAN_AMERICA.WE8DEC'

43

none GE Confidential

4. Mathematical Functions: Abs Function In Oracle/PLSQL, the abs function returns the absolute value of a number. The syntax for the abs function is: abs( number ) number is the number to convert to an absolute value.

For example: abs(-23)

would return 23

abs(-23.6)

would return 23.6

abs(-23.65)

would return 23.65

abs(23.65)

would return 23.65

abs(23.65 * -1)

would return 23.65

Acos Function In Oracle/PLSQL, the acos function returns the arc cosine of a number. The syntax for the acos function is: acos( number ) number is the number used to calculate the arc cosine.

For example: acos(0.2)

would return 1.36943840600457

acos(0.35)

would return 1.21322522314939

acos(-0.15)

would return 1.72136459957158

44

none GE Confidential

Asin Function In Oracle/PLSQL, the asin function returns the arc sine of a number. The syntax for the asin function is: asin( number ) number is the number used to calculate the arc sine.

For example: asin(0.2)

would return 0.201357920790331

asin(0.35)

would return 0.35757110364551

asin(-0.15)

would return -0.150568272776686

Atan Function In Oracle/PLSQL, the atan function returns the arc tangent of a number. The syntax for the atan function is: atan( number ) number is the number used to calculate the arc tangent.

For example: atan(0.2)

would return 0.197395559849881

atan(0.35)

would return 0.336674819386727

atan(-0.15)

would return -0.148889947609497

Atan2 Function In Oracle/PLSQL, the atan2 function returns the arc tangent of n and m. The syntax for the atan2 function is: atan2( n, m ) n and m are the numbers used to calculate the arc tangent.

45

none GE Confidential

For example: atan2(0.2, 0.3)

would return 0.588002603547568

atan2(0.35, -0.15)

would return 1.97568811307998

atan2(-0.3, -0.4)

would return -2.49809154479651

Avg Function In Oracle/PLSQL, the Avg function returns the average value of an expression. The basic syntax when using the Avg function is: SELECT Avg( expression ) FROM tables WHERE predicates; expression can be a numeric field or formula.

Simple Example For example, you might wish to know how the average salary of all employees whose salary is above $25,000 / year. SELECT Avg(salary) as "Avg Salary" FROM employees WHERE salary > 25000;

Example using DISTINCT You can use the DISTINCT clause within the Avg function. For example, the SQL statement below returns the average salary of unique salary values where the salary is above $25,000 / year. SELECT Avg(DISTINCT salary) as "Avg Salary" FROM employees WHERE salary > 25000; If there were two salaries of $30,000/year, only one of these values were be used in the Avg function.

Example using a Formula The expression contained within the Avg function does not need to be a single field. You could also use a formula. For example, you might want the average commission. SELECT Avg(sales * 0.10) as "Average Commission" FROM orders;

46

none GE Confidential

Example using GROUP BY You could also use the Avg function to return the name of the department and the average sales (in the associated department). For example, SELECT department, Avg(sales) as "Avg sales" FROM order_details GROUP BY department; Because you have listed one column in your SELECT statement that is not encapsulated in the Avg function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

Bin_To_Num Function In Oracle/PLSQL, the bin_to_num function converts a bit vector to a number. The syntax for the bin_to_num function is: bin_to_num( expr1, expr2, ... expr_n) expr1, expr2, ... expr_n must be either 0 or 1 values. They represent bits in a bit vector.

For example: bin_to_num(1)

would return 1

bin_to_num(1,0)

would return 2

bin_to_num(1,1)

would return 3

bin_to_num(1,1,1,0)

would return 14

bin_to_num(1,1,1,1)

would return 15

BitAnd Function In Oracle/PLSQL, the bitand function returns an integer representing an AND operation on the bits of expr1 and expr2. The syntax for the bitand function is: bitand( expr1, expr2 ) expr1 and expr2 must resolve to non-negative integers.

For example:

47

none GE Confidential

bitand(5,3)

would return 1

bitand(15,7)

would return 7

bitand(5,2)

would return 0

bitand(5,0)

would return 0

bitand(6,2)

would return 2

Ceil Function In Oracle/PLSQL, the ceil function returns the smallest integer value that is greater than or equal to a number. The syntax for the ceil function is: ceil( number ) number is the value used to find the smallest integer value.

For example: ceil(32.65)

would return 33.

ceil(32)

would return 32.

ceil(-32.65)

would return -32.

ceil(-32)

would return -32.

Corr Function In Oracle/PLSQL, the corr function returns the coefficient of correlation of a set of number pairs. The syntax for the corr function is: corr( n ,m ) [ over (analytic_clause) ] n and m are the numbers to use to calculate the cofficient of correlation. The corr function requires at least two rows.

For example: select corr(quantity, commission) from sales;

48

none GE Confidential

select max_entents, corr(max_trans, initial_extent) from data group by max_extents;

Cos Function In Oracle/PLSQL, the cos function returns the cosine of a number. The syntax for the cos function is: cos( number ) number is the number used to calculate the cosine.

For example: cos(0.2)

would return 0.980066577841242

cos(0.35)

would return 0.939372712847379

cos(-3.15)

would return -0.999964658471342

cos(200)

would return 0.487187675007006

Cosh Function In Oracle/PLSQL, the cosh function returns the hyperbolic cosine of a number. The syntax for the cosh function is: cosh( number ) number is the number used to calculate the hyperbolic cosine.

For example: cosh(0.2)

would return 1.02006675561908

cosh(0.35)

would return 1.06187781915599

cosh(-3.15)

would return 11.6894583539049

cosh(200)

would return 3.61298688406287E86

cosh(0)

1

Covar_pop Function

49

none GE Confidential

In Oracle/PLSQL, the covar_pop function returns the population covariance of a set of number pairs. The syntax for the covar_pop function is: covar_pop( expression1, expression2 ) expression1 is a numeric expression. expression2 is a numeric expression. The covar_pop function eliminates all pairs where expression1 or expression2 has a null value.

For example: select owner, covar_pop(avg_row_len, avg_space) from all_tables group by owner;

Covar_samp Function In Oracle/PLSQL, the covar_samp function returns the sample covariance of a set of number pairs. The syntax for the covar_samp function is: covar_samp( expression1, expression2 ) expression1 is a numeric expression. expression2 is a numeric expression. The covar_samp function eliminates all pairs where expression1 or expression2 has a null value.

For example: select owner, covar_samp(avg_row_len, avg_space) from all_tables group by owner;

COUNT Function The COUNT function returns the number of rows in a query. The syntax for the COUNT function is: SELECT COUNT(expression) FROM tables WHERE predicates;

50

none GE Confidential

Simple Example For example, you might wish to know how many employees have a salary that is above $25,000 / year. SELECT COUNT(*) as "Number of employees" FROM employees WHERE salary > 25000; In this example, we've aliased the count(*) field as "Number of employees". As a result, "Number of employees" will display as the field name when the result set is returned.

Example using DISTINCT You can use the DISTINCT clause within the COUNT function. For example, the SQL statement below returns the number of unique departments where at least one employee makes over $25,000 / year. SELECT COUNT(DISTINCT department) as "Unique departments" FROM employees WHERE salary > 25000; Again, the count(DISTINCT department) field is aliased as "Unique departments". This is the field name that will display in the result set.

Example using GROUP BY In some cases, you will be required to use a GROUP BY clause with the COUNT function. For example, you could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year. SELECT department, COUNT(*) as "Number of employees" FROM employees WHERE salary > 25000 GROUP BY department; Because you have listed one column in your SELECT statement that is not encapsulated in the COUNT function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

TIP: Performance Tuning Since the COUNT function will return the same results regardless of what field(s) you include as the COUNT function parameters (ie: within the brackets), you can change the syntax of the COUNT function to COUNT(1) to get better performance as the database engine will not have to fetch back the data fields.

51

none GE Confidential

For example, based on the example above, the following syntax would result in better performance: SELECT department, COUNT(1) as "Number of employees" FROM employees WHERE salary > 25000 GROUP BY department; Now, the COUNT function does not need to retrieve all fields from the employees table as it had to when you used the COUNT(*) syntax. It will merely retrieve the numeric value of 1 for each record that meets your criteria.

Cume_Dist Function In Oracle/PLSQL, the cume_dist function returns the cumulative distribution of a value in a group of values. The cume_dist function will return a value that is >0 and <=1. The cume_dist function can be used two ways - as an Aggregate function or as an Analytic function.

Syntax #1 - Used as an Aggregate Function As an Aggregate function, the cume_dist returns the relative position of a row within a group of rows. The syntax for the cume_dist function when used as an Aggregate function is: cume_dist( expression1, ... expression_n ) WITHIN GROUP ( ORDER BY expression1, ... expression_n ) expression1 .. expression_n can be one or more expressions which identify a unique row in the group.

Note: There must be the same number of expressions in the first expression list as there is in the ORDER BY clause. The expression lists match by position so the data types must be compatible between the expressions in the first expression list as in the ORDER BY clause.

For Example: select cume_dist(1000, 500) WITHIN GROUP (ORDER BY salary, bonus) from employees; The SQL statement above would return the cumulative distribution of an employee with a salary of $1,000 and a bonus of $500 from within the employees table.

52

none GE Confidential

Syntax #2 - Used as an Analytic Function As an Analytic function, the cume_dist returns the relative position of a value within a group of values. The syntax for the cume_dist function when used as an Analytic function is: cume_dist() OVER ( [ query_partition_clause] ORDER BY clause )

For Example: select employee_name, salary, cume_dist() OVER (PARTITION BY department ORDER BY salary) from employees where department = 'Marketing'; The SQL statement above would return the salary percentile for each employee in the Marketing department.

Dense_Rank Function In Oracle/PLSQL, the dense_rank function returns the rank of a row in a group of rows. It is very similar to the rank function. However, the rank function can cause non-consecutive rankings if the tested values are the same. Whereas, the dense_rank function will always result in consecutive rankings. The dense_rank function can be used two ways - as an Aggregate function or as an Analytic function.

Syntax #1 - Used as an Aggregate Function As an Aggregate function, the dense_rank returns the dense rank of a row within a group of rows. The syntax for the dense_rank function when used as an Aggregate function is: dense_rank( expression1, ... expression_n ) WITHIN GROUP ( ORDER BY expression1, ... expression_n ) expression1 .. expression_n can be one or more expressions which identify a unique row in the group.

Note: There must be the same number of expressions in the first expression list as there is in the ORDER BY clause. The expression lists match by position so the data types must be compatible between the expressions in the first expression list as in the ORDER BY clause.

53

none GE Confidential

For Example: select dense_rank(1000, 500) WITHIN GROUP (ORDER BY salary, bonus) from employees; The SQL statement above would return the dense rank of an employee with a salary of $1,000 and a bonus of $500 from within the employees table.

Syntax #2 - Used as an Analytic Function As an Analytic function, the dense_rank returns the rank of each row of a query with respective to the other rows. The syntax for the dense_rank function when used as an Analytic function is: dense_rank() OVER ( [ query_partition_clause] ORDER BY clause )

For Example: select employee_name, salary, dense_rank() OVER (PARTITION BY department ORDER BY salary) from employees where department = 'Marketing'; The SQL statement above would return all employees who work in the Marketing department and then calculate a rank for each unique salary in the Marketing department. If two employees had the same salary, the dense_rank function would return the same rank for both employees.

Exp Function In Oracle/PLSQL, the exp function returns e raised to the nth power, where e = 2.71828183. The syntax for the exp function is: exp( number ) number is the power to raise e to.

For example: exp(3)

would return 20.0855369231877

exp(3.1)

would return 22.1979512814416

exp(-3)

would return 0.0497870683678639

Extract Function 54

none GE Confidential

In Oracle/PLSQL, the extract function extracts a value from a date or interval value. The syntax for the extract function is: EXTRACT ( { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } | { TIMEZONE_HOUR | TIMEZONE_MINUTE } | { TIMEZONE_REGION | TIMEZONE_ABBR } FROM { date_value | interval_value } ) You can only extract YEAR, MONTH, and DAY from a DATE. You can only extract TIMEZONE_HOUR and TIMEZONE_MINUTE from a timestamp with a time zone datatype.

For example: extract(YEAR FROM DATE '2003-08-22')

would return 2003

extract(MONTH FROM DATE '2003-08-22')

would return 8

extract(DAY FROM DATE '2003-08-22')

would return 22

Floor Function In Oracle/PLSQL, the floor function returns the largest integer value that is equal to or less than a number. The syntax for the floor function is: floor( number ) number is the value used to determine the largest integer value that is equal to or less than a number.

For example: floor(5.9)

would return 5

floor(34.29)

would return 34

floor(-5.9)

would return -6

Greatest Function In Oracle/PLSQL, the greatest function returns the greatest value in a list of expressions. The syntax for the greatest function is: greatest( expr1, expr2, ... expr_n )

55

none GE Confidential

expr1, expr2, . expr_n are expressions that are evaluated by the greatest function. If the datatypes of the expressions are different, all expressions will be converted to whatever datatype expr1 is. If the comparison is based on a character comparison, one character is considered greater than another if it has a higher character set value.

For example: greatest(2, 5, 12, 3)

would return 12

greatest('2', '5', '12', '3')

would return '5'

greatest('apples', 'oranges', 'bananas')

would return 'oranges'

greatest('apples', 'applis', 'applas')

would return 'applis'

Least Function In Oracle/PLSQL, the least function returns the smallest value in a list of expressions. The syntax for the least function is: least( expr1, expr2, ... expr_n ) expr1, expr2, . expr_n are expressions that are evaluated by the greatest function. If the datatypes of the expressions are different, all expressions will be converted to whatever datatype expr1 is. If the comparison is based on a character comparison, one character is considered smaller than another if it has a lower character set value.

For example: least(2, 5, 12, 3)

would return 2

least('2', '5', '12', '3')

would return '12'

least('apples', 'oranges', 'bananas')

would return 'apples'

least('apples', 'applis', 'applas')

would return 'applas'

Ln Function In Oracle/PLSQL, the ln function returns the natural logarithm of a number. The syntax for the ln function is:

56

none GE Confidential

ln( number ) number must be greater than 0.

For example: ln(20)

would return 2.99573227355399

ln(25)

would return 3.2188758248682

ln(100)

would return 4.60517018598809

ln(100.5)

would return 4.61015772749913

Log Function In Oracle/PLSQL, the log function returns the logarithm of n base m. The syntax for the log function is: log( m, n ) m must be a positive number, except 0 or 1. n must be a positive number.

For example: log(10, 20)

would return 1.30102999566398

log(2, 15)

would return 3.90689059560852

log(100, 1)

would return 0

MAX Function The MAX function returns the maximum value of an expression. The syntax for the MAX function is: SELECT MAX(expression ) FROM tables WHERE predicates;

Simple Example For example, you might wish to know the maximum salary of all employees.

57

none GE Confidential

SELECT MAX(salary) as "Highest salary" FROM employees; In this example, we've aliased the max(salary) field as "Highest salary". As a result, "Highest salary" will display as the field name when the result set is returned.

Example using GROUP BY In some cases, you will be required to use a GROUP BY clause with the MAX function. For example, you could also use the MAX function to return the name of each department and the maximum salary in the department. SELECT department, MAX(salary) as "Highest salary" FROM employees GROUP BY department; Because you have listed one column in your SELECT statement that is not encapsulated in the MAX function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

Frequently Asked Questions Question: I'm trying to pull some info out of a table. To simplify, let's say the table (report_history) has 4 columns: user_name, report_job_id, report_name, report_run_date. Each time a report is run in Oracle, a record is written to this table noting the above info. What I am trying to do is pull from this table when the last time each distinct report was run and who ran it last. My initial query: SELECT report_name, max(report_run_date) FROM report_history GROUP BY report_name runs fine. However, it does not provide the name of the user who ran the report. Adding user_name to both the select list and to the group by clause returns multiple lines for each report; the results show the last time each person ran each report in question. (i.e. User1 ran Report 1 on 01-JUL03, User2 ran Report1 on 01-AUG-03). I don't want that....I just want to know who ran a particular report the last time it was run. Any suggestions? Answer: This is where things get a bit complicated. The SQL statement below will return the results that you want:

58

none GE Confidential

SELECT rh.user_name, rh.report_name, rh.report_run_date FROM report_history rh, (SELECT max(report_run_date) as maxdate, report_name FROM report_history GROUP BY report_name) maxresults WHERE rh.report_name = maxresults.report_name AND rh.report_run_date= maxresults.maxdate; Let's take a few moments to explain what we've done. First, we've aliased the first instance of the report_history table as rh. Second, we've included two components in our FROM clause. The first is the table called report_history (aliased as rh). The second is a select statement: (SELECT max(report_run_date) as maxdate, report_name FROM report_history GROUP BY report_name) maxresults We've aliased the max(report_run_date) as maxdate and we've aliased the entire result set as maxresults. Now, that we've created this select statement within our FROM clause, Oracle will let us join these results against our original report_history table. So we've joined the report_name and report_run_date fields between the tables called rh and maxresults. This allows us to retrieve the report_name, max(report_run_date) as well as the user_name.

Question: I need help in an SQL query. I have a table in Oracle called orders which has the following fields: order_no, customer, and amount. I need a query that will return the customer who has ordered the highest total amount. Answer: The following SQL should return the customer with the highest total amount in the orders table. select query1.* from (SELECT customer, Sum(orders.amount) AS total_amt FROM orders GROUP BY orders.customer) query1, (select max(query2.total_amt) as highest_amt from (SELECT customer, Sum(orders.amount) AS total_amt FROM orders GROUP BY orders.customer) query2) query3 where query1.total_amt = query3.highest_amt; This SQL statement will summarize the total orders for each customer and then return the customer with the highest total orders. This syntax is optimized for Oracle and may not work for other database technologies.

Question: I'm trying to retrieve some info from an Oracle database. I've got a table named Scoring with two fields - Name and Score. What I want to get is the highest score from the table and the name of the player. Answer: The following SQL should work:

59

none GE Confidential

SELECT Name, Score FROM Scoring WHERE Score = (select Max(Score) from Scoring);

Median Function (NEW in Oracle 10) In Oracle/PLSQL, the median function returns the median of an expression. The syntax for the median function is: median( expression ) [ OVER ( query partition clause ) ]

Note: The median function is NEW to Oracle 10! In older versions of Oracle, try using the percentile_cont function to calculate the median value.

For example: select median(salary) from employees where department = 'Marketing'; The SQL statement above would return the median salary for all employees in the Marketing department.

MIN Function The MIN function returns the minimum value of an expression. The syntax for the MIN function is: SELECT MIN(expression ) FROM tables WHERE predicates;

Simple Example For example, you might wish to know the minimum salary of all employees. SELECT MIN(salary) as "Lowest salary" FROM employees; In this example, we've aliased the min(salary) field as "Lowest salary". As a result, "Lowest salary" will display as the field name when the result set is returned.

60

none GE Confidential

Example using GROUP BY In some cases, you will be required to use a GROUP BY clause with the MIN function. For example, you could also use the MIN function to return the name of each department and the minimum salary in the department. SELECT department, MIN(salary) as "Lowest salary" FROM employees GROUP BY department; Because you have listed one column in your SELECT statement that is not encapsulated in the MIN function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

Mod Function In Oracle/PLSQL, the mod function returns the remainder of m divided by n. The syntax for the mod function is: mod( m, n ) Returns m if n is 0.

For example: mod(15, 4)

would return 3

mod(15, 0)

would return 15

mod(11.6, 2)

would return 1.6

mod(11.6, 2.1)

would return 1.1

mod(-15, 4)

would return -3

mod(-15, 0)

would return -15

Power Function In Oracle/PLSQL, the power function returns m raised to the nth power. The syntax for the power function is: power( m, n ) m is the base. n is the exponent. If m is negative, then n must be an integer.

61

none GE Confidential

For example: power(3, 2)

would return 9

power(5, 3)

would return 125

power(-5, 3)

would return -125

power(6.2, 3)

would return 238.328

power(6.2, 3.5)

would return 593.431934277892

Rank Function In Oracle/PLSQL, the rank function returns the rank of a value in a group of values. It is very similar to the dense_rank function. However, the rank function can cause non-consecutive rankings if the tested values are the same. Whereas, the dense_rank function will always result in consecutive rankings. The rank function can be used two ways - as an Aggregate function or as an Analytic function.

Syntax #1 - Used as an Aggregate Function As an Aggregate function, the rank returns the rank of a row within a group of rows. The syntax for the rank function when used as an Aggregate function is: rank( expression1, ... expression_n ) WITHIN GROUP ( ORDER BY expression1, ... expression_n ) expression1 .. expression_n can be one or more expressions which identify a unique row in the group.

Note: There must be the same number of expressions in the first expression list as there is in the ORDER BY clause. The expression lists match by position so the data types must be compatible between the expressions in the first expression list as in the ORDER BY clause.

For Example: select rank(1000, 500) WITHIN GROUP (ORDER BY salary, bonus) from employees; The SQL statement above would return the rank of an employee with a salary of $1,000 and a bonus of $500 from within the employees table.

62

none GE Confidential

Syntax #2 - Used as an Analytic Function As an Analytic function, the rank returns the rank of each row of a query with respective to the other rows. The syntax for the rank function when used as an Analytic function is: rank() OVER ( [ query_partition_clause] ORDER BY clause )

For Example: select employee_name, salary, rank() OVER (PARTITION BY department ORDER BY salary) from employees where department = 'Marketing'; The SQL statement above would return all employees who work in the Marketing department and then calculate a rank for each unique salary in the Marketing department. If two employees had the same salary, the rank function would return the same rank for both employees. However, this will cause a gap in the ranks (ie: non-consecutive ranks). This is quite different from the dense_rank function which generates consecutive rankings.

Remainder Function (NEW in Oracle 10) In Oracle/PLSQL, the remainder function returns the remainder of m divided by n. The syntax for the remainder function is: remainder( m, n )

Note: The remainder function is NEW to Oracle 10! In older versions of Oracle, try using the mod function.

For example: remainder(15, 4)

would return 3

remainder(15, 0)

would return 15

remainder(11.6, 2)

would return 1.6

remainder(11.6, 2.1)

would return 1.1

remainder(-15, 4)

would return -3

remainder(-15, 0)

would return -15

Round Function (with numbers) 63

none GE Confidential

In Oracle/PLSQL, the round function returns a number rounded to a certain number of decimal places. The syntax for the round function is: round( number, [ decimal_places ] ) number is the number to round. decimal_places is the number of decimal places rounded to. This value must be an integer. If this parameter is omitted, the round function will round the number to 0 decimal places.

For example: round(125.315)

would return 125

round(125.315, 0)

would return 125

round(125.315, 1)

would return 125.3

round(125.315, 2)

would return 125.32

round(125.315, 3)

would return 125.315

round(-125.315, 2)

would return -125.32

Round Function (with dates) In Oracle/PLSQL, the round function returns a date rounded to a specific unit of measure. The syntax for the round function is: round( date, [ format ] ) date is the date to round. format is the unit of measure to apply for rounding. If the format parameter is omitted, the round function will round to the nearest day.

Below are the valid format parameters: Unit

Valid format parameters

Rounding Rule

Year

SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y

Rounds up on July 1st

ISO Year

IYYY, IY, I

Quarter

Q

Rounds up on the 16th day of the second month of the quarter

Month

MONTH, MON, MM, RM

Rounds up on the 16th day of the month

Week

WW

Same day of the week as the first

64

none GE Confidential

day of the year IW

IW

Same day of the week as the first day of the ISO year

W

W

Same day of the week as the first day of the month

Day

DDD, DD, J

Start day of the week DAY, DY, D Hour

HH, HH12, HH24

Minute

MI

For example: round(to_date ('22-AUG-03'),'YEAR')

would return '01-JAN-04'

round(to_date ('22-AUG-03'),'Q')

would return '01-OCT-03'

round(to_date ('22-AUG-03'),'MONTH')

would return '01-SEP-03'

round(to_date ('22-AUG-03'),'DDD')

would return '22-AUG-03'

round(to_date ('22-AUG-03'),'DAY')

would return '24-AUG-03'

Sign Function In Oracle/PLSQL, the sign function returns a value indicating the sign of a number. The syntax for the sign function is: sign( number ) number is the number to test for its sign. If number < 0, then sign returns -1. If number = 0, then sign returns 0. If number > 0, then sign returns 1.

For example: sign(-23)

would return -1

sign(-0.001)

would return -1

sign(0)

would return 0

sign(0.001)

would return 1

sign(23)

would return 1

sign(23.601)

would return 1

Sin Function 65

none GE Confidential

In Oracle/PLSQL, the sin function returns the sine of n. The syntax for the sin function is: sin( n ) n is a number. It is an angle expressed in radians.

For example: sin(3)

would return 0.141120008059867

sin(5.2)

would return -0.883454655720153

sin(-5.2)

would return 0.883454655720153

Sinh Function In Oracle/PLSQL, the sinh function returns the hyperbolic sine of n. The syntax for the sinh function is: sinh( n ) n is a number.

For example: sinh(3)

would return 10.0178749274099

sinh(5.2)

would return 90.6333626553652

sinh(-5.2)

would return -90.6333626553652

Sqrt Function In Oracle/PLSQL, the sqrt function returns the square root of n. The syntax for the sqrt function is: sqrt( n ) n is a positive number.

For example:

66

none GE Confidential

sqrt(9)

would return 3

sqrt(37)

would return 6.08276253029822

sqrt(5.617)

would return 2.37002109695251

StdDev Function In Oracle/PLSQL, the stddev function returns the standard deviation of a set of numbers. The stddev function can be used two ways - as an Aggregate function or as an Analytic function.

Syntax #1 - Used as an Aggregate Function The syntax for the stddev function when used as an Aggregate function is: stddev( [ DISTINCT | ALL ] expression )

For Example: select stddev(bonus) from employees; The SQL statement above would return the standard deviation of the bonus field in the employees table.

Syntax #2 - Used as an Analytic Function The syntax for the cume_dist function when used as an Analytic function is: stddev( [ DISTINCT | ALL ] expression ) [ OVER ( analytical_clause ) ]

For Example: select employee_name, bonus, stddev(bonus) OVER (ORDER BY salary) from employees where department = 'Marketing'; The SQL statement above would return the cumulative standard deviation of the bonuses in the Marketing department in the employees table, ordered by salary.

SUM Function

67

none GE Confidential

The SUM function returns the summed value of an expression. The syntax for the SUM function is: SELECT SUM(expression ) FROM tables WHERE predicates; expression can be a numeric field or formula.

Simple Example For example, you might wish to know how the combined total salary of all employees whose salary is above $25,000 / year. SELECT SUM(salary) as "Total Salary" FROM employees WHERE salary > 25000; In this example, we've aliased the sum(salary) field as "Total Salary". As a result, "Total Salary" will display as the field name when the result set is returned.

Example using DISTINCT You can use the DISTINCT clause within the SUM function. For example, the SQL statement below returns the combined total salary of unique salary values where the salary is above $25,000 / year. SELECT SUM(DISTINCT salary) as "Total Salary" FROM employees WHERE salary > 25000; If there were two salaries of $30,000/year, only one of these values would be used in the SUM function.

Example using a Formula The expression contained within the SUM function does not need to be a single field. You could also use a formula. For example, you might want the net income for a business. Net Income is calculated as total income less total expenses. SELECT SUM(income - expenses) as "Net Income" FROM gl_transactions;

You might also want to perform a mathematical operation within a SUM function. For example, you might determine total commission as 10% of total sales.

68

none GE Confidential

SELECT SUM(sales * 0.10) as "Commission" FROM order_details;

Example using GROUP BY In some cases, you will be required to use a GROUP BY clause with the SUM function. For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department). SELECT department, SUM(sales) as "Total sales" FROM order_details GROUP BY department; Because you have listed one column in your SELECT statement that is not encapsulated in the SUM function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

Tan Function In Oracle/PLSQL, the tan function returns the tangent of n. The syntax for the tan function is: tan( n ) n is a number. It is an angle expressed in radians.

For example: tan(3)

would return -0.142546543074278

tan(5.2)

would return -1.88564187751976

tan(-5.2)

would return 1.88564187751976

Tanh Function In Oracle/PLSQL, the tanh function returns the hyperbolic tangent of n. The syntax for the tanh function is: tanh( n ) n is a number.

69

none GE Confidential

For example: tanh(3)

would return 0.99505475368673

tanh(5.2)

would return 0.999939136886199

tanh(-5.2)

would return -0.999939136886199

Trunc Function (with dates) In Oracle/PLSQL, the trunc function returns a date truncated to a specific unit of measure. The syntax for the trunc function is: trunc ( date, [ format ] ) date is the date to truncate. format is the unit of measure to apply for truncating. If the format parameter is omitted, the trunc function will truncate the date to the day value, so that any hours, minutes, or seconds will be truncated off.

Below are the valid format parameters: Unit

Valid format parameters

Year

SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y

ISO Year

IYYY, IY, I

Quarter

Q

Month

MONTH, MON, MM, RM

Week

WW

IW

IW

W

W

Day

DDD, DD, J

Start day of the week

DAY, DY, D

Hour

HH, HH12, HH24

Minute

MI

For example: trunc(to_date('22-AUG-03'), 'YEAR')

would return '01-JAN-03'

trunc(to_date('22-AUG-03'), 'Q')

would return '01-JUL-03'

trunc(to_date('22-AUG-03'), 'MONTH')

would return '01-AUG-03'

trunc(to_date('22-AUG-03'), 'DDD')

would return '22-AUG-03'

trunc(to_date('22-AUG-03'), 'DAY')

would return '17-AUG-03'

70

none GE Confidential

Trunc Function (with numbers) In Oracle/PLSQL, the trunc function returns a number truncated to a certain number of decimal places. The syntax for the trunc function is: trunc( number, [ decimal_places ] ) number is the number to truncate. decimal_places is the number of decimal places to truncate to. This value must be an integer. If this parameter is omitted, the round function will truncate the number to 0 decimal places.

For example: trunc(125.815)

would return 125

trunc(125.815, 0)

would return 125

trunc(125.815, 1)

would return 125.8

trunc(125.815, 2)

would return 125.81

trunc(125.815, 3)

would return 125.815

trunc(-125.815, 2)

would return -125.81

trunc(125.815, -1)

would return 120

trunc(125.815, -2)

would return 100

trunc(125.815, -3)

would return 0

Var_pop Function In Oracle/PLSQL, the var_pop function returns the population variance of a set of numbers. The syntax for the var_pop function is: var_pop( expression ) expression is a numeric expression. The var_pop function eliminates all null values before performing its calculations.

For example: select var_pop(data_length) from all_tab_columns;

Var_samp Function 71

none GE Confidential

In Oracle/PLSQL, the var_samp function returns the sample variance of a set of numbers. The syntax for the var_samp function is: var_samp( expression ) expression is a numeric expression. The var_samp function eliminates all null values before performing its calculations.

For example: select var_samp(char_length) from all_tab_columns;

Variance Function In Oracle/PLSQL, the variance function returns the variance of a set of numbers. The syntax for the variance function is: variance( expression ) expression is a numeric expression.

For example: select variance(char_length) from all_tab_columns;

5. Date Functions: Add_Months Function In Oracle/PLSQL, the add_months function returns a date plus n months. The syntax for the add_months function is: add_months( date1, n ) date1 is the starting date (before the n months have been added.

72

none GE Confidential

n is the number of months to add to date1.

For example: add_months('01-Aug-03', 3)

would return '01-Nov-03'

add_months('01-Aug-03', -3)

would return '01-May-03'

add_months('21-Aug-03', -3)

would return '21-May-03'

add_months('31-Jan-03', 1)

would return '28-Feb-03'

Current_Date function In Oracle/PLSQL, the current_date function returns the current date in the time zone of the current SQL session as set by the ALTER SESSION command. The syntax for the current_date function is: current_date

For example: If the following ALTER SESSION command was issued: ALTER SESSION SET TIME_ZONE = '-7:0'; And then the following SQL statement was executed: select current_date from dual; You might get the following result: 9/10/2005 10:58:24 PM You then modified the session time zone with the following ALTER SESSION command: ALTER SESSION SET TIME_ZONE = '-2:0'; And then the following SQL statement was executed: select current_date from dual; You would now get the following result: 9/11/2005 3:58:24 AM

73

none GE Confidential

The session time zone value has changed from -7:0 to -2:0, causing the current_date function to return the current date as a value 5 hours ahead.

Current_Timestamp function In Oracle/PLSQL, the current_timestamp function returns the current date and time in the time zone of the current SQL session as set by the ALTER SESSION command. It returns a TIMESTAMP WITH TIME ZONE value. A similar function to the current_timestamp function is the localtimestamp function. The difference between these two functions is that the current_timestamp function returns a TIMESTAMP WITH TIME ZONE value while the localtimestamp function returns a TIMESTAMP value. The syntax for the current_timestamp function is: current_timestamp

For example: If the following ALTER SESSION command was issued: ALTER SESSION SET TIME_ZONE = '-7:0'; And then the following SQL statement was executed: select current_timestamp from dual; You might get the following result: 10-Sep-05 10.58.24.853421 PM -07:00 You then modified the session time zone with the following ALTER SESSION command: ALTER SESSION SET TIME_ZONE = '-2:0'; And then the following SQL statement was executed: select current_timestamp from dual; You would now get the following result: 10-Sep-05 03.58.24.853421 AM -02:00 The session time zone value has changed from -7:0 to -2:0, causing the current_timestamp function to return the current date and time as a value 5 hours ahead.

Tz_Offset Function 74

none GE Confidential

In Oracle/PLSQL, the tz_offset function returns the time zone offset of a value. The syntax for the tz_offset function is: tz_offset( timezone ) timezone can be a valid time zone name, a time zone offset from UTC, the sessiontimezone function, or the dbtimezone function. Some of the more commonly used time zone names are: Canada/Atlantic

Pacific/Easter

Canada/Central

Pacific/Honolulu

Canada/East-Saskatchewan

Pacific/Kwajalein

Canada/Eastern

Pacific/Pago_Pago

Canada/Mountain

Pacific/Samoa

Canada/Newfoundland

US/Alaska

Canada/Pacific

US/Aleutian

Canada/Saskatchewan

US/Arizona

Canada/Yukon

US/Central

Europe/Dublin

US/East-Indiana

Europe/Istanbul

US/Eastern

Europe/Lisbon

US/Hawaii

Europe/London

US/Michigan

Europe/Moscow

US/Mountain

Europe/Warsaw

US/Pacific

Greenwich

US/Pacific-New

Pacific/Auckland

US/Samoa

Pacific/Chatham To find more time zone values, you can run the following query: select distinct tzname from V$TIMEZONE_NAMES;

For example: tz_offset('US/Michigan')

would return '-05:00'

tz_offset('-08:00')

would return '-08:00'

tz_offset(sessiontimezone)

would return '-07:00' (depending on your configuration)

tz_offset(dbtimezone)

would return '-07:00' (depending on your configuration)

DbTimeZone Function

75

none GE Confidential

In Oracle/PLSQL, the dbtimezone function returns the database time zone as a time zone offset (in the following format: '[+|-]TZH:TZM') or a time zone region name. The syntax for the dbtimezone function is: dbtimezone

For example: select dbtimezone from dual; The dbtimezone function might return a value like this: +00:00 or a value such as this: -07:00

The value returned depends on the time zone value set in your individual Oracle database.

From_Tz function In Oracle/PLSQL, the from_tz function converts a TIMESTAMP value (given a TIME ZONE) to a TIMESTAMP WITH TIME ZONE value. The syntax for the from_tz function is: from_tz( timestamp_value, time_zone_value ) timestamp_value is the value to convert to a TIMESTAMP WITH TIME ZONE value. time_zone_value is the time zone used to convert timestamp_value to a TIMESTAMP WITH TIME ZONE value.

For example: If you executed the following SQL statement:

76

none GE Confidential

select from_tz(TIMESTAMP '2005-09-11 01:50:42', '5:00') from dual; You would now get the following result: 11-Sep-05 01.50.42.000000 AM +05:00

If you executed the same SQL statement, but set the time zone to '-7:00' as follows: select from_tz(TIMESTAMP '2005-09-11 01:50:42', '-7:00') from dual; You would now get the following result: 11-Sep-05 01.50.42.000000 AM -07:00

Last_Day Function In Oracle/PLSQL, the last_day function returns the last day of the month based on a date value. The syntax for the last_day function is: last_day( date ) date is the date value to use to calculate the last day of the month.

For example: last_day(to_date('2003/03/15', 'yyyy/mm/dd'))

would return Mar 31, 2003

last_day(to_date('2003/02/03', 'yyyy/mm/dd'))

would return Feb 28, 2003

last_day(to_date('2004/02/03', 'yyyy/mm/dd'))

would return Feb 29, 2004

LocalTimestamp function In Oracle/PLSQL, the localtimestamp function returns the current date and time in the time zone of the current SQL session as set by the ALTER SESSION command. It returns a TIMESTAMP value. A similar function to the localtimestamp function is the current_timestamp function. The difference between these two functions is that the localtimestamp function returns a TIMESTAMP value while the current_timestamp function returns a TIMESTAMP WITH TIME ZONE value. The syntax for the localtimestamp function is: localtimestamp

77

none GE Confidential

For example: If the following ALTER SESSION command was issued: ALTER SESSION SET TIME_ZONE = '-7:0'; And then the following SQL statement was executed: select localtimestamp from dual; You might get the following result: 10-Sep-05 10.58.24 PM You then modified the session time zone with the following ALTER SESSION command: ALTER SESSION SET TIME_ZONE = '-2:0'; And then the following SQL statement was executed: select localtimestamp from dual; You would now get the following result: 10-Sep-05 03.58.24 AM The session time zone value has changed from -7:0 to -2:0, causing the localtimestamp function to return the current date and time as a value 5 hours ahead.

New_Time Function In Oracle/PLSQL, the new_time function returns a date in time zone1 to a date in time zone2. The syntax for the new_time function is: new_time( date, zone1, zone2 ) zone1 and zone2 can be any of the following values: Value

Description

AST

Atlantic Standard Time

ADT

Atlantic Daylight Time

BST

Bering Standard Time

BDT

Bering Daylight Time

CST

Central Standard Time

CDT

Central Daylight Time

78

none GE Confidential

EST

Eastern Standard Time

EDT

Eastern Daylight Time

GMT

Greenwich Mean Time

HST

Alaska-Hawaii Standard Time

HDT

Alaska-Hawaii Daylight Time

MST

Mountain Standard Time

MDT

Mountain Daylight Time

NST

Newfoundland Standard Time

PST

Pacific Standard Time

PDT

Pacific Daylight Time

YST

Yukon Standard Time

YDT

Yukon Daylight Time

For example: The following new_time example converts an Atlantic Standard Time into a Mountain Standard Time: new_time (to_date ('2003/11/01 01:45', 'yyyy/mm/dd HH24:MI'), 'AST', 'MST') This example would return '2003/10/31 10:45:00 PM'.

Next_Day Function In Oracle/PLSQL, the next_day function returns the first weekday that is greater than a date. The syntax for the next_day function is: next_day( date, weekday ) date is used to find the next weekday. weekday is a day of the week (ie: SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY)

For example: next_day('01-Aug-03', 'TUESDAY')

would return '05-Aug-03'

next_day('06-Aug-03', 'WEDNESDAY')

would return '13-Aug-03'

next_day('06-Aug-03', 'SUNDAY')

would return '10-Aug-03'

Round Function (with dates) 79

none GE Confidential

In Oracle/PLSQL, the round function returns a date rounded to a specific unit of measure. The syntax for the round function is: round( date, [ format ] ) date is the date to round. format is the unit of measure to apply for rounding. If the format parameter is omitted, the round function will round to the nearest day.

Below are the valid format parameters: Unit

Valid format parameters

Rounding Rule

Year

SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y

Rounds up on July 1st

ISO Year

IYYY, IY, I

Quarter

Q

Rounds up on the 16th day of the second month of the quarter

Month

MONTH, MON, MM, RM

Rounds up on the 16th day of the month

Week

WW

Same day of the week as the first day of the year

IW

IW

Same day of the week as the first day of the ISO year

W

W

Same day of the week as the first day of the month

Day

DDD, DD, J

Start day of the week DAY, DY, D Hour

HH, HH12, HH24

Minute

MI

For example: round(to_date ('22-AUG-03'),'YEAR')

would return '01-JAN-04'

round(to_date ('22-AUG-03'),'Q')

would return '01-OCT-03'

round(to_date ('22-AUG-03'),'MONTH')

would return '01-SEP-03'

round(to_date ('22-AUG-03'),'DDD')

would return '22-AUG-03'

round(to_date ('22-AUG-03'),'DAY')

would return '24-AUG-03'

SessionTimeZone Function

80

none GE Confidential

In Oracle/PLSQL, the sessiontimezone function returns the current session's time zone as a time zone offset (in the following format: '[+|-]TZH:TZM') or a time zone region name. The syntax for the sessiontimezone function is: sessiontimezone

For example: select sessiontimezone from dual; The sessiontimezone function might return a value like this: +00:00 or a value such as this: -07:00

The value returned depends on how the user specified the session time zone value in the most recent ALTER SESSION statement. An example of an ALTER SESSION command is: ALTER SESSION SET TIME_ZONE = '-7:0';

Months_Between Function In Oracle/PLSQL, the months_between function returns the number of months between date1 and date2. The syntax for the months_between function is: months_between( date1, date2 ) date1 and date2 are the dates used to calculate the number of months. If a fractional month is calculated, the months_between function calculates the fraction based on a 31-day month.

Example #1 months_between (to_date ('2003/01/01', 'yyyy/mm/dd'), to_date ('2003/03/14', 'yyyy/mm/dd') ) would return -2.41935483870968

81

none GE Confidential

Example #2 months_between (to_date ('2003/07/01', 'yyyy/mm/dd'), to_date ('2003/03/14', 'yyyy/mm/dd') ) would return 3.58064516129032

Example #3 months_between (to_date ('2003/07/02', 'yyyy/mm/dd'), to_date ('2003/07/02', 'yyyy/mm/dd') ) would return 0

Example #4 months_between (to_date ('2003/08/02', 'yyyy/mm/dd'), to_date ('2003/06/02', 'yyyy/mm/dd') ) would return 2

SysTimestamp function In Oracle/PLSQL, the systimestamp function returns the current system date and time (including fractional seconds and time zone) on your local database. The syntax for the systimestamp function is: systimestamp

For example: select systimestamp from dual; The systimezone function might return a value like this: 03-11-21 20:4

You can also choose to use the to_char function with the systimestamp function. For example: select to_char(systimestamp, 'SSSS.FF') from dual; The function above may return a value such as:

82

none GE Confidential

4141.550774

To_Char Function In Oracle/PLSQL, the to_char function converts a number or date to a string. The syntax for the to_char function is: to_char( value, [ format_mask ], [ nls_language ] ) value can either be a number or date that will be converted to a string. format_mask is optional. This is the format that will be used to convert value to a string. nls_language is optional. This is the nls language used to convert value to a string.

Examples - Numbers The following are number examples for the to_char function. to_char(1210.73, '9999.9')

would return '1210.7'

to_char(1210.73, '9,999.99')

would return '1,210.73'

to_char(1210.73, '$9,999.00')

would return '$1,210.73'

to_char(21, '000099')

would return '000021'

Examples - Dates The following is a list of valid parameters when the to_char function is used to convert a date to a string. These parameters can be used in many combinations. Parameter

Explanation

YEAR

Year, spelled out

YYYY

4-digit year

YYY YY Y

Last 3, 2, or 1 digit(s) of year.

IYY IY I

Last 3, 2, or 1 digit(s) of ISO year.

IYYY

4-digit year based on the ISO standard

Q

Quarter of year (1, 2, 3, 4; JAN-MAR = 1).

MM

Month (01-12; JAN = 01).

MON

Abbreviated name of month.

83

none GE Confidential

MONTH

Name of month, padded with blanks to length of 9 characters.

RM

Roman numeral month (I-XII; JAN = I).

WW

Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.

W

Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.

IW

Week of year (1-52 or 1-53) based on the ISO standard.

D

Day of week (1-7).

DAY

Name of day.

DD

Day of month (1-31).

DDD

Day of year (1-366).

DY

Abbreviated name of day.

J

Julian day; the number of days since January 1, 4712 BC.

HH

Hour of day (1-12).

HH12

Hour of day (1-12).

HH24

Hour of day (0-23).

MI

Minute (0-59).

SS

Second (0-59).

SSSSS

Seconds past midnight (0-86399).

FF

Fractional seconds.

The following are date examples for the to_char function. to_char(sysdate, 'yyyy/mm/dd');

would return '2003/07/09'

to_char(sysdate, 'Month DD, YYYY');

would return 'July 09, 2003'

to_char(sysdate, 'FMMonth DD, YYYY');

would return 'July 9, 2003'

to_char(sysdate, 'MON DDth, YYYY');

would return 'JUL 09TH, 2003'

to_char(sysdate, 'FMMON DDth, YYYY');

would return 'JUL 9TH, 2003'

to_char(sysdate, 'FMMon ddth, YYYY');

would return 'Jul 9th, 2003'

You will notice that in some examples, the format_mask parameter begins with "FM". This means that zeros and blanks are suppressed. This can be seen in the examples below. to_char(sysdate, 'FMMonth DD, YYYY');

would return 'July 9, 2003'

to_char(sysdate, 'FMMON DDth, YYYY');

would return 'JUL 9TH, 2003'

to_char(sysdate, 'FMMon ddth, YYYY');

would return 'Jul 9th, 2003'

The zeros have been suppressed so that the day component shows as "9" as opposed to "09".

84

none GE Confidential

Frequently Asked Questions Question: Why doesn't this sort the day's of the week in order? select ename, hiredate, to_char((hiredate),'fmDay') "Day" from emp order by "Day"; Answer: The fmDay parameter will return the name of the Day and not the numeric value of the day. Try the following: select ename, hiredate, to_char((hiredate),'fmDD') "Day" from emp order by "Day";

Sysdate function In Oracle/PLSQL, the sysdate function returns the current system date and time on your local database. The syntax for the sysdate function is: sysdate

Example #1 select sysdate into v_date from dual; The variable called v_date will now contain the current date and time value.

Example #2 You could also use the sysdate function in any SQL statement. For example: select supplier_id, sysdate from suppliers where supplier_id > 5000;

Example #3 If you wanted to extract the date portion only (and exclude the time component), you could use the to_char function. For example:

85

none GE Confidential

select supplier_id, to_char(sysdate, 'yyyy/mm/dd') from suppliers where supplier_id > 5000;

To_Date Function In Oracle/PLSQL, the to_date function converts a string to a date. The syntax for the to_date function is: to_date( string1, [ format_mask ], [ nls_language ] ) string1 is the string that will be converted to a date. format_mask is optional. This is the format that will be used to convert string1 to a date. nls_language is optional. This is the nls language used to convert string1 to a date.

The following is a list of options for the format_mask parameter. These parameters can be used in many combinations. Parameter

Explanation

YEAR

Year, spelled out

YYYY

4-digit year

YYY YY Y

Last 3, 2, or 1 digit(s) of year.

IYY IY I

Last 3, 2, or 1 digit(s) of ISO year.

IYYY

4-digit year based on the ISO standard

RRRR

Accepts a 2-digit year and returns a 4-digit year. A value between 0-49 will return a 20xx year. A value between 50-99 will return a 19xx year.

Q

Quarter of year (1, 2, 3, 4; JAN-MAR = 1).

MM

Month (01-12; JAN = 01).

MON

Abbreviated name of month.

MONTH

Name of month, padded with blanks to length of 9 characters.

RM

Roman numeral month (I-XII; JAN = I).

WW

Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.

W

Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.

86

none GE Confidential

IW

Week of year (1-52 or 1-53) based on the ISO standard.

D

Day of week (1-7).

DAY

Name of day.

DD

Day of month (1-31).

DDD

Day of year (1-366).

DY

Abbreviated name of day.

J

Julian day; the number of days since January 1, 4712 BC.

HH

Hour of day (1-12).

HH12

Hour of day (1-12).

HH24

Hour of day (0-23).

MI

Minute (0-59).

SS

Second (0-59).

SSSSS

Seconds past midnight (0-86399).

FF

Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. For example, 'FF4'.

AM, A.M., PM, or P.M. Meridian indicator AD or A.D

AD indicator

BC or B.C.

BC indicator

TZD

Daylight savings information. For example, 'PST'

TZH

Time zone hour.

TZM

Time zone minute.

TZR

Time zone region.

For example: to_date('2003/07/09', 'yyyy/mm/dd')

would return a date value of July 9, 2003.

to_date('070903', 'MMDDYY')

would return a date value of July 9, 2003.

to_date('20020315', 'yyyymmdd')

would return a date value of Mar 15, 2002.

Trunc Function (with dates) In Oracle/PLSQL, the trunc function returns a date truncated to a specific unit of measure. The syntax for the trunc function is: trunc ( date, [ format ] ) date is the date to truncate.

87

none GE Confidential

format is the unit of measure to apply for truncating. If the format parameter is omitted, the trunc function will truncate the date to the day value, so that any hours, minutes, or seconds will be truncated off.

Below are the valid format parameters: Unit

Valid format parameters

Year

SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y

ISO Year

IYYY, IY, I

Quarter

Q

Month

MONTH, MON, MM, RM

Week

WW

IW

IW

W

W

Day

DDD, DD, J

Start day of the week

DAY, DY, D

Hour

HH, HH12, HH24

Minute

MI

For example: trunc(to_date('22-AUG-03'), 'YEAR')

would return '01-JAN-03'

trunc(to_date('22-AUG-03'), 'Q')

would return '01-JUL-03'

trunc(to_date('22-AUG-03'), 'MONTH')

would return '01-AUG-03'

trunc(to_date('22-AUG-03'), 'DDD')

would return '22-AUG-03'

trunc(to_date('22-AUG-03'), 'DAY')

would return '17-AUG-03'

6. Error Functions: SQLCODE Function

What does the SQLCODE Function do? The SQLCODE function returns the error number associated with the most recently raised error exception. This function should only be used within the Exception Handling section of your code:

88

none GE Confidential

EXCEPTION WHEN exception_name1 THEN [statements] WHEN exception_name2 THEN [statements] WHEN exception_name_n THEN [statements] WHEN OTHERS THEN [statements] END [procedure_name];

You could use the SQLCODE function to raise an error as follows: EXCEPTION WHEN OTHERS THEN raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); END;

Or you could log the error to a table as follows: EXCEPTION WHEN OTHERS THEN err_code := SQLCODE; err_msg := substr(SQLERRM, 1, 200); INSERT INTO audit_table (error_number, error_message) VALUES (err_code, err_msg); END;

Learn more about the SQLERRM Function.

SQLERRM Function

What does the SQLERRM Function do? The SQLERRM function returns the error message associated with the most recently raised error exception. This function should only be used within the Exception Handling section of your code: EXCEPTION WHEN exception_name1 THEN [statements] WHEN exception_name2 THEN [statements]

89

none GE Confidential

WHEN exception_name_n THEN [statements] WHEN OTHERS THEN [statements] END [procedure_name];

You could use the SQLERRM function to raise an error as follows: EXCEPTION WHEN OTHERS THEN raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); END;

Or you could log the error to a table as follows: EXCEPTION WHEN OTHERS THEN err_code := SQLCODE; err_msg := substr(SQLERRM, 1, 200); INSERT INTO audit_table (error_number, error_message) VALUES (err_code, err_msg); END;

Learn more about the SQLCODE Function.

7. Miscellaneous Functions: Uid function In Oracle/PLSQL, the uid function returns the id number for a user's session (the user who is currently logged in). The syntax for the uid function is: uid For example: select uid into parm_uid from dual; The variable called parm_uid will now contain the id number for the user's session. The value could be 25, for example.

90

none GE Confidential

User function In Oracle/PLSQL, the user function returns the user_id from the current Oracle session. The syntax for the user function is: user

For example: select user into parm_user_ID from dual;

The variable called parm_user_ID will now contain the Oracle user ID from the current Oracle session.

91

none GE Confidential