Monday, July 2, 2018

Informatica Power center Designer - Functions

1.1         ISNULL

The ISNULL function returns whether a value is NULL. It is available in the Designer and the Workflow Manager.
                ISNULL (value)

Example: The following example checks for null values in the items table:
ISNULL (ITEM_NAME)

ITEM_NAME
RETURN VALUE
Flashlight
0 (FALSE)
NULL
1 (TRUE)
''
0 (FALSE) Empty string is not NULL

1.2         IS_DATE

The IS_DATE function returns whether a value is a valid date. It is available in the Designer and the Workflow Manager.
     IS_DATE (value)

ExampleThe following expression checks the INVOICE_DATE port for valid dates:
IS_DATE (INVOICE_DATE)
This expression returns data similar to the following:
INVOICE_DATE
RETURN VALUE
NULL
NULL
180
0 (FALSE)
'04/01/98'
0 (FALSE)
'04/01/1998 00:12:15'
1 (TRUE)
'02/31/1998 12:13:55'
0 (FALSE) (February does not have 31 days)
'John Smith'
0 (FALSE)

This function can also be used to validate a date for a specified format for which the syntax is
IS_DATE (value, format)

If the format is not specified, ‘MM/DD/YYYY’ is taken as the default format.

1.3         IS_NUMBER

The IS_NUMBER returns whether a string is a valid number. It is available in the Designer and the Workflow Manager.
     IS_NUMBER (value)

ExampleThe following expression checks the ITEM_PRICE port for valid numbers:
IS_NUMBER (ITEM_PRICE)

ITEM_PRICE
RETURN VALUE
123.00
1 (True)
-3.45e+3
1 (True)
''
0 (False) Empty string
+123abc
0 (False)
ABC
0 (False)
-ABC
0 (False)
NULL
NULL

1.4         IS_SPACES:

The IS_SPACES function returns whether a value consists entirely of spaces. It is available in the Designer and the Workflow Manager.
     IS_SPACES (value)

Example: The following expression checks the ITEM_NAME port for rows that consist entirely of spaces:
IS_SPACES IS_SPACES (ITEM_NAME)

ITEM_NAME
RETURN VALUE
Flashlight
0 (False)
1 (True)
Regulator system
0 (False)

        
1.5         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


1.6         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 costly 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.

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

1.8         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

Date Functions

Date Format Strings in the Transformation Reference

D, DD, DDD, DAY, DY, J

Days (01-31). We can use any of these format strings to specify the entire day portion of a date. For example, if we pass 12-APR-1997 to a date function, we can use any of these format strings specify 12.

HH, HH12, HH24

Hour of day (0 to 23), where zero is 12 AM (midnight). We can use any of these formats to specify the entire hour portion of a date. For example, if we pass the date 12-APR-1997 2:01:32 PM, we can use HH, HH12, or HH24 to specify the hour portion of the date.

MI

Minutes.

MM, MON, MONTH

Month portion of date (0 to 59). We can use any of these format strings to specify the entire month portion of a date. For example, if we pass 12-APR-1997 to a date function, we can use MM, MON, or MONTH to specify APR.

SS, SSSS

Second portion of date (0 to 59).

Y, YY, YYY, YYYY, RR

Year portion of date (1753 to 9999). We can use any of these format strings to specify the entire year portion of a date. For example, if we pass 12-APR-1997 to a date function, we can use Y, YY, YYY, or YYYY to specify 1997.

1.9         ADD_TO_DATE

The ADD_TO_DATE function adds a specified amount to one part of a date/time value, and returns a date in the same format as the specified date.

Note: If we do not specify the year as YYYY, the Informatica Server assumes the date is in the current century. It is available in the Designer and the Workflow Manager.

     ADD_TO_DATE (date, format, amount)

Example : The following expression adds one month to each date in the DATE_SHIPPED port. If we pass a value that creates a day that does not exist in a particular month, the Informatica Server returns the last day of the month. For example, if we add one month to Jan 31 1998, the Informatica Server returns Feb 28 1998.

Also note, ADD_TO_DATE recognizes leap years and adds one month to Jan 29 2000:

ADD_TO_DATE (DATE_SHIPPED, 'MM', 1)


DATE_SHIPPED

RETURN VALUE
Jan 12 1998 12:00:30AM
Feb 12 1998 12:00:30AM

The following expression subtracts 10 days from each date in the DATE_SHIPPED port:

ADD_TO_DATE (DATE_SHIPPED, 'D', -10)

DATE_SHIPPED
RETURN VALUE
Jan 1 1997 12:00:30AM
Dec 22 1996 12:00AM

The following expression subtracts 15 hours from each date in the DATE_SHIPPED port:

ADD_TO_DATE (DATE_SHIPPED, 'HH', -15)


DATE_SHIPPED
RETURN VALUE
Jan 1 1997 12:00:30AM
Dec 31 1996 9:00:30AM

In ADD_TO_DATE function, if the argument passed evaluates to a date that does not exist in a particular month, the Informatica Server returns the last day of the month.

The following expression reveals this.

ADD_TO_DATE (DATE_SHIPPED, 'MON', 3)

DATE_SHIPPED
RETURN VALUE
Jan 31 1998 6:24:45PM                                      
Apr 30 1998 6:24:45PM

1.10      DATE_COMPARE

The DATE_COMPARE function returns a value indicating the earlier of two dates. It is available in the Designer and the Workflow Manager.

      DATE_COMPARE (date1, date2)

ExampleThe following expression compares each date in the DATE_PROMISED and DATE_SHIPPED ports, and returns an integer indicating which date is earlier:

DATE_COMPARE (DATE_PROMISED, DATE_SHIPPED)

DATE_PROMISED
DATE_SHIPPED
RETURN VALUE
Jan 1 1997
Jan 13 1997
-1
Feb 1 1997
Feb 1 1997
0
Dec 22 1997
Dec 15 1997
1

2.1    DATE_DIFF

The DATE_DIFF function returns the length of time between two dates, measured in the specified increment (years, months, days, hours, minutes, or seconds). It is available in the Designer and the Workflow Manager.

      DATE_DIFF (date1, date2, format)

Example: The following expressions return the number of days between the DATE_PROMISED and the DATE_SHIPPED ports:

DATE_DIFF

DATE_DIFF (DATE_PROMISED, DATE_SHIPPED, 'D’)

DATE_DIFF

DATE_DIFF (DATE_PROMISED, DATE_SHIPPED, 'DD')

DATE_PROMISED
DATE_SHIPPED
RETURN VALUE
Jan 1 1997 12:00:00AM
Mar 29 1997 12:00:00PM
-87.5
Mar 29 1997 12:00:00PM
Jan 1 1997 12:00:00AM
87.5

We can combine DATE functions and TEST functions so as to validate the dates.

For example, while using the DATE functions like DATE_COMPARE and DATE_DIFF, the dates given as inputs can be validated using the TEST function IS_DATE and then passed to them if valid.


2.2    GET_DATE_PART

The GET_DATE_PART function returns the specified part of a date as an integer value, based on the default date format of MM/DD/YYYY HH24:MI: SS. It is available in the Designer and the Workflow Manager.

      GET_DATE_PART (date, format)

Example: The following expressions return the day for each date in the DATE_SHIPPED port:

GE

GET_DATE_PART (DATE_SHIPPED, 'D’)

GE

GET_DATE_PART (DATE_SHIPPED, 'DD’)

DATE_SHIPPED
RETURN VALUE
Mar 13 1997 12:00:00AM
13
June 3 1997 11:30:44PM
3
NULL
NULL


2.3    LAST_DAY

The LAST_DAY function returns the date of the last day of the month for each date in a port. It is available in the Designer and the Workflow Manager.

      LAST_DAY (date)

ExampleThe following expression returns the last day of the month for each date in the ORDER_DATE port:

LAST_DAY (ORDER_DATE)

ORDER_DATE
RETURN VALUE
Apr 1 1998 12:00:00AM
Apr 30 1998 12:00:00AM
Jan 6 1998 12:00:00AM
Jan 31 1998 12:00:00AM

DATE functions combine with Conversion functions also.

The following expression has LAST_DAY and TO_DATE functions nested or combined together.

LAST_DAY (TO_DATE (GIVEN_DATE, 'DD-MON-YY’))


2.4    MAX
  
The MAX function returns the latest date found in a group. It is available in the Designer.

     MAX (date, filtercondition)

We can return the maximum date for a port or group.

Example: The following expression returns the maximum order date for flashlights:

MAX (ORDERDATE, ITEM_NAME='Flashlight' )

ITEM_NAME
ORDER_DATE
Flashlight
Apr 20 1998
Regulator System
May 15 1998
Flashlight
Sep 21 1998
Diving Hood
Aug 18 1998
Halogen Flashlight
Feb 1 1998
Flashlight
Oct 10 1998

RETURN VALUE: Oct 10 1998
  
2.5    MIN

The MIN function returns the earliest date found in a group. It is available in the Designer.

    MIN( date, filter_condition )

Example: The following expression returns the oldest order date for flashlights:

MIN (ORDER_DATE, ITEM_NAME='Flashlight' )

ITEM_NAME
ORDER_DATE
Flashlight
Apr 20 1998
Regulator System
May 15 1998
Flashlight
Sep 21 1998
Diving Hood
Aug 18 1998
Halogen Flashlight
Feb 1 1998
Flashlight
Oct 10 1998
RETURN VALUE: Feb 1 1998




2.6    ROUND

The ROUND function rounds one part of a date. It is available in the Designer and the Workflow Manager.

   ROUND( date [, format ] )

Example: The following expressions round the month portion of each date in the DATE_SHIPPED port.

ROUND( DATE_SHIPPED, 'MM' )

ROUND( DATE_SHIPPED, 'MON' )

DATE_SHIPPED
RETURN VALUE
Jan 15 1998 2:10:30AM
Jan 1 1998 12:00:00AM

Similarly, the ROUND function can be used to round off Year, Day or Time portions.

2.7    SET_DATE_PART

The SET_DATE_PART function sets one part of a date/time value to a specified value. It is available in the Designer and the Workflow Manager.

    SET_DATE_PART( date, format, value )

Example: The following expressions change the month to June for the dates in the DATE_PROMISED port. The Informatica Server displays an error when we try to create a date that does not exist, such as changing March 31 to June 31:

SET_DATE_PART( DATE_PROMISED, 'MM', 6 )

SET_DATE_PART( DATE_PROMISED, 'MON', 6 )

DATE_PROMISED
RETURN VALUE
Jan 1 1997 12:15:56AM
Jun 1 1997 12:15:56AM
NULL
NULL

Similarly, the SET_DATE_PART function can be used to round off Year, Day or Time portions.

2.8    TRUNC

The TRUNC function truncates dates to a specific year, month, day, hour, or minute. It is available in the Designer and the Workflow Manager.

     TRUNC (date [, format] )

Example: The following expressions truncate the year portion of dates in the DATE_SHIPPED port:

TRUNC (DATE_SHIPPED, 'Y' )

TRUNC (DATE_SHIPPED, 'YY' )

DATE_SHIPPED
RETURN VALUE
Jan 15 1998 2:10:30AM
Jan 1 1998 12:00:00AM

Similarly, the TRUNC function can be used to truncate Month, Day or Time portions.

The functions TRUNC & ROUND can be nested in order to manipulate dates.

No comments:

Post a Comment

Thank you :
- kareem