Wednesday 29 June 2011

Special Functions


4.1      DECODE

The DECODE function searches a port for the specified value. It is available in the Designer and the Workflow Manager.
     DECODE( value, first_search, first_result [, second_search, second_result ]…[, default ] )
Example: We might use DECODE in an expression that searches for a particular ITEM_ID and returns the ITEM_NAME:
DECODE( ITEM_ID, 10, 'Flashlight',
14, 'Regulator', 20, 'Knife', 40, 'Tank', 'NONE' )
ITEM_ID
RETURN VALUE
10
Flashlight
14
Regulator
17
NONE

 

4.2      IIF

The IIF function returns one of two values we specify, based on the results of a condition. It is available in the Designer and the Workflow Manager.
     IIF( condition, value2 [, value2 ] )
Example : IIF( SALES < 100, 0, SALARY )
SALES
SALARY
RETURN VALUE
150
50,000.00
50,000
50
20,000.00
0
NULL
50,000.41
50,000

IIF functions can be nested if there is more than one condition to be tested. But it is always a better option to go for DECODE function when the number of conditions is large since DECODE function is less costlier compared to IIF function.
For example consider the following expression
IIF(MARKS>=90,'A',
    (IIF(MARKS>= 75,'B',
          (IIF(MARKS>=65,'C',
               (IIF(MARKS>=55,'D',
                     IIF(MARKS>=45,'E',
                           'F'))))))))
The same result can be obtained with
DECODE(TRUE,
       MARKS>=90,'A',
       MARKS>=75,'B',
       MARKS>=65,'C',
       MARKS>=55,'D',
       MARKS>=45,'E',
       'F')
            
   When the number of conditions increase we will be able to appreciate the simplicity of the DECODE function and the complexity of the IIF function.
     In both the cases , If MARKS>90 it will return 'A' though it satisfies all the conditions given. It is because it returns when the first condition is satisfied. Therefore even if a port satisfies two or more the conditions it will take only the first one. Therefore Ordering is  important in IIF and DECODE functions.

4.3      ERROR:

The ERROR function causes the Informatica Server to skip a record and throws an error message defined by the user. It is available in the Designer.
                        ERROR( string )
Example : The following example shows how you can reference a mapping that calculates the average salary for employees in all departments of your company, but skips negative values. The following expression nests the ERROR function in an IIF expression so that if the Informatica Server finds a negative salary in the Salary port, it skips the row and displays an error:
IIF( SALARY < 0, ERROR ('Error. Negative salary found. Row skipped.', EMP_SALARY )
SALARY
RETURN VALUE
10000
10000
-15000
'Error. Negative salary found. Row skipped.'

The below example combines two special functions, a test Function and a conversion function.

IIF(IS_DATE(DATE_PROMISED,'MM/DD/YY'),TO_DATE(DATE_PROMISED),ERROR('Invalid Date'))

4.4      LOOKUP:

The LOOKUP function searches for a particular value in a lookup source column. It is available in the Designer.
                        LOOKUP( result, search1, value1 [, search2, value2]… )

Example : The following expression searches the lookup source :TD.SALES for a specific item ID and price, and returns the item name if both searches find a match:
LOOKUP( :TD.SALES.ITEM_NAME, :TD.SALES.ITEM_ID, 10, :TD.SALES.PRICE, 15.99 )
ITEM_NAME
ITEM_ID
PRICE
Regulator
5
100.00
Flashlight
10
15.99

No comments:

Post a Comment