1.1
ISNULL
The DECODE function searches a port for the specified value. It is available in the Designer and the Workflow Manager.
The ISNULL function returns
whether a value is NULL. It is available in the Designer and
the Workflow Manager.
The IS_DATE function returns
whether a value is a valid date. It is available in the Designer and the
Workflow Manager.
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.
The IS_NUMBER returns whether a
string is a valid number. It is available in the Designer and the Workflow
Manager.
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:
1.5
DECODE:
The DECODE function searches a port for the specified value. It is available in the Designer and the Workflow Manager.
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’)
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.
Example : IIF(
SALES < 100, 0, SALARY )
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)
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 )
Date Functions
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.
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.
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.
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.
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
|
The following expression subtracts 10 days from each date in the
DATE_SHIPPED port:
ADD_TO_DATE (DATE_SHIPPED, 'D', -10)
The following expression subtracts 15 hours from each date in the
DATE_SHIPPED port:
ADD_TO_DATE (DATE_SHIPPED, 'HH', -15)
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.
Example: The
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)
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.
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')
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.
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’)
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.
Example: The
following expression returns the last day of the month for each date in the
ORDER_DATE port:
LAST_DAY (ORDER_DATE)
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
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' )
RETURN VALUE: Oct 10 1998
2.5 MIN
Example: The following expression returns the oldest order date
for flashlights:
MIN (ORDER_DATE, ITEM_NAME='Flashlight' )
2.6 ROUND
The ROUND function rounds one
part of a date. It is available in the Designer and the Workflow Manager.
ROUND( DATE_SHIPPED, 'MM' )
ROUND( DATE_SHIPPED, 'MON' )
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.
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 )
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_SHIPPED, 'Y' )
TRUNC (DATE_SHIPPED, 'YY' )
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.
I think this is the best blog post focusing on Informatica and its useful functions.Thank you for putting up such a nice blog post.
ReplyDeleteInformatica Read JSON