Decode and Ifthenelse

[Former Member]

Hi Experts,

                I would like to know the functioning of decode and ifthenelse functions. Which one is faster. Is there difference in the performance of these two.

Thanks & Regards

Alex Oommen

[former_member186897 · Accepted solution]

The decode function provides an easier way to write nested ifthenelse functions. Therefore, decode is less error prone than nested ifthenelse functions. I think there is no performance difference and

DS pushes this function to the database server when possible.

[former_member187605 · Accepted solution]

There's definitely no performance difference. From this mapping

following SQL-code is generated:

SQL_Stage refers to SQL Server 2012 db.

[former_member189153 · Accepted solution]

Hi,

I don't think any performance difference, Even nested expression also generate same SQL Code.

Regards

M Ramesh

[former_member189153]

Hi Alex,

ifthenelse function Allows conditional logic in expressions Returns one of the values provided, based on the result of condition. The data type of the return
value is the data type of the expression in true_branch. If the data type of false_branch is not
convertible to the data type of true_branch, Data Services produces an error at validation.

decode returns the value associated with the first condition that evaluates to TRUE. The data type of the return value is the data type of the first expression in the condition_and_expression_list. If the data type of any subsequent expression or the default_expression is not convertible to the data type of the first expression, Data Services produces an error at validation. If the data types are convertible but do not match, a warning appears at validation.

The decode function provides an easier way to write nested ifthenelse functions. In nested
ifthenelse functions, you must write nested conditions and ensure that the parentheses are in the
correct places, as the following example shows:
ifthenelse ((EMPNO = 1), '111',
ifthenelse((EMPNO = 2), '222',
ifthenelse((EMPNO = 3), '333',
ifthenelse((EMPNO = 4), '444',
'NO_ID'))))

In the decode function, you list the conditions, as the following example shows. Therefore, decode is
less error prone than nested ifthenelse functions.

decode ((EMPNO = 1), '111',
(EMPNO = 2), '222',
(EMPNO = 3), '333',
(EMPNO = 4), '444',
'NO_ID')

Regards,

M Ramesh

[AlexGG]

Hi Ramesh
What will be returned when EMPNO = NULL ?
NULL or NO_ID ?

assumption : We don't have option i.e. (EMPNO IS NULL), '0',

Regards
Alex

[etorri]

@AlexGG, we have set conditions for few EMPNO(1,2,3,4) for all other values it will return 'NO_ID' whatever the EMPNO, even if it is 5,6,7 or NULL.