Oracle: Case Statement

Starting in Oracle 9i, you can use the case statement within an SQL statement. It has the functionality of an IF-THEN-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 optional. It 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:

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.

Applies To:

• Oracle 9i, Oracle 10g, Oracle 11g

For Example:

You could use the case statement in an SQL statement as follows: (includes the expression clause)

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;

Or you could write the SQL statement using the case statement like this: (omits the expression clause)

select table_name,
CASE
  WHEN owner='SYS' THEN 'The owner is SYS'
  WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
  ELSE 'The owner is another value'
END
from all_tables;

The above two case statements are 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.

For Example:

Here is an example that demonstrates how to use the case statement to compare different conditions:

select
CASE
  WHEN a < b THEN 'hello'
  WHEN d < e THEN 'goodbye'
END
from suppliers;

Frequently Asked Questions

---

Question: Can you create a case statement that evaluates two different fields? I want to return a value based on the combinations in two different fields.

Answer: Yes, below is an example of a case statement that evaluates two different fields.

select supplier_id,
CASE
  WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'North office'
  WHEN supplier_name = 'IBM' and supplier_type = 'Software' THEN 'South office'
END
from suppliers;

So if supplier_name field is IBM and the supplier_type field is Hardware, then the case statement will return North office. If the supplier_name field is IBM and the supplier_type is Software, the case statement will return South office.

Fonte: http://www.techonthenet.com/oracle/functions/case.php

Oracle: 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.

Applies To:

• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

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'

Oracle: Grant Privileges on Tables

You can grant users various privileges to tables. These privileges can be any combination of select, insert, update, delete, references, alter, and index. Below is an explanation of what each privilege means.

Privilege	Description
Select	Ability to query the table with a select statement.
Insert	Ability to add new rows to the table with the insert statement.
Update	Ability to update rows in the table with the update statement.
Delete	Ability to delete rows from the table with the delete statement.
References	Ability to create a constraint that refers to the table.
Alter	Ability to change the table definition with the alter table statement.
Index	Ability to create an index on the table with the create index statement.

The syntax for granting privileges on a table is:

grant privileges on object to user;

For example, if you wanted to grant select, insert, update, and delete privileges on a table called suppliers to a user name smithj, you would execute the following statement:

grant select, insert, update, delete on suppliers to smithj;

You can also use the all keyword to indicate that you wish all permissions to be granted. For example:

grant all on suppliers to smithj;

If you wanted to grant select access on your table to all users, you could grant the privileges to the public keyword. For example:

grant select on suppliers to public;

Revoke Privileges on Tables

Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.

The syntax for revoking privileges on a table is:

revoke privileges on object from user;

For example, if you wanted to revoke delete privileges on a table called suppliers from a user named anderson, you would execute the following statement:

revoke delete on suppliers from anderson;

If you wanted to revoke all privileges on a table, you could use the all keyword. For example:

revoke all on suppliers from anderson;

If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:

revoke all on suppliers from public;

Grant Privileges on Functions/Procedures

When dealing with functions and procedures, you can grant users the ability to execute these functions and procedures. The Execute privilege is explained below:

Privilege	Description
Execute	Ability to compile the function/procedure. Ability to execute the function/procedure directly.

The syntax for granting execute privileges on a function/procedure is:

grant execute on object to user;

For example, if you had a function called Find_Value and you wanted to grant execute access to the user named smithj, you would execute the following statement:

grant execute on Find_Value to smithj;

If you wanted to grant all users the ability to execute this function, you would execute the following:

grant execute on Find_Value to public;

Revoke Privileges on Functions/Procedures

Once you have granted execute privileges on a function or procedure, you may need to revoke these privileges from a user. To do this, you can execute a revoke command.

The syntax for the revoking privileges on a function or procedure is:

revoke execute on object from user;

If you wanted to revoke execute privileges on a function called Find_Value from a user named anderson, you would execute the following statement:

revoke execute on Find_Value from anderson;

If you had granted privileges to public (all users) and you wanted to revoke these privileges, you could execute the following statement:

revoke execute on Find_Value from public;

Oracle: Sequences (Autonumber)

In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

The syntax for a sequence is:

CREATE SEQUENCE sequence_name
    MINVALUE value
    MAXVALUE value
    START WITH value
    INCREMENT BY value
    CACHE value;

For example:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

This would create a sequence object called supplier_seq. The first sequence number that it would use is 1 and each subsequent number would increment by 1 (ie: 2,3,4,...}. It will cache up to 20 values for performance.

If you omit the MAXVALUE option, your sequence will automatically default to:

MAXVALUE 999999999999999999999999999

So you can simplify your CREATE SEQUENCE command as follows:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

Now that you've created a sequence object to simulate an autonumber field, we'll cover how to retrieve a value from this sequence object. To retrieve the next value in the sequence order, you need to use nextval.

For example:

supplier_seq.nextval

This would retrieve the next value from supplier_seq. The nextval statement needs to be used in an SQL statement. For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(supplier_seq.nextval, 'Kraft Foods');

This insert statement would insert a new record into the suppliers table. The supplier_id field would be assigned the next number from the supplier_seq sequence. The supplier_name field would be set to Kraft Foods.

Frequently Asked Questions

---

One common question about sequences is:

Question:  While creating a sequence, what does cache and nocache options mean? For example, you could create a sequence with a cache of 20 as follows:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

Or you could create the same sequence with the nocache option:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    NOCACHE;

Answer:  With respect to a sequence, the cache option specifies how many sequence values will be stored in memory for faster access.

The downside of creating a sequence with a cache is that if a system failure occurs, all cached sequence values that have not be used, will be "lost". This results in a "gap" in the assigned sequence values. When the system comes back up, Oracle will cache new numbers from where it left off in the sequence, ignoring the so called "lost" sequence values.

Note: To recover the lost sequence values, you can always execute an ALTER SEQUENCE command to reset the counter to the correct value.

Nocache means that none of the sequence values are stored in memory. This option may sacrifice some performance, however, you should not encounter a gap in the assigned sequence values.

---

Question:  How do we set the LASTVALUE value in an Oracle Sequence?

Answer:  You can change the LASTVALUE for an Oracle sequence, by executing an ALTER SEQUENCE command.

For example, if the last value used by the Oracle sequence was 100 and you would like to reset the sequence to serve 225 as the next value. You would execute the following commands.

alter sequence seq_name
increment by 124;

select seq_name.nextval from dual;

alter sequence seq_name
increment by 1;

Now, the next value to be served by the sequence will be 225.

Oracle/PLSQL: Data Types

The following is a list of datatypes available in Oracle.

Character Datatypes

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
char(size)	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Where size is the number of characters to store. Fixed-length strings. Space padded.
nchar(size)	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Where size is the number of characters to store. Fixed-length NLS string Space padded.
nvarchar2(size)	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Where size is the number of characters to store. Variable-length NLS string.
varchar2(size)	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Where size is the number of characters to store. Variable-length string.
long	Maximum size of 2GB.	Maximum size of 2GB.	Maximum size of 2GB.	Variable-length strings. (backward compatible)
raw	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Variable-length binary strings
long raw	Maximum size of 2GB.	Maximum size of 2GB.	Maximum size of 2GB.	Variable-length binary strings. (backward compatible)

 

Numeric Datatypes

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
number(p,s)	Precision can range from 1 to 38. Scale can range from -84 to 127.	Precision can range from 1 to 38. Scale can range from -84 to 127.	Precision can range from 1 to 38. Scale can range from -84 to 127.	Where p is the precision and s is the scale. For example, number(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.
numeric(p,s)	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Where p is the precision and s is the scale. For example, numeric(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.
float
dec(p,s)	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Where p is the precision and s is the scale. For example, dec(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.
decimal(p,s)	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Where p is the precision and s is the scale. For example, decimal(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.
integer
int
smallint
real
double precision

 

Date/Time Datatypes

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
date	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.
timestamp (fractional seconds precision)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds. For example: timestamp(6)
timestamp (fractional seconds precision) with time zone	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds; with a time zone displacement value. For example: timestamp(5) with time zone
timestamp (fractional seconds precision) with local time zone	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds; with a time zone expressed as the session time zone. For example: timestamp(4) with local time zone
interval year (year precision) to month	year precision is the number of digits in the year. (default is 2)	year precision is the number of digits in the year. (default is 2)	year precision is the number of digits in the year. (default is 2)	Time period stored in years and months. For example: interval year(4) to month
interval day (day precision) to second (fractional seconds precision)	day precision must be a number between 0 and 9. (default is 2) fractional seconds precision must be a number between 0 and 9. (default is 6)	day precision must be a number between 0 and 9. (default is 2) fractional seconds precision must be a number between 0 and 9. (default is 6)	day precision must be a number between 0 and 9. (default is 2) fractional seconds precision must be a number between 0 and 9. (default is 6)	Time period stored in days, hours, minutes, and seconds. For example: interval day(2) to second(6)

 

Large Object (LOB) Datatypes

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
bfile	Maximum file size of 4GB.	Maximum file size of 232-1 bytes.	Maximum file size of 264-1 bytes.	File locators that point to a binary file on the server file system (outside the database).
blob	Store up to 4GB of binary data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage).	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage).	Stores unstructured binary large objects.
clob	Store up to 4GB of character data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character data.	Stores single-byte and multi-byte character data.
nclob	Store up to 4GB of character text data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character text data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character text data.	Stores unicode data.

 

Rowid Datatypes

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation (if applicable)
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.	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.	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.	Fixed-length binary data. Every record in the database has a physical address or rowid.
urowid(size)				Universal rowid. Where size is optional.

Oracle: 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.

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.

Applies To:

• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

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.

Oracle: 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.

Applies To:

• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

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;

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;

Oracle: 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.

Applies To:

• Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

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).
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 days of the week in order?

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

Answer:

In the above SQL, the fmDay format mask used in the to_char function will return the name of the Day and not the numeric value of the day.

To sort the days of the week in order, you need to return the numeric value of the day by using the fmD format mask as follows:

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

Oracle: 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.
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).

Applies To:

• Oracle 9i, Oracle 10g, Oracle 11g

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:

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

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:

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')

---

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,	'category 1',
	1,	'category 2',
	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,	0.04,
	1,	0.04,
		0.06) as perc_value
FROM employees;

---

Question:  Is there a limit to the number of arguments that you can have in one DECODE statement? I'm getting an error, "ORA-00939: too many arguments for function".
Answer:  Yes, the maximum number of components that you can have in a decode function is 255. This includes the 
expression, search, and result arguments.

Fonte: http://www.techonthenet.com/oracle/functions/decode.php

Oracle: To_Date Function

Oracle/PLSQL: 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.
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.

Applies To:

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

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.