Functions In Informatica PowerCenter 6.2
Declaration
We hereby declare that this document is based on our personal experiences and experiences of our project members in our Project MHRSINFO. To the best of our knowledge, this document does not contain any material that infringes the copyrights of any other individual or organization including the customers of Infosys. Nandhakumar Nandhakumar Arunachalam, Prabhakaran C MHRSINFO Project Date: 31 January, 2004
Functions in Informatica
Page 1 of 19
Acknowledgements
We take immense pleasure in expressing our gratitude to our Project Managers Mr.Dhinakaran and Mr.Lakshmi Narasimhan CR, under whose leadership we are executing the project MHRSINFO. Loads of thanks to our Module Leader, Ms.Ragini Ms.Ragini Panjwani who guided us in learning Informatica and completing this BOK. We also take take this opportunity to thank our teammates who provided many of the inputs to create this BOK.
Thank You, Nandhakumar Arunachalam
mailto:
[email protected] Prabhakaran Prabhakaran C
mailto:
[email protected]
Functions in Informatica
Page 2 of 19
INDEX
Declaration..........................................................................................................................1 Acknowledgements.............................................................................................................2 Introduction....................................................... ............................................................................................................... ................................................................. ..........4. 1 Character Functions:.................................................. ........................................ ....... .............. ........5. 1.1 LENGTH:....................................................... .......................................................................................... ................................... ....... .............. .............. .......... ...5 1.2 LPAD: LPAD:................................................ ................................................ ........ ............... .............. ......... ..5 1.3 LTRIM: LTRIM:....................................................... ............................................................................................... ........................................ ....... .............. .............. ......... ..5 1.4 RPAD: RPAD:....................................................... ....................................................................................................... ................................................ ....... .............. .......... ...6 1.5 RTRIM:................................................... ..................................................... ............................................................ .............. .......6 1.6 SUBSTR:...................................................... .............................................................................................. ........................................ ....... .............. .............. .......7 2 Conversion Functions:................................................. ................................... ........ ............... .......... ...7 2.1 TO_CHAR:............................................... ................................................ ....... .............. .......... ...7 2.2 TO_DATE: TO_DATE:........................................................ ................................................................................................... ........................................... ....... .............. .......8 2.3 TO_DECIMAL:................................................ ........................................... ....... .............. .......9 2.4 TO_FLOA TO_FLOAT: T:............................................... .................................................. ....... .............. .......9 2.5 TO_INTEGER:................................................ .......................................... ....... .............. ......... ..9 3 Date Functions:................................................ ............................................... ....... .............. ......... ..10 10 3.1 ADD_TO_DATE ADD_TO_DATE ............................................... .................................................... ...................................................... ..10 10 3.2 DATE_COMPARE .................................................................................................11 11 3.3 DATE_DIFF ............................................................................................................11 11 3.4 GET_DA GET_DATE_P TE_PAR ART T ................................................................................. ...................................................... .................................. .......... ...12 12 3.5 LAST_DAY ............................................................................................................12 12 3.6 MAX .......................................................................................................................13 13 3.7 MIN .........................................................................................................................13 13 3.8 ROUND ..................................................................................................................14 14 3.9 SET_DA SET_DATE_P TE_PART ART ................................................... ............................................ ...14 14 3.10 TRUNC .................................................................................................................14 14 4 Special Functions: .................................................... .................................................................................................. .............................................. ....... ......... ..15 15 4.1 DECODE ................................................................................................................15 15 4.2 IIF ............................................................................................................................15 15 4.3 ERROR:....................................................... ................................................................................................... ............................................ ....... .............. ......... ..16 16 4.4 LOOKUP:................................................... .......................................... ........ ............... ........... ....17 17 5 Test Functions:................................................. .............................................. ....... .............. .......... ...17 17 5.1 ISNULL ..................................................................................................................17 17 5.2 IS_DATE .................................................................................................................17 17 5.3 IS_NUMBER ..........................................................................................................18 18 5.4 IS_SPACES .............................................................................................................18 18
Functions in Informatica
Page 3 of 19
Introduction
Informatica PowerCenter PowerCenter 6.2 is the most efficient tool in Data Warehousing. Warehousing. It highly reduces manual efforts and provides efficient outputs. Thanks to the various functions provided in Informatica, it is easy to learn and also userfriendly. friendly. The Functions in Informatica PowerCenter PowerCenter 6.2 are very much helpful in reducing human efforts to a large extent and also in evolving at the perfect solutions. There are a number of Functions in Informatica PowerCenter 6.2 such as Character Functions, Conversion Functions, Date Functions, Numerical Functions, Scientific Functions, Special Functions, Test Test Functions, Variable Functions and Lookup Functions.
Of these functions, this BOK explains only those functions, which we frequently used in our Project. These tips are mainly based on our experience and some of them are also collected from the Informatica Help Manual.
Functions in Informatica
Page 4 of 19
1
Character Functions: Functions:
1.1 LENGTH: The LENGTH function returns the number of characters in a string, including trailing blanks. It is available in the Designer and the Workflow Manager. LENGTH (string) Example: The following expression returns the length of each customer name: LENGTH (CUSTOMER_NAME) CUSTOMER_NAME
RETURN VALUE
Leonardo
8
NULL
NULL
Edwin Britto
12
1.2
LPAD:
The LPAD function adds a set of blanks or characters to the beginning of a string, to set a string to a specified length. It is available in the Designer and the Workflow Manager. LPAD LPAD (first_string, length [, second_string]) Example: The following expression standardizes numbers to five digits by padding them with leading zeros. LPAD (NUM, 5, '0') NUM
RETURN VALUE
1
00001
250
00250
1.3
LTRIM:
The LTRIM LTRIM function removes blanks or characters from the beginning of a string. It is available in the Designer and the Workflow Manager. LTRIM (string [, trim_set]) LTRIM (string) removes the leading spaces or blanks from the string. When LTRIM function is used with a trim set, which is optional, it removes the characters in the trim set from the string. Example : The following expression removes remo ves the leading zeroes in the port ITEM_CODE. LTRIM (ITEM_CODE,'0')
Functions in Informatica
Page 5 of 19
ITEM_CODE
RETURN VALUE
006
6
0803
803
* The LTRIM function can be nested when needed to remove multiple characters.
1.4
RPAD:
The RPAD function converts a string to a specified length by adding blanks or characters to the end of the string. It is available in the Designer and the Workflow Manager. RPAD( RPAD( first_string, length [, second_string ] ) Example: The following expression expression returns the string with a length of 5 characters, appending the string ':' to the end of each word: RPAD (WORD, 5, ':’) WORD
RETURN VALUE
Date
Date:
Time
Time:
1.5
RTRIM:
The RTRIM function removes removes blanks or characters from the end of a string. It is available in the Designer and the Workflow Manager. RTRIM (string [, trim_set]) The RTRIM function can be combined with the LENGTH function if the trailing blanks are to be ignored. It can also be nested when needed to remove multiple characters. RTRIM (string) removes the trailing spaces or blanks from the string. When RTRIM function is used with a trimset, which is optional, it removes the characters in the trimset from the string. For example, RTRIM (ITEM_CODE,'10') The above expression removes the characters 10 in the port ITEM_CODE. ITEM_CODE
RETURN VALUE
0610
06
380
38
In the second example the function removes the trailing zero since the RTRIM compares the first character in the trimset with the last character of the string, since
Functions in Informatica
Page 6 of 19
it does not match it takes the second character in the trimset and compares with last character of the string. Since it matches it removes it. 1.6
SUBSTR:
The SUBSTR function returns a portion of a string. It is available in the Designer and the Workflow Manager. SUBSTR( string, start [, length ] ) The SUBSTR may not give the desired result if the string on which it is used is not trimmed. Though it is always a good practice to trim the strings before using them in any expression, it becomes extremely extremely important to trim them if they are used in a SUBSTR function. For example, if there is a function SUBSTR (NAME, 2,2) It will not return the 2,3 characters of the NAME if the port has leading spaces. In this case LTRIM becomes essential. SUBSTR(LTRIM(NAME),2,2) The SUBSTR function can also be used to get the last few characters as described below. SUBSTR(NAME,-3,3) This function will return the last three characters of the string. But it may not return the required last three characters if the port has trailing blanks, hence RTRIM RTRIM is essential. SUBSTR(RTRIM(NAME),-3,3) Hence it is always better to trim the strings before using them in a SUBSTR function. SUBSTR(LTRIM(RTRIM(NAME)),3,2) The above expression will get the 3,4 character of the port NAME irrespective irrespective of whether the port has leading or trailing blanks or not.
2
Conversion Functions: Functions:
2.1 TO_CHAR: The TO_CHAR function converts numeric values and dates to text strings. It is available in the Designer and the Workflow Manager. TO_CHAR( numeric_value ) TO_CHAR (date [, format ] ) Example : The following expression converts the values in the SALES port to text: TO_CHAR (SALES ) SALES
RETURN VALUE
1800.03
'1800.03'
-22.57891
'-22.57891'
Functions in Informatica
Page 7 of 19
The following expression converts the dates in the DATE_PROMISED DATE_PROMISED port to text in the format MON DD YYYY: TO_CHAR (DATE_PROMISED, (DATE_PROMISED, 'MON DD YYYY' ) DATE_PROMISED
RETURN VALUE
Apr 1 1998 12:00:10AM
'Apr 01 1998'
If we omit the format_string argument, TO_CHAR TO_CHAR returns a string in the default date format ‘MM/DD/YYYY’. We can use Conversion functions with DATE DATE functions in order to do some calculations. The following composite expression converts the string DATE_PROMISED DATE_PROMISED to date, adds 1 to it and then converts the same to text string with the format YYYYMMDD. TO_CHAR(ADD_TO_DATE(TO_DATE(DATE_PROMISED),'DD',1),'YYYYMMDD') Test functions can also be used with Conversion functions. The following expression uses IS_DATE along with TO_CHAR. IS_DATE(TO_CHAR(DATE_PROMISED,'YYYYMMDD')) * TO_CHAR returns NULL if invalid Date is passed to the function.
2.2 TO_DATE: The TO_DATE TO_DATE function converts a character string to a date datatype in the same format as the character string. It is available in the Designer and the Workflow Manager. TO_DATE( string [, format ] ) Example : The following expression returns date values for the strings in the DATE_PROMISED port. TO_DATE always returns a date and time. If we pass a string that does not have a time value, the date returned always includes the time 00:00:00. If we execute a session in the twentieth century, the century will be 19. The current year on the machine running the Informatica Server is 1998: TO_DATE( DATE_PROMISED, 'MM/DD/YY' ) DATE_PROMISED
RETURN VALUE
'12/28/81'
Dec 28 1981 00:00:00
NULL
NULL
The format of the string must exactly be the format given in the TO_DATE TO_DATE function. * TO_DATE function fails if invalid date entries are given. To avoid this we must use IS_DATE IS_DATE function to check if the string has a valid date to be converted.
Functions in Informatica
Page 8 of 19
2.3 TO_DECIMAL: The TO_DECIMAL function converts any value (except binary) to a decimal. It is available in the Designer. TO_DECIMAL( value [, scale ] ) Example : This expression expression uses values from the port IN_TAX. IN_TAX. The datatype is decimal with precision of 10 and scale of 3: TO_DECIMAL( IN_TAX, 3 ) IN_TAX
RETURN VALUE
'15.6789'
15.678
NULL
NULL
'A12.3Grove'
0
We can also use two conversion functions together in a single expression. The following expression uses the functions TO_DECIMAL and TO_CHAR. TO_CHAR. TO_DECIMAL(TO_CHAR(DA TO_DECIMAL(TO_CHAR (DATE_PROMISED,'YYYYMMDD')) TE_PROMISED,'YYYYMMDD'))
2.4 TO_FLOAT: The TO_FLOAT TO_FLOAT function converts any value (except binary) to a double-precision floating point number (the Double datatype). It is available in the Designer and the Workflow Manager. TO_FLOAT( value ) Example : This expression uses values from the port IN_TAX: TO_FLOAT( IN_TAX ) IN_TAX
RETURN VALUE
'15.6789'
15.6789
NULL
NULL
2.5 TO_INTEGER: The TO_INTEGER function converts any value (except binary) to an integer by rounding the decimal portion of a value. It is available in the Designer and the Workflow Manager. TO_INTEGER( value ) Example : This expression uses values from the port IN_TAX: TO_INTEGER( IN_TAX ) IN_TAX
RETURN VALUE
'15.6789'
16
'60.2'
60
Functions in Informatica
Page 9 of 19
3
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 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, example, if we pass 12-APR-1997 to a date function,, we can use MM, MON, or MONTH to specify APR. function SS , SSSS Second portion of date (0 to 59). Y, YY, YYY, YYYY , RR Year 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.
3.1
ADD_TO D_TO_D _DA ATE
The ADD_TO_DATE 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 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
Functions in Informatica 19
Page 10 of
The following expression subtracts 10 days from each date in the DATE_SHIPPED 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 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 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
3.2
Apr 30 1998 6:24:45PM
DAT DA TE_CO E_COMP MPA ARE
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 ) Example : The following expression compares each date in the DATE_PROMISED and DATE_SHIPPED ports, and returns an integer indicating which date is earlier: DA DATE_COMPARE ( DATE_PROMISED, DATE_SHIPPED ) DATE_PROMISED Jan 1 1997
DATE_SHIPPED Jan 13 1997
RETURN VALUE -1
Feb 1 1997
Feb 1 1997
0
Dec 22 1997
Dec 15 1997
1
3.3
DATE_DIFF
The DATE_DIFF 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 )
Functions in Informatica 19
Page 11 of
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 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 IS_DATE and then passed to them if valid. 3.4 3.4
GET_ GET_DA DATE TE_P _PAR ART T
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 expressions return the day for each date in the DATE_SHIPPED port: GE GET_DATE_PART ( DATE_SHIPPED, 'D' ) GEGET_DATE_PART GE GET_DATE_PART ( DATE_SHIPPED, 'DD' ) DATE_SHIPPED
RETURN VALUE
Mar 13 1997 12:00:00AM
13
June 3 1997 11:30:44PM NULL
3.5
3 NULL
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 ) Example : The following expression returns the last day of the month for each date in the ORDER_DATE port: LAST_DAY( ORDER_DATE )
ORDER_DATE
Functions in Informatica 19
RETURN VALUE
Page 12 of
Apr 1 1998 12:00:00AM Jan 6 1998 12:00:00AM
Apr 30 1998 12:00:00AM Jan 31 1998 12:00:00AM
DATE functions combine with Conversion functions also. The following expression has LAST_DAY LAST_DAY and TO_DATE TO_DATE functions nested or combined together. LAST_DAY( TO_DATE( GIVEN_DATE, 'DD-MON-YY' ))
3.6
MAX
The MAX function returns the latest date found in a group. It is available in the Designer. MAX( date, filter_condition ) 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
3.7
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
Functions in Informatica 19
Page 13 of
Halogen Flashlight
Feb 1 1998
Flashlight
Oct 10 1998
RETURN VALUE: Feb 1 1998
3.8
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 expressions round the month portion por tion 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, Year, Day or Time portions. 3.9 3.9
SET_ SET_DA DATE TE_P _PAR ART T
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 expressions change the month to June for the dates in the DATE_PROMISED DATE_PROMISED port. The Informatica Server displays an error when we try tr y 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 Ja Jan 1 1997 1997 12:1 12:15: 5:56 56A AM NULL
RETURN VALUE Jun Jun 1 199 1997 12 12:15: :15:56 56AM AM NULL
Similarly the SET_DATE_PART function can be used to round off Year, Day or Time portions. 3.10 3.10 TRUN TRUNC C The TRUNC function truncates dates to a specific year, month, day, hour, or minute. It is available in the Designer and the Workflow Manager.
Functions in Informatica 19
Page 14 of
TRUNC( date [, format ] ) Example: The following expressions expressions truncate the year portion of dates in the DATE_SHIPPED port: TRUNC( DATE_SHIPPED, 'Y' ) TRUNC( DATE_SHIPPED, 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.
4
Special Functions: 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_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', 'Regulator', 20, 'Knife', 40, 'Tank', 'NONE' ) ITEM_ID
RETURN VALUE 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, SALAR SAL ARY Y) SALES
SALARY
RETURN VALUE
150
50,000.00
50,000
50
20,000.00
0
Functions in Informatica 19
Page 15 of
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 increase we will be able to appreciate 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 Ordering is important in IIF and DECODE functions. 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 salary for employees in all departments of your company, but skips negative values. The following expression nests the ERROR function in an IIF expression 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 ) SALAR SALARY Y RETU RETURN RN VALUE ALUE
Functions in Informatica 19
Page 16 of
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 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 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_NAME, :TD.SALES.ITEM_ID, 10, :TD.SALES.PRICE, 15.99 ) ITEM_NAME
ITEM_ID
PRICE
Regulator
5
100.00
Flashlight
10
15.99
5
Test Functions:
5.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 ISNULL ( ITEM_NAME ) ITEM_NAME
RETURN VALUE
Flashlight
0 (FALSE)
NULL
1 (TRUE)
''
0 (FALSE) Empty string is not NULL 5.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.
Functions in Informatica 19
Page 17 of
IS_DATE( value ) Example : The following expression expression checks the INVOICE_DATE 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/ '02/31 31/1 /199 998 8 12:1 12:13: 3:55 55''
0 (FAL (FALSE SE)) (Feb (Febru ruar ary y does does not not have have 31 days 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 taken as the default format.
5.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 ) Example : The 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
5.4
IS_SPACES
The IS_SPACES IS_SPACES function returns whether a value consists entirely of spaces. It is available in the Designer and the Workflow Manager.
Functions in Informatica 19
Page 18 of
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 VA VALUE
Flashlight
0 (False) 1 (True)
Regulator system
0 (False)
For further details contact Ms. Ragini Panjwani Infosys Technologies Limited Phone : 91 44 24509530 Extn : 82377 Email : Ragini_Panjwani
[email protected] @infosys.com
Functions in Informatica 19
Page 19 of