The Oracle DECODE function

The Oracle DECODE function can be considered an IF/ELSE variation. It compares the very same expression with other or others.

Syntax

This function has three mandatory parameters. The first and second parameters are compared, if they are equal, DECODE returns the third parameter, if not, NULL is returned.

DECODE(expression, expressionB, result_if_equalB);

This function can be bigger. For instance, if we want a concrete result to be returned if the expressions are not equal, we add a new parameter at the end.

DECODE(expression, expressionB, result_if_equalB,result_if_not_equalB);

It can be used to compare “expression” with more results. Let’s rewrite this example to show how can it grow.

DECODE(expression,
expressionB, result_if_equalB,
expressionC, result_if_equalC,
expressionD, result_if_equalD,
result_if_none_is_equal);

Examples

SELECT DECODE(343,344,'Equal') from dual;

This query returns: NULL.

SELECT DECODE(343,344,'Equal','Non_Equal') from dual;

This query returns: Non_Equal.

SELECT DECODE('ORIGINAL','COPY','Its_not_copy','TRANSCRIPTION','Its_not_transcription','ORIGINAL','Its_original','Unknown') from dual;

This query returns: Its_original.

Leave a Reply

Your email address will not be published.