Transformation Language Reference
INFORMATICA® POWERMART® 5 INFORMATICA® POWERCENTER® 5 (VERSION 5.1)
Informatica PowerMart/PowerCenter Transformation Language Reference Version 5.1.0 June 2001 Copyright (c) 2001 Informatica Corporation. All rights reserved. Printed in the USA. This software and documentation contain proprietary information of Informatica Corporation, they are provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited. No part of this document may be reproduced or transmitted in any form, by any means (electronic, photocopying, recording or otherwise) without prior consent of Informatica Corporation. Use, duplication, or disclosure of the Software by the U.S. Government is subject to the restrictions set forth in the applicable software license agreement as provided in DFARS 227.7202-1(a) and 227.7702-3(a) (1995), DFARS 252.227-7013(c)(1)(ii) (OCT 1988), FAR 12.212(a) (1995), FAR 52.227-19, or FAR 52.227-14 (ALT III), as applicable. The information in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. Informatica Corporation does not warrant that this documentation is error free. Informatica, PowerMart, PowerCenter, PowerChannel, PowerConnect, PowerPlug, PowerBridge, and MX are trademarks or registered trademarks of Informatica Corporation in the United States and in jurisdictions throughout the world. All other company and product names may be trade names or trademarks of their respective owners. Portions of this software are copyrighted by MERANT, 1991-2000. Apache Software This product includes software developed by the Apache Software Foundation (http://www.apache.org/). The Apache Software is Copyright (c) 1999-2000 The Apache Software Foundation. All rights reserved. Redistribution and use in source and binary forms of the Apache Software, with or without modification, are permitted provided that the following conditions are met: 1. Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer. 2. Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution. 3. The end-user documentation included with the redistribution, if any, must include the following acknowledgment: “This product includes software developed by the Apache Software Foundation (http://www.apache.org/).” Alternately, this acknowledgment may appear in the software itself, if and wherever such third-party acknowledgments normally appear. 4. The names “Xerces” and “Apache Software Foundation” must not be used to endorse or promote products without prior written permission of the Apache Software Foundation. 5. Products derived from this software may not be called “Apache”, nor may “Apache” appear in their name, without prior written permission of the Apache Software Foundation. THE APACHE SOFTWARE IS PROVIDED “AS IS” AND ANY EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
The Apache Software consists of voluntary contributions made by many individuals on behalf of the Apache Software Foundation and was originally based on software copyright (c) 1999, International Business Machines, Inc., http://www.ibm.com. For more information on the Apache Software foundation, please see http://www.apache.org/. DISCLAIMER: Informatica Corporation provides this documentation “as is” without warranty of any kind, either express or implied, including, but not limited to, the implied warranties of non-infringement, merchantability, or use for a particular purpose. The information provided in this documentation may include technical inaccuracies or typographical errors. Informatica could make improvements and/or changes in the products described in this documentation at any time without notice.
Table of Contents /LVWRI7DEOHV L[ 3UHIDFH [L About Informatica Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xii About this Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiv About PowerCenter and PowerMart . . . . . . . . . . . . . . . . . . . . . . . . . . .xiv Document Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv Other Informatica Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xvi Accessing the Informatica Webzine . . . . . . . . . . . . . . . . . . . . . . . . . . . .xvi Visiting the Informatica Web Site . . . . . . . . . . . . . . . . . . . . . . . . . . . .xvi Visiting the Informatica Developer Network . . . . . . . . . . . . . . . . . . . . .xvi Obtaining Technical Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii New Features and Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xviii Version 5.1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xviii Version 5.0 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xix
&KDSWHU 7KH7UDQVIRUPDWLRQ/DQJXDJH Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Transformation Language Components . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Transformation Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Internationalization and the Transformation Language . . . . . . . . . . . . . . 3 Expression Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Expression Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 General Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 Adding Comments to Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Reserved Words . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
&KDSWHU &RQVWDQWV DD_DELETE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 DD_INSERT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 DD_REJECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
LL L
DD_UPDATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 FALSE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 NULL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Null Values in Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Null Values in Filter Conditions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Nulls with Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 TRUE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
&KDSWHU 2SHUDWRUV Operator Precedence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 Arithmetic Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Using Arithmetic Operators to Convert Data . . . . . . . . . . . . . . . . . . . . 19 String Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Nulls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Comparison Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 Logical Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Nulls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
&KDSWHU 9DULDEOHV System Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 PROC_RESULT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 $$$SessStartTime . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 SYSDATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 SESSSTARTTIME . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Local Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
&KDSWHU 'DWHV Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 Date/Time Datatype . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 Julian Day, Modified Julian Day, and the Gregorian Calendar . . . . . . . . 32 Dates in the Year 2000 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 Dates in Relational Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 Dates in Flat Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 Default Date Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 LY
7DE OHRI&RQWHQWV
Date Format Strings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 TO_CHAR Format Strings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 TO_DATE and IS_DATE Format Strings . . . . . . . . . . . . . . . . . . . . . . . . . . 41 Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 Understanding Date Arithmetic . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
&KDSWHU )XQFWLRQV Function Categories . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 Aggregate Functions and Nulls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 Character Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 Conversion Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 Date Functions
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
Numerical Functions
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Scientific Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 Special Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 Test Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 Variable Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 ABORT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 ABS
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
ADD_TO_DATE ASCII
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
AVG
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
CEIL
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
CHR
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
CHRCODE
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
CONCAT COS
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
COSH
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
COUNT CUME
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
DATE_COMPARE DATE_DIFF DECODE
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
7DE O HRI & R Q WH Q WV
Y
ERROR EXP
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
FIRST . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 FLOOR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86 GET_DATE_PART
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
IIF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89 INITCAP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93 INSTR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94 ISNULL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97 IS_DATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98 IS_NUMBER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 IS_SPACES . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103 LAST . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104 LAST_DAY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106 LENGTH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108 LN
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
LOG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110 LOOKUP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111 LOWER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113 LPAD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114 LTRIM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116 MAX (Dates) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118 MAX (Numbers) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119 MEDIAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121 MIN (Dates) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123 MIN (Numbers) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125 MOD
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
MOVINGAVG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129 MOVINGSUM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131 PERCENTILE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133 POWER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136 ROUND (Dates) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 ROUND (Numbers) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140 RPAD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142 RTRIM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144 SETCOUNTVARIABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146 SET_DATE_PART . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148 SETMAXVARIABLE YL
7DE OHRI&RQWHQWV
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
SETMINVARIABLE SETVARIABLE SIGN
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
SIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158 SINH
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159
SQRT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160 STDDEV . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161 SUBSTR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163 SUM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166 TAN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168 TANH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169 TO_CHAR (Dates) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170 TO_CHAR (Numbers) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174 TO_DATE
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
TO_DECIMAL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180 TO_FLOAT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181 TO_INTEGER
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
TO_NUMBER
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
TRUNC (Dates) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184 TRUNC (Numbers) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187 UPPER
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189
VARIANCE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
$SSHQGL[$ )XQFWLRQ4XLFN5HIHUHQFH Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194 Character Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195 Conversion Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196 Date Functions
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197
Numeric Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198 Scientific Functions
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
Special Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200 Test Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201 Variable Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202
,QGH[
7DE O HRI & R Q WH Q WV
YLL
YLLL
7DE OHRI&RQWHQWV
List of Tables Table Table Table Table Table Table Table Table Table Table Table Table Table
1-1. 3-1. 3-2. 3-3. 3-4. 3-5. 4-1. 5-1. 5-2. 5-3. 5-4. 5-5. 5-6.
Reference Qualifiers in Transformation Language . . . . . Operators in the Transformation Language . . . . . . . . . . Arithmetic Operators in the Transformation Language . Converting Data with Arithmetic Operators . . . . . . . . . Comparison Operators in the Transformation Language Logical Operators in the Transformation Reference . . . . Datatype Expansion for $$$SessStartTime . . . . . . . . . . RR Format String Conversions . . . . . . . . . . . . . . . . . . Differences Between RR and YY Format Strings . . . . . . Date Format Strings in the Transformation Reference . . Date Functions that Use Date Format Strings . . . . . . . . TO_CHAR Format Strings . . . . . . . . . . . . . . . . . . . . . TO_DATE and IS_DATE Format Strings . . . . . . . . . .
... ... ... ... ... ... ... ... ... ... ... ... ...
.. .. .. .. .. .. .. .. .. .. .. .. ..
.. .. .. .. .. .. .. .. .. .. .. .. ..
.. .. .. .. .. .. .. .. .. .. .. .. ..
.. .. .. .. .. .. .. .. .. .. .. .. ..
... ... ... ... ... ... ... ... ... ... ... ... ...
.. .. .. .. .. .. .. .. .. .. .. .. ..
.. .. .. .. .. .. .. .. .. .. .. .. ..
.. .. .. .. .. .. .. .. .. .. .. .. ..
. 4 18 19 19 22 23 27 34 34 37 37 38 41
/L V WR I 7D E OH V
L[
[
/L V W RI 7DE O HV
Preface Welcome to PowerMart and PowerCenter, Informatica’s integrated suite of software products that deliver an open, scalable solution addressing the complete life cycle for data warehouse and analytic application development. Both PowerMart and PowerCenter combine the latest technology enhancements for reliably managing data repositories and delivering information resources in a timely, usable manner. The metadata repository coordinates and drives a variety of core functions including extraction, transformation, loading, and management. The Informatica Server can extract large volumes of data from multiple platforms, handle complex transformations, and support high-speed loads. PowerMart and PowerCenter can simplify and accelerate the process of moving data warehouses from development to test to full production.
[ L
About Informatica Documentation The complete set of printed documentation for PowerMart and PowerCenter includes the following books: ♦
Designer Guide. Provides information needed to use the Designer. Includes information to help you create mappings, mapplets, and transformations. Also includes a description of the transformation datatypes used to process and transform source data.
♦
Getting Started. Provides basic tutorials for getting started. Also contains documentation about the sample repository.
♦
Installation and Configuration Guide. Provides information needed to install and configure the Informatica tools, including details on environment variables and database connections.
♦
Metadata Reporter Guide. Provides information on how to install and use the web-based Metadata Reporter to generate reports on the metadata in PowerMart and PowerCenter repositories.
♦
Repository Guide. Provides information needed to administer the repository using the Repository Manager or the pmrep command line program. Includes details on functionality available in the Repository Manager, such as creating and maintaining repositories, folders, users, groups, and permissions and privileges.
♦
Session and Server Guide. Provides information to help you create and run sessions in the Server Manager, as well as administer the Informatica Server. Also contains information on tuning sessions and the Informatica Server for better performance.
♦
Transformation Language Reference. Provides syntax descriptions and examples for each transformation function provided with PowerMart and PowerCenter.
♦
Troubleshooting Guide. Provides error messages that you might encounter while using PowerMart or PowerCenter. Each error message includes one or more possible causes and actions that you can take to correct the condition.
Documentation available with our other products includes:
vi
Preface
♦
Informatica® Metadata Exchange SDK User Guide. Provides information about the second generation of Metadata Exchange interfaces for PowerMart and PowerCenter repositories.
♦
Informatica® PowerChannel™ User Guide. Provides information on how to transport compressed and encrypted data through a secure channel.
♦
PowerCenter™ Integration Server for BW Installation Guide. Provides information to install and configure PCISBW for loading source data into a SAP Business Warehouse. It also contains basic instructions for loading into BW.
♦
PowerConnect™ for IBM ® DB2® Installation Guide. Provides information on setting up a mainframe environment to work with PowerCenter, including connectivity issues for mainframe and DB2 databases.
PowerConnect™ for IBM® MQSeries® User and Administrator Guide. Provides information to install PowerConnect for IBM MQSeries, build mappings, extract data from message queues, and load data to message queues.
PowerConnect™ for PeopleSoft® User and Administrator Guide. Provides information to install PowerConnect for PeopleSoft, extract data from PeopleSoft, build mappings, and run sessions to load PeopleSoft source data into your data warehouse.
PowerConnect™ for SAP™ R/3® User and Administrator Guide. Provides information to install PowerConnect for SAP R/3, extract data from SAP R/3, build mappings, and run sessions to load SAP R/3 data into a data warehouse.
PowerConnect™ for Siebel® User and Administrator Guide. Provides information to install PowerConnect for Siebel, extract data from Siebel systems, build mappings, and run sessions to load Siebel source data into a data warehouse.
PowerPlug™ User Guide. Provides information on how to use PowerPlug to extract metadata from leading data modeling tools and import it into PowerMart/PowerCenter repositories through Informatica Metadata Exchange SDK.
PowerBridge™ for Hyperion® Essbase® Server User Guide. Provides information on how to use PowerBridge to export multi-dimensional metadata from PowerMart/PowerCenter repositories into the Hyperion Integration Server through Informatica Metadata Exchange SDK.
$E R X W,Q IR U PD W LF D' RF X P HQ W DW LR Q
[LLL
About this Book The Transformation Language Reference is written for the developers who are responsible for building mappings to load a data warehouse. The Transformation Language Reference assumes you have knowledge of SQL, relational database concepts, and the interface requirements for your supporting applications. The online help and Adobe Acrobat versions of this book contain material for PowerMart 5.1/ PowerCenter 5.1. The printed version of this book contains material for PowerMart 5.0/ PowerCenter 5.0. To view printed material for the new features in PowerMart 5.1/PowerCenter 5.1, refer to the PowerMart/PowerCenter Supplemental Guide. The material in this book is available for online use.
About PowerCenter and PowerMart This guide contains information about both PowerMart and PowerCenter. The documentation explicitly mentions software features that differ between the two products.
If You Are Using PowerCenter With PowerCenter, you receive all product functionality, including the ability to register multiple servers, share metadata across repositories, and partition data. A PowerCenter license lets you create a single repository that you can configure as a global repository, the core component of a data warehouse. When this guide mentions a PowerCenter Server, it is referring to an Informatica Server with a PowerCenter license.
If You Are Using PowerMart This version of PowerMart includes all features except distributed metadata, multiple registered servers, and data partitioning. Also, the various options available with PowerCenter (such as PowerCenter Integration Server for BW, PowerConnect for IBM DB2, PowerConnect for IBM MQSeries, PowerConnect for SAP R/3, PowerConnect for Siebel, and PowerConnect for PeopleSoft) are not available with PowerMart. When this guide mentions a PowerMart Server, it is referring to an Informatica Server with a PowerMart license.
[LY
3UHIDFH
Document Conventions This guide uses the following formatting conventions: ,I\RX VHH «
italicized text
,WP HDQV«
7KHZRUGRUVHWRIZRUGVDUHHVSHFLDOO\HPSKDVL]HG
boldfaced text
italicized monospaced text
(PSKDVL]HGVXEMHFWV
7KLVLVWKHYDULDEOHQDPHIRUDYDOXH\RXHQWHUDVSDUWRIDQ RSHUDWLQJV\VWHPFRPPDQG7KLVLVJHQHULFWH[WWKDWVKRXOGEH UHSODFHGZLWKXVHUVXSSOLHGYDOXHV
1RWH
7KHIROORZLQJSDUDJUDSKSURYLGHVDGGLWLRQDOIDFWV
7LS
7KHIROORZLQJSDUDJUDSKSURYLGHVVXJJHVWHGXVHV
:DUQLQJ
7KHIROORZLQJSDUDJUDSKQRWHVVLWXDWLRQVZKHUH\RXFDQRYHUZULWH RUFRUUXSWGDWDXQOHVV\RXIROORZWKHVSHFLILHGSURFHGXUH
monospaced text
7KLVLVDFRGHH[DPSOH
bold monospaced text
7KLVLVDQRSHUDWLQJV\VWHPFRPPDQG\RXHQWHUIURPDSURPSWWR H[HFXWHDWDVN
$ E RX W W KL V% RR N
[Y
Other Informatica Resources In addition to the product manuals, Informatica provides these other resources:
Informatica Webzine Informatica web site Informatica Developer Network Informatica Technical Support
Accessing the Informatica Webzine The Informatica Documentation Team delivers an online journal, the Informatica Webzine. This journal provides solutions to common tasks, conceptual overviews of industry-standard technology, detailed descriptions of specific features, and tips and tricks to help you develop data warehouses. You can access the webzine at: http://www.informatica.com/webzine The Informatica Webzine is a password-protected site. Informatica has an online registration form for login accounts to its webzine and web support. To register for an account, go to the following URL: http://www.informatica.com/support/webpaswdreq.html If you have any questions, please email
[email protected]. To better serve your needs, the Informatica Documentation Team welcomes all comments and suggestions. You can send comments and suggestions to:
[email protected]
Visiting the Informatica Web Site You can access Informatica’s corporate web site at http://www.informatica.com. The site contains information about Informatica, its background, upcoming events, and locating your closest sales office. You will also find product information, as well as literature and partner information. The services area of the site includes important information on technical support, training and education, and implementation services.
Visiting the Informatica Developer Network The Informatica Developer Network is a web-based forum for third-party software developers. You can access the Informatica Developer Network at http://devnet.informatica.com. The site contains information on how to create, market, and support customer-oriented add-on solutions based on Informatica’s interoperability interfaces.
[YL
3UHIDFH
Obtaining Technical Support There are many ways to access Informatica technical support. You can call or email your nearest Technical Support Center listed below or you can use our WebSupport Service. Both WebSupport and our Customer Site require a user name and password. To receive a user name and password, please contact us at
[email protected] or call 650-687-6300. 1RUWK$PHULFD6RXWK$ PHULFD
$IULFD$VLD$XVWUDOLD(XURS H
,QIRUPDWLFD&RUSRUDWLRQ
,QIRUPDWLFD6RIWZDUH/WG
:%D\VKRUH5G
:DOWKDP3DUN
3DOR$OWR&$
:DOWKDP5RDG:KLWH:DOWKDP
3KRQH
0DLGHQKHDG%HUNVKLUH
)D[
6/71
+RXUV DPSPSVW
3KRQH
HPDLO VXSSRUW#LQIRUPDWLFDFRP
8. *HUPDQ\ 6ZLW]HUODQG )D[ +RXUV DPSPJPW HPDLO VXSSRUWBXN#LQIRUPDWLFDFRP
2 W K HU , QI R UP D WL FD 5H V RX U FH V
[YLL
New Features and Enhancements This section describes new features and enhancements to PowerMart and PowerCenter.
Version 5.1 The following list describes new features and enhancements in PowerMart 5.1/PowerCenter 5.1.
Performance Enhancements High precision decimal arithmetic. The Informatica Server optimizes data throughput to increase performance of sessions using the Enable Decimal Arithmetic option.
To_Decimal and Aggregate functions. The Informatica Server uses improved algorithms to increase performance of To_Decimal and all aggregate functions such as percentile, median, and average.
Cache management. The Informatica Server uses better cache management to increase performance of Aggregator, Joiner, Lookup, and Rank transformations.
Partition sessions with sorted aggregation. You can partition sessions with Aggregator transformation that use sorted input. This improves memory usage and increases performance of sessions that have sorted data.
Relaxed Data Code Page Validation When enabled, the Informatica Client and Informatica Server lift code page selection and validation restrictions. You can select any supported code page for source, target, lookup, and stored procedure data.
Designer Features and Enhancements Debug mapplets. You can debug a mapplet within a mapping in the Mapping Designer. You can set breakpoints in transformations in the mapplet.
Support for slash character (/) in table and field names. You can use the Designer to import source and target definitions with table and field names containing the slash character (/). This allows you to import SAP BW source definitions by connecting directly to the underlying database tables.
Server Manager Features and Enhancements Continuous sessions. You can schedule a session to run continuously. A continuous session starts automatically when the Load Manager starts. When the session stops, it restarts immediately without rescheduling. Use continuous sessions when reading real time sources, such as IBM MQSeries.
[YLLL
3UHIDFH
Partition sessions with sorted aggregators. You can partition sessions with sorted aggregators in a mapping.
Register multiple servers against a local repository. You can register multiple PowerCenter Servers against a local repository.
Version 5.0 The following list describes new features and enhancements in PowerMart 5.0/PowerCenter 5.0.
Designer Features and Enhancements Debugger. You can debug a mapping from the Mapping Designer. Using the Debugger Wizard, choose to run the Debugger against an existing session in debug mode or create a debug session. Before you debug a mapping, set breakpoints in the mapping where you want the Informatica Server to evaluate data and error conditions. The Debugger pauses when a breakpoint evaluates to true, and you can change transformation output data.
Double-click folder icon to open the folder and tool. When you double-click a folder icon in the Navigator window, the Designer opens the folder and displays the last tool that was active within that folder.
Find in Workspace tool. You can search for text strings in tables, columns, or ports across all open objects in the workspace.
Full screen view. You can view the workspace over the entire screen. The Designer hides the menu, the Navigator and Output windows, and the title bar to maximize the workspace window.
Invalid mapping icon. The Designer displays a new icon for invalid mappings in the Navigator window.
Lookup cache. You can use a dynamic lookup cache in a Lookup transformation. When you use a dynamic lookup cache, the Informatica Server updates the lookup cache during the session run. Also, you can share persistent lookup caches within and across mappings by naming the cache files.
Mapping parameters and variables. Create mapping parameters and variables in a mapplet or mapping to make mappings more flexible. Mapping parameters and variables represent values in mappings and mapplets. Use mapping parameters and variables to reduce the overhead of creating multiple mappings when only certain attributes of a mapping need to change.
New Designer shortcut keys. New shortcut keys allow you to cut, copy, paste, and delete rows when you edit a repository object.
New workspace look. You can select two colors to create a color gradient for the title bars of repository objects. Other components of the workspace, such as links, arrows, and tool title, also have a sharper, three-dimensional look.
Object import and export. You can export objects from a repository and import objects into a repository. When you export a repository object, the Designer or Server Manager
1 H Z) HD W XU H VDQ G ( Q KD Q F HP H Q WV
[L[
creates an XML file describing the repository metadata. Use the XML file to import repository objects into a repository. You can export and import sources, targets, transformations, mapplets, mappings, and sessions.
Preview data. The Designer allows you to preview data for relational sources, flat file sources, and relational targets.
Retain relationships and source or target definition descriptions when re-importing a source or target definition. When you re-import a source or target definition, you have the option of retaining primary and foreign key relationships and source or target descriptions.
Resizable Expression Editor. You can resize the Expression Editor by dragging the corner of the dialog box.
Router transformation. The Designer allows you to use a Router transformation to test data based on multiple conditions. The Router transformation allows you to route groups of data to a transformation or a target.
Save mappings and mapplets under a different name. The Designer allows you to save mappings and mapplets under a different name within the same folder.
XML sources and targets. You can use XML data as a source or target. You can create source and target definitions from an XML, DTD, or XML schema file and use them in mappings. When you run a session, you can read data from an XML source file and validate it against a DTD. You can also write data from any type of source to an XML file.
Informatica Server Enhancements Expanded pmcmd capability. You can use the command line program pmcmd to specify a parameter file when you start a session or batch. This allows you to change the values of session parameters, and mapping parameters and variables at runtime.
Parallel data processing. Available with PowerCenter only. If you run the Informatica Server on a symmetric multi-processing system, you can use multiple CPUs to process a session concurrently. You configure partitions in the session properties based on source qualifiers. The Informatica Server reads, transforms, and writes partitions of data in parallel for a single session.
Process session data using threads. The Informatica Server runs on two processes: the Load Manager process and the DTM process. The DTM process creates threads to perform session tasks, such as reading, transforming, and writing data as well as performing pre-and post-session operations.
Metadata Reporter The Metadata Reporter is a web-based application that enables you to run reports against repository metadata. With the Metadata Reporter, you can access information about your repository without having knowledge of SQL, the transformation language, or the underlying tables in the repository.
[[
3UHIDFH
Repository Manager Features and Enhancements Compare Folders. You can compare the contents of folders in the same repository or across different repositories.
Copy and Replace Folder Wizard. You can maintain shortcuts from local to global repositories when you copy and replace folders. When copying folders, you can select the shared folder to associate shortcuts.
Display user privileges. The Manage Privileges dialog box displays all user and group privileges so you can assign and revoke privileges by selecting the appropriate option.
pmrep. A command-line utility for Windows NT/2000 that allows you to perform repository tasks and change session-related parameters. Using pmrep, you can connect to, back up, delete, and restore a repository. Additional pmrep commands allow you to update database connectivity information for a repository, update email address information for session notification email, and update server variables.
Server Manager Features and Enhancements Additional email variables. With new email variables, you can configure post-session email to include information, such as the mapping used during the session.
Additional server variables. You can configure several new server variables when registering a server. Server variables such as $PMSuccessEmailUser allow you to configure sessions without hard-coding information.
Copy session. You can copy sessions across folders and repositories using the Copy Session Wizard in the Server Manager.
Enhanced user security. You can set read, write, and execute permissions for users and groups to restrict access to database, FTP, and external loader connections.
Global validation of sessions. You can validate a specified set of sessions in a folder or validate all of the invalid sessions in a folder.
Read-only privileges for sessions. You can set user and group permissions and privileges that allow users to view, but not edit, session properties.
Session parameters. Create session parameters to reuse sessions. You can create parameters for database connections, reject files, and file sources and targets. You can also use a builtin session parameter for the session log. Define session parameters in the session parameter file.
Sybase IQ 12 External Loader. You can use Sybase IQ 12 external loaders to load multibyte data. Null characters and delimiters can be up to four bytes long.
Teradata External Loader. You can use Teradata external loaders to load single-byte data. The Teradata external loader uses ASCII-based single-byte code pages.
Transformation Language Enhancements You can use two new built-in system variables with PowerMart and PowerCenter:
1 H Z) HD W XU H VDQ G ( Q KD Q F HP H Q WV
[[L
SESSSTARTTIME. Returns the system date value on the machine hosting the Informatica Server when the server initializes the session. You can use SESSSTARTTIME within any function that accepts transformation date/time datatypes using the transformation language.
$$$SessStartTime. Returns the system date value on the machine hosting the Informatica Server when the server initializes the session. You can use $$$SessStartTime at the mapping level in the filter condition, user-defined join, or SQL override of a source qualifier. You can also use $$$SessStartTime in session-level overrides for mapping attributes. You can also use four new variable functions with mapping variables:
SetCountVariable. Counts the number of evaluated rows and increment or decrement a mapping variable for each row.
SetMaxVariable. Evaluates the value of a mapping variable to the higher of two values. SetMinVariable. Evaluates the value of a mapping variable to the lower of two values. SetVariable. Sets the value of a mapping variable to a specified value.
[[LL
3UHIDFH
&KDSWHU
The Transformation Language This chapter provides a general overview of the transformation language. It includes the following topics:
Overview, 2 Expression Syntax, 4 Adding Comments to Expressions, 7 Reserved Words, 8
For information about supported datatypes and data conversions, see “Datatype Reference” in the Designer Guide.
Overview PowerCenter and PowerMart provide a transformation language to help you write expressions to transform source data. The transformation language includes SQL-like functions so you can write expressions that modify data or test whether data matches conditions. You can write expressions in the following transformations:
Aggregator Expression Filter Rank Router Update Strategy
Transformation Language Components The transformation language includes the following components to help you create simple or complex transformation expressions:
Functions. Over 60 SQL-like functions allow you to change data in a mapping. Operators. Use transformation operators to create transformation expressions to perform mathematical computations, combine data, or compare data.
Constants. Use built-in constants to reference values that remain constant, such as TRUE. Mapping parameters and variables. Create mapping parameters for use within a mapping or mapplet to reference values that remain constant throughout a session, such as a state sales tax rate. Create mapping variables in mapplets or mappings to write expressions referencing values that change from session to session. See “Mapping Parameters and Variables” in the Designer Guide for details.
Local and system variables. Use built-in variables to write expressions that reference value that vary, such as the system date. You can also create local variables in transformations.
Return values. You can also write expressions that include the return values from Lookup, Stored Procedure, and External Procedure transformations. For details on creating expressions and local variables in the Designer, see “Transformations” in the Designer Guide.
Transformation Expressions With the transformation language, you can create a transformation expression that takes the data from a port and changes it. For example, you might use the AVG function to calculate the average salary of all your employees, or the SUM function to calculate the total sales for a specific branch. Then, during a session, the Informatica Server writes results to the target tables based on the update strategies you define.
&KDSWHU 7K H7UDQVIRUPDWLRQ/DQJXDJH
You can create an expression as simple as a port, such as ORDERS, or a numeric literal, such as 10. You can also write complex expressions that include functions nested within functions, or combine different ports using the transformation language operators. For a detailed discussion of how transformation expressions are evaluated, see “Transformations” in the Designer Guide.
Internationalization and the Transformation Language Transformation language functions can handle character data in either ASCII or Unicode data movement mode. Use Unicode mode to handle multibyte character data. The return values of the following functions and transformations depend on the code page of the Informatica Server and the data movement mode:
INITCAP LOWER UPPER MIN (Date) MIN (Number) MAX (Date) MAX (Number) Any function that uses conditional statements to compare strings, such as IIF and DECODE
MIN and MAX also return values based on the sort order associated with the Informatica Server code page. When you validate an invalid expression in the expression editor, a dialog box displays the expression with an error indicator, “>>>>”. This indicator appears to the left of and points to the part of the expression containing the error. For example, if the expression a = b + c contains an error at c, the error message displays: a = b +
>>>> c
Transformation language functions that evaluate character data are character-oriented, not byte-oriented. For example, the LENGTH function returns the number of characters in a string, not the number of bytes. The LOWER function returns a string in lowercase according to the code page of the Informatica Server.
2Y H UY LH Z
Expression Syntax Although the transformation language is based on standard SQL, it is not exactly like SQL. For example, SQL supports the keywords AS and FROM for nonaggregate functions and ALL and DISTINCT for aggregate functions, but the transformation language does not. On the other hand, the transformation language supports an optional filter condition for aggregate functions, while SQL does not. You can create an expression that is as simple as a port (such as ORDERS) or a numeric literal (such as 10). You can also write complex expressions that include functions nested within functions, or combine different columns using the transformation language operators.
Expression Components Expressions can consist of any combination of the following components:
Ports (input, input/output, variable) String literals, numeric literals Constants Functions Local and system variables Mapping parameters and mapping variables Operators Return values
Ports and Return Values When you write an expression that includes a port or return value from an unconnected transformation, use the reference qualifiers in Table 1-1: Table 1-1. Reference Qualifiers in Transformation Language 5HIHUHQFH 'HVFULSWLRQ 4XDOLILH U
(;7
5HTXLUHGZKHQ\RXZULWHDQH[SUHVVLRQWKDWLQFOXGHVDUHWXUQYDOXHIURPDQ([WHUQDO3URFHGXUH WUDQVIRUPDWLRQ7KHJHQHUDOV\QWD[LV
:EXT.external_procedure_transformation(argument1, argument2, ...) /.3
5HTXLUHGZKHQ\RXFUHDWHDQH[SUHVVLRQWKDWLQFOXGHVWKHUHWXUQYDOXHIURPDQXQFRQQHFWHG /RRNXSWUDQVIRUPDWLRQ7KHJHQHUDOV\QWD[LV
:LKP.lookup_transformation(argument1, argument2, ...) 7KHDUJXPHQWVDUHWKHORFDOSRUWVXVHGLQWKHORRNXSFRQGLWLRQ7KHRUGHUPXVWPDWFKWKHRUGHURI WKHSRUWVLQWKHWUDQVIRUPDWLRQ7KHGDWDW\SHVIRUWKHORFDOSRUWVPXVWH[DFWO\PDWFKWKHGDWDW\SHRI WKH/RRNXSSRUWVXVHGLQWKHORRNXSFRQGLWLRQ
&KDSWHU 7K H7UDQVIRUPDWLRQ/DQJXDJH
Table 1-1. Reference Qualifiers in Transformation Language 5HIHUHQFH 'HVFULSWLRQ 4XDOLILHU
6'
2SWLRQDO 3RZHU0DUWH[SUHVVLRQVRQO\ 4XDOLILHVDVRXUFHWDEOHSRUWLQDQH[SUHVVLRQ7KH JHQHUDOV\QWD[LV
:SD.source_table.column_name 6(4
5HTXLUHGZKHQ\RXFUHDWHDQH[SUHVVLRQWKDWLQFOXGHVDSRUWLQD6HTXHQFH*HQHUDWRU WUDQVIRUPDWLRQ7KHJHQHUDOV\QWD[LV
:SEQ.sequence_generator_transformation.CURRVAL 63
5HTXLUHGZKHQ\RXZULWHDQH[SUHVVLRQWKDWLQFOXGHVWKHUHWXUQYDOXHIURPDQXQFRQQHFWHG6WRUHG 3URFHGXUHWUDQVIRUPDWLRQ7KHJHQHUDOV\QWD[LV
:SP.stored_procedure_transformation( argument1, argument2, [, PROC_RESULT]) 7KHDUJXPHQWVPXVWPDWFKWKHDUJXPHQWVLQWKHXQFRQQHFWHG6WRUHG3URFHGXUHWUDQVIRUPDWLRQ
7'
5HTXLUHGZKHQ\RXUHIHUHQFHDWDUJHWWDEOHLQD3RZHU0DUW/22.83IXQFWLRQ7KHJHQHUDO V\QWD[LV
LOOKUP(:TD.SALES.ITEM_NAME, :TD.SALES.ITEM_ID, 10, :TD.SALES.PRICE, 15.99)
String and Numeric Literals You can include numeric or string literals. Be sure to enclose string literals within single quotation marks. For example: ’Alice Davis’
String literals are case-sensitive and can contain any character except a single quotation mark. For example, the following string is not allowed: ’Joan’s car’
To return a string containing a single quote, use the CHR function: ’Joan’ || CHR(39) || ’s car’
Do not use single quotation marks with numeric literals. Just type the number you want to pass. For example: .05
or $$Sales_Tax
1RWH For details on constants, functions, local and system variables, mapping parameters and
variables, and operators see corresponding chapters in this guide.
( [S UH V VL R Q6 \ Q WD [
General Rules These general rules apply when writing all expressions:
You can pass a value from a port, literal string or number, variable, Lookup transformation, Stored Procedure transformation, External Procedure transformation, or the results of another expression.
Separate each argument in a function with a comma. Except for literals, the transformation language is not case-sensitive. Except for literals, the Designer and Informatica Server ignore spaces. The colon (:), comma (,), and period (.) have special meaning and should be used only to specify syntax.
The Informatica Server treats a dash (-) as a minus operator. If you pass a literal value to a function, enclose literal strings within single quotation marks. Do not use quotation marks for literal numbers. The Informatica Server treats any string value enclosed in single quotation marks as a character string.
When you pass a mapping parameter or variable to a function within an expression, do not use quotation marks to designate mapping parameters or variables.
Do not use quotation marks to designate ports. You can nest multiple functions within an expression (except aggregate functions, which allow only one nested aggregate function). The Informatica Server evaluates the expression starting with the innermost function. For more information on entering expressions, see “Transformations” in the Designer Guide.
&KDSWHU 7K H7UDQVIRUPDWLRQ/DQJXDJH
Adding Comments to Expressions The transformation language provides two comment specifiers to let you insert comments in expressions:
Two dashes, as in: -- These are comments
Two slashes, as in: // These are comments
The Informatica Server ignores all text on a line preceded by these two comment specifiers. For example, if you want to concatenate two strings, you can enter the following expression with comments in the middle of the expression: -- This expression concatenates first and last names for customers: FIRST_NAME -- First names from the CUST table || // Concat symbol LAST_NAME // Last names from the CUST table // Joe Smith Aug 18 1998
The Informatica Server ignores the comments and evaluates the expression as follows: FIRST_NAME || LAST_NAME
You cannot continue a comment to a new line: -- This expression concatenates first and last names for customers: FIRST_NAME -- First names from the CUST table || // Concat symbol LAST_NAME // Last names from the CUST table Joe Smith Aug 18 1998
In this case, the Designer does not validate the expression, since the last line is not a valid expression.
$ GG L QJ &R P P HQ W VWR ( [ S UH VV L RQ V
Reserved Words Some keywords in the transformation language, such as constants, operators, and system variables, are reserved for specific functions. These include:
:EXT :LKP :SD :SEQ :SP :TD AND DD_DELETE DD_INSERT DD_REJECT DD_UPDATE FALSE NOT NULL OR PROC_RESULT SESSSTARTTIME SPOUTPUT SYSDATE TRUE
1RWH You cannot use a reserved word to name a port or local variable. You can only use
reserved words within transformation expressions. Reserved words have predefined meanings in expressions.
&KDSWHU 7K H7UDQVIRUPDWLRQ/DQJXDJH
&KDSWHU
Constants This chapter describes how to use the built-in constants of the transformation language:
DD_DELETE, 10 DD_INSERT, 11 DD_REJECT, 12 DD_UPDATE, 13 FALSE, 14 NULL, 15 TRUE, 16
DD_DELETE Flags records for deletion in an update strategy expression. DD_DELETE is equivalent to the integer literal 2. 1RWH The DD_DELETE constant is designed for use in the Update Strategy transformation
only. Informatica recommends using DD_DELETE instead of the integer literal 2. It is easier to troubleshoot complex numeric expressions if you use DD_DELETE. When you run a session, select the data-driven update strategy to delete records from a target based on this flag.
Example The following expression marks items with an ID number of 1001 for deletion, and all other items for insertion: IIF( ITEM_ID = 1001, DD_DELETE, DD_INSERT )
This update strategy expression uses numeric literals to produce the same result: IIF( ITEM_ID = 1001, 2, 0 )
1RWH The expression using constants is easier to read than the expression using numeric
literals.
&KDSWHU & RQVWD QWV
DD_INSERT Flags records for insertion in an update strategy expression. DD_INSERT is equivalent to the integer literal 0. 1RWH The DD_INSERT constant is designed for use in the Update Strategy transformation
only. Informatica recommends using DD_INSERT instead of the integer literal 0. It is easier to troubleshoot complex numeric expressions if you use DD_INSERT. When you run a session, select the data-driven update strategy to write records to a target based on this flag.
Examples The following examples modify a mapping that calculates monthly sales per salesperson, so you can examine the sales of just one salesperson. This update strategy expression flags this employee’s sales for insertion (using DD_INSERT), and rejects everything else (using DD_REJECT): IIF( EMPLOYEE.NAME = ’Alex’, DD_INSERT, DD_REJECT )
This update strategy expression uses numeric literals to produce the same result: IIF( EMPLOYEE.NAME = ’Alex’, 0, 3 )
Notice that the expression using constants is easier to read than the expression using numeric literals. The following update strategy expression uses SESSSTARTTIME to find only those orders that have shipped in the last two days and flag them for insertion. Using DATE_DIFF, the expression subtracts DATE_SHIPPED from the system date, returning the difference between the two dates. Because DATE_DIFF returns a Double value, the expression uses TRUNC to truncate the difference. It then compares the result to the integer literal 2. If the result is greater than 2, the expression flags the records for rejection. If the result is 2 or less, it flags them for insertion: IIF( TRUNC( DATE_DIFF( SESSSTARTTIME, ORDERS.DATE_SHIPPED, ’DD’ ), 0 ) > 2,DD_REJECT, DD_INSERT )
' ' B ,1 6 ( 57
DD_REJECT Flags records for rejection in an update strategy expression. DD_REJECT is equivalent to the integer literal 3. 1RWH The DD_REJECT constant is designed for use in the Update Strategy transformation
only. Informatica recommends using DD_REJECT instead of the integer literal 3. It is easier to troubleshoot complex numeric expressions if you use DD_REJECT. When you run a session, select the data-driven update strategy to reject records from a target based on this flag. DD_REJECT is generally used to filter or validate data. If you flag a record as reject, the Informatica Server skips the record and writes it to the session reject file. For more information, see “Reject Loading” in the Session and Server Guide.
Examples The following examples modify a mapping that calculates the sales for the current month, so it includes only positive values. This update strategy expression flags records less than zero for reject and all others for insert: IIF( ORDERS.SALES > 0, DD_INSERT, DD_REJECT )
This expression uses numeric literals to produce the same result: IIF( ORDERS.SALES > 0, 0, 3 )
Notice that the expression using constants is easier to read than the expression using numeric literals. The following data-driven example uses DD_REJECT and IS_SPACES to avoid writing spaces to a character column in a target table. This expression flags records that consist entirely of spaces for reject and flags all others for insert: IIF( IS_SPACES( CUST_NAMES ), DD_REJECT, DD_INSERT )
&KDSWHU & RQVWD QWV
DD_UPDATE Flags records for update in an update strategy expression. DD_UPDATE is equivalent to the integer literal 1. 1RWH The DD_UPDATE constant is designed for use in the Update Strategy transformation
only. Informatica recommends using DD_UPDATE instead of the integer literal 1. It is easier to troubleshoot complex numeric expressions if you use DD_UPDATE. When you run a session, select the data-driven update strategy to write records to a target based on this flag.
Examples The following examples modify a mapping that calculates sales for the current month, so it only loads the sales for one particular employee. This expression flags records for Alex as updates and flags all others for rejection: IIF( EMPLOYEE.NAME = ’Alex’, DD_UPDATE, DD_REJECT )
This expression uses numeric literals to produce the same result, flagging Alex’s sales for update (1) and flagging all other sales records for rejection (3): IIF( EMPLOYEE.NAME = ’Alex’, 1, 3 )
Notice that the expression using constants is easier to read than the expression using numeric literals. The following update strategy expression uses SYSDATE to find only those orders that have shipped in the last two days and flag them for insertion. Using DATE_DIFF, the expression subtracts DATE_SHIPPED from the system date, returning the difference between the two dates. Because DATE_DIFF returns a Double value, the expression uses TRUNC to truncate the difference. It then compares the result to the integer literal 2. If the result is greater than 2, the expression flags the records for rejection. If the result is 2 or less, it flags the records for update; otherwise, it flags them for rejection: IIF( TRUNC( DATE_DIFF( SYSDATE, ORDERS.DATE_SHIPPED, ’DD’ ), 0 ) > 2, DD_REJECT, DD_UPDATE )
' ' B8 3 ' $7 (
FALSE Clarifies a conditional expression. FALSE is equivalent to the integer 0.
Example The following example uses FALSE in a DECODE expression to return values based on the results of a comparison. (This is useful if you want to perform multiple searches based on a single search value): DECODE( FALSE, Var1 = 22,’Variable 1 was 22!’, Var2 = 49,’Variable 2 was 49!’, Var1 < 23, ’Variable 1 was less than 23.’, Var2 > 30, ’Variable 2 was more than 30.’, ’Variables were out of desired ranges.’)
&KDSWHU & RQVWD QWV
NULL Indicates that a value is either unknown or undefined. NULL is not equivalent to a blank or empty string (for character columns) or zero (for numerical columns). Although you can write expressions that return nulls, keep in mind that any column that has the NOT NULL or PRIMARY KEY constraint will not accept nulls. Therefore, if the Informatica Server tries to write a null value to a column with one of these constraints, the database will reject the row and the Informatica Server will write it to the reject file. Be sure to consider nulls when you create transformations. The way functions handle nulls varies from function to function. If you pass a null value to a function, it might return zero or NULL, or it might ignore null values. For details on how each function handles null values, see “Functions” on page 47.
Null Values in Aggregate Functions By default, the Informatica Server treats null values as nulls in aggregate functions. If you pass an entire port or group of null values, the function returns NULL. However, when you configure the Informatica Server, you can choose how you want it to handle null values in aggregate functions. You can have the Informatica Server treat null values as zero in aggregate functions or as NULLs. For more information on configuring the Informatica Server, see “Installing and Configuring the Informatica NT Server” or “Installing and Configuring the Informatica UNIX Server” in the Installation and Configuration Guide.
Null Values in Filter Conditions If a filter condition evaluates to NULL, the function does not select the record. If the filter condition evaluates to NULL for all records in the selected port, the aggregate function returns NULL (except COUNT, which returns zero). You use filter conditions with aggregate functions, as well as CUME, MOVINGAVG, and MOVINGSUM.
Nulls with Operators Any expression that uses operators (except the string operator ||) and contains a null value always evaluates to NULL. For example, the following expression evaluates to NULL: 8 * 10 - NULL
To test for nulls, use the ISNULL function.
1 8/ /
TRUE Returns a value based on the result of a comparison. TRUE is equivalent to the integer 1.
Example The following example uses TRUE in a DECODE expression to return values based on the results of a comparison. (This is useful if you want to perform multiple searches based on a single search value): DECODE( TRUE, Var1 = 22,’Variable 1 was 22!’, Var2 = 49,’Variable 2 was 49!’, Var1 < 23, ’Variable 1 was less than 23.’, Var2 > 30, ’Variable 2 was more than 30.’, ’Variables were out of desired ranges.’)
&KDSWHU & RQVWD QWV
&KDSWHU
Operators This chapter describes how to use operators to perform arithmetic or comparisons within functions. It includes the following topics:
Operator Precedence, 18 Arithmetic Operators, 19 String Operators, 21 Comparison Operators, 22 Logical Operators, 23
Operator Precedence The transformation language supports the use of multiple operators and the use of operators within nested expressions. If you write an expression that includes multiple operators, the Informatica Server evaluates the expression in the following order: 1.
Arithmetic operators
2.
String operators
3.
Comparison operators
4.
Logical operators
Table 3-1 lists the precedence for all transformation language operators. The Informatica Server evaluates operators in the order they appear in the following table. It evaluates operators in an expression with equal precedence to all operators from left to right. Table 3-1. Operators in the Transformation Language 2SHUDWRU
0HDQLQJ
3DUHQWKHVHV
8QDU\SOXVDQGPLQXV
0XOWLSOLFDWLRQGLYLVLRQPRGXOXV
$GGLWLRQVXEWUDFWLRQ
__
&RQFDWHQDWH
!!
!
A
/HVVWKDQOHVVWKDQRUHTXDOWRJUHDWHUWKDQJUHDWHUWKDQRUHTXDOWR
(TXDOWRQRWHTXDOWRQRWHTXDOWRQRWHTXDOWR
127
7KHORJLFDO127RSHUDWRU
$1'
7KHORJLFDO$1'RSHUDWRUXVHGZKHQVSHFLI\LQJFRQGLWLRQV
25
7KHORJLFDO25RSHUDWRUXVHGZKHQVSHFLI\LQJFRQGLWLRQV
The transformation language also supports the use of operators within nested expressions. When expressions contain parentheses, the Informatica Server evaluates operations inside parentheses before operations outside parentheses. Operations in the innermost parentheses are evaluated first. For example, depending on how you nest the operations, the equation 8 + 5 - 2 * 8 returns different values:
(TXDWLRQ
5HWXUQ9DOXH
&KDSWHU 2 SHUDWR UV
Arithmetic Operators Use arithmetic operators to perform mathematical calculations on numeric data. Table 3-2 lists the arithmetic operators in order of precedence in the transformation language: Table 3-2. Arithmetic Operators in the Transformation Language 2SHUDWRU
0HDQLQJ
8QDU\SOXVDQGPLQXV8QDU\SOXVLQGLFDWHVDSRVLWLYHYDOXH8QDU\PLQXVLQGLFDWHVD QHJDWLYHYDOXH
0XOWLSOLFDWLRQGLYLVLRQPRGXOXV$PRGXOXVLVWKHUHPDLQGHUDIWHUGLYLGLQJWZRLQWHJHUV )RUH[DPSOH
EHFDXVHGLYLGHGE\HTXDOVZLWKDUHPDLQGHURI
$GGLWLRQVXEWUDFWLRQ 1RWH 7KHDGGLWLRQRSHUDWRU GRHVQRWFRQFDWHQDWHVWULQJV,I\RXXVHDULWKPHWLF RSHUDWRUVRQWH[WRUGDWHYDOXHVWKH,QIRUPDWLFD6HUYHUFRQYHUWVWKHWH[WWRDQXPEHU EHIRUHFDOFXODWLQJDUHWXUQYDOXH7RFRQFDWHQDWHVWULQJVXVHWKHVWULQJRSHUDWRU__7R SHUIRUPDULWKPHWLFRQGDWHYDOXHVXVHWKHGDWHIXQFWLRQV
If you perform arithmetic on a null value, the function returns NULL. You can use these operators with numeric data or text data. However, the operator may produce different results when you use it to manipulate numeric data versus character data. For example, if you use an arithmetic operator with a string, the parser converts the string to a number. 1RWH The transformation language provides built-in date functions that let you perform
arithmetic on date/time values. For further information on built-in date functions, see “Understanding Date Arithmetic” on page 45.
Using Arithmetic Operators to Convert Data You can convert data to numeric values within an expression. If the string consists of numbers, unary plus or minus, or a decimal point, the Informatica Server converts it and evaluates it without error. Table 3-3 shows the results when you pass strings to an expression that adds numeric values: Table 3-3. Converting Data with Arithmetic Operators ([SUHVVLRQ
&RQYHUWV7R
5HVXOWV
$EF
DEF
DEF
DEF
$ U LW KP H WL F2S H UD W RU V
Table 3-3. Converting Data with Arithmetic Operators
([SUHVVLRQ
&RQYHUWV7R
5HVXOWV
H
18//
18//
18//
&KDSWHU 2 SHUDWR UV
String Operators Use the || string operator to concatenate two strings. The || operator converts operands of any datatype (except Binary) to String datatypes before concatenation: ,QSXW9DOXH
5HWXUQ9DOXH
DOSKD __ EHWLFDO
DOSKDEHWLFDO
DOSKD __
DOSKD
DOSKD __18//
DOSKD
The || operator includes leading and trailing spaces. You can use the LTRIM and RTRIM functions to trim leading and trailing spaces before concatenating two strings.
Nulls The || operator ignores null values. However, if both values are NULL, the || operator returns NULL.
Example The following example shows an expression that concatenates employee first names and employee last names that are normally stored in two separate columns. This expression removes the spaces from the end of all first names and the beginning of all last names, concatenates a space to the end of each first name, then concatenates the last name: LTRIM( RTRIM( EMP_FIRST ) || ’ ’ || LTRIM( EMP_LAST )) EMP_FIRST
EMP_LAST
RETURN VALUE
‘
Alfred’
‘
Alfred Rice
‘
Bernice’
Rice ‘
‘ Kersins’
Bernice Kersins
NULL
‘
Proud
‘
NULL
Curt
NULL
NULL
Curt’
NULL
Proud’
1RWH You can also use the CONCAT function to concatenate two string values. The operator,
however, produces the same results in less time.
6 WU LQ J2S H UD W RU V
Comparison Operators Use comparison operators to compare character or numeric strings, manipulate data, and return a TRUE (1) or FALSE (0) value. Table 3-4 lists the comparison operators in the transformation language: Table 3-4. Comparison Operators in the Transformation Language 2SHUDWRU
0 HDQLQJ
(TXDOWR
!
*UHDWHUWKDQ
/HVVWKDQ
!
*UHDWHUWKDQRUHTXDOWR
/HVVWKDQRUHTXDOWR
!
1RWHTXDOWR
1RWHTXDOWR
A
1RWHTXDOWR
You can use the greater than (>) and less than ( 0, --then test to see if sales is between 1 and 49: IIF( SALES < 50, --then return SALARY1 SALARY1, --else test to see if sales is between 50 and 99: IIF( SALES < 100, --then return SALARY2,
, ,)
--else test to see if sales is between 100 and 199: IIF( SALES < 200, --then return SALARY3, --else for sales over 199, return BONUS) ) ), --else for sales less than or equal to zero, return 0)
You can use IIF in update strategies. For example: IIF( ISNULL( ITEM_NAME ), DD_REJECT, DD_INSERT)
Alternative to IIF You can use DECODE instead of IIF in many cases. DECODE may improve readability. The following shows how you can use DECODE instead of IIF using the first example from the previous section: DECODE( TRUE, SALES > 0 and SALES < 50, SALARY1, SALES > 49 AND SALES < 100, SALARY2, SALES > 99 AND SALES < 200, SALARY3, SALES > 199, BONUS)
You can often use a Filter transformation instead of IIF to maximize session performance. See “Filter Transformation” in the Designer Guide for more information.
&KDSWHU )X QFWLRQV
INITCAP Capitalizes the first letter in each word of a string and converts all other letters to lowercase. Words are delimited by white space (a blank space, formfeed, newline, carriage return, tab, or vertical tab) and characters that are not alphanumeric. For example, if you pass the string ‘…THOMAS’, the function returns Thomas.
Syntax INITCAP( string )
$UJXPHQW
0HDQLQJ
VWULQJ
5HTXLUHG$Q\GDWDW\SH H[FHSW%LQDU\ ILOWHUBFRQGLWLRQ@
5HWXUQVWKHDYHUDJHRIDOOYDOXHVLQDJURXS
&2817
&2817 YDOXH>ILOWHUBFRQGLWLRQ@
5HWXUQVWKHQXPEHURIUHFRUGVZLWKQRQQXOOYDOXH
RU
LQDJURXS
&2817 >ILOWHUBFRQGLWLRQ@
),567
),567 YDOXH>ILOWHUBFRQGLWLRQ@
5HWXUQVWKHILUVWUHFRUGLQDJURXS
/$67
/$67 YDOXH>ILOWHUBFRQGLWLRQ@
5HWXUQVWKHODVWUHFRUGLQDJURXS
0$;
0$; YDOXH>ILOWHUBFRQGLWLRQ@
5HWXUQVWKHPD[LPXPYDOXHRUODWHVWGDWHIRXQGLQ DJURXS
0(',$1
0(',$1 QXPHULFBYDOXH>ILOWHUBFRQGLWLRQ@
5HWXUQVWKHPHGLDQRIDOOYDOXHVLQDVHOHFWHGSRUW
0,1
0,1 YDOXH>ILOWHUBFRQGLWLRQ@
5HWXUQVWKHPLQLPXPYDOXHRUHDUOLHVWGDWHIRXQG LQDJURXS
3(5&(17,/(
3(5&(17,/( QXPHULFBYDOXHSHUFHQWLOH>ILOWHUBFRQGLWLRQ@
&DOFXODWHVWKHYDOXHWKDWIDOOVDWDJLYHQSHUFHQWLOH LQDJURXSRIQXPEHUV
67''(9
67''(9 QXPHULFBYDOXH>ILOWHUBFRQGLWLRQ@
5HWXUQVWKHVWDQGDUGGHYLDWLRQIRUDJURXS
680
680 QXPHULFBYDOXH>ILOWHUBFRQGLWLRQ@
5HWXUQVWKHVXPRIDOOUHFRUGVLQDJURXS
9$5,$1&(
9$5,$1&( QXPHULFBYDOXH>ILOWHUBFRQGLWLRQ@
5HWXUQVWKHYDULDQFHRIDOOUHFRUGVLQDJURXS
$SSHQGL[$ )XQFWLRQ 4 XLFN5HIHUHQFH
Character Functions )XQFWLRQ
6\QWD[
'HVF ULSWLRQ
$6&,,
$6&,, VWULQJ
,Q$6&,,PRGHUHWXUQVWKHQXPHULF$6&,,YDOXHRIWKHILUVW FKDUDFWHURIWKHVWULQJSDVVHGWRWKHIXQFWLRQ ,Q8QLFRGHPRGHUHWXUQVWKHQXPHULF8QLFRGHYDOXHRIWKHILUVW FKDUDFWHURIWKHVWULQJSDVVHGWRWKHIXQFWLRQ7KLVIXQFWLRQLV LGHQWLFDOLQEHKDYLRUWRWKH&+5&2'(IXQFWLRQ ,I\RXXVHWKH$6&,,IXQFWLRQLQH[LVWLQJH[SUHVVLRQVLWZLOOVWLOO ZRUNFRUUHFWO\KRZHYHU,QIRUPDWLFDUHFRPPHQGVXVLQJWKH &+5&2'(IXQFWLRQLQVWHDGRIWKH$6&,,IXQFWLRQWRFUHDWHQHZ H[SUHVVLRQV
&+5
&+5 QXPHULFBYDOXH
5HWXUQVWKH$6&,,RU8QLFRGHFKDUDFWHUFRUUHVSRQGLQJWRWKH VSHFLILHGQXPHULFYDOXH
&+5&2'(
&+5&2'( VWULQJ
,Q$6&,,PRGHUHWXUQVWKHQXPHULF$6&,,YDOXHRIWKHILUVW FKDUDFWHURIWKHVWULQJSDVVHGWRWKHIXQFWLRQ ,Q8QLFRGHPRGHUHWXUQVWKHQXPHULF8QLFRGHYDOXHRIWKHILUVW FKDUDFWHURIWKHVWULQJSDVVHGWRWKHIXQFWLRQ7KLVIXQFWLRQLV LGHQWLFDOLQEHKDYLRUWRWKH$6&,,IXQFWLRQ
&21&$7
&21&$7 ILUVWBVWULQJVHFRQGBVWULQJ
&RQFDWHQDWHVWZRVWULQJV
,1,7&$3
,1,7&$3 VWULQJ
&DSLWDOL]HVWKHILUVWOHWWHULQHDFKZRUGRIDVWULQJDQGFRQYHUWV DOORWKHUOHWWHUVWRORZHUFDVH
,1675
,1675 VWULQJVHDUFKBYDOXH>VWDUW>RFFXUUHQFH@@
5HWXUQVWKHSRVLWLRQRIDFKDUDFWHUVHWLQDVWULQJFRXQWLQJ IURPOHIWWRULJKW
/(1*7+
/(1*7+ VWULQJ
5HWXUQVWKHQXPEHURIFKDUDFWHUVLQDVWULQJLQFOXGLQJWUDLOLQJ EODQNV
/2:(5
/2:(5 VWULQJ
&RQYHUWVXSSHUFDVHVWULQJFKDUDFWHUVWRORZHUFDVH
/3$'
/3$' ILUVWBVWULQJOHQJWK>VHFRQGBVWULQJ@
$GGVDVHWRIEODQNVRUFKDUDFWHUVWRWKHEHJLQQLQJRIDVWULQJ WRVHWDVWULQJWRDVSHFLILHGOHQJWK
/75,0
/75,0 VWULQJ>WULPBVHW@
5HPRYHVEODQNVRUFKDUDFWHUVIURPWKHEHJLQQLQJRIDVWULQJ
53$'
53$' ILUVWBVWULQJOHQJWK>VHFRQGBVWULQJ@
&RQYHUWVDVWULQJWRDVSHFLILHGOHQJWKE\DGGLQJEODQNVRU FKDUDFWHUVWRWKHHQGRIWKHVWULQJ
575,0
575,0 VWULQJ>WULPBVHW@
5HPRYHVEODQNVRUFKDUDFWHUVIURPWKHHQGRIDVWULQJ
68%675
68%675 VWULQJVWDUW>OHQJWK@
5HWXUQVDSRUWLRQRIDVWULQJ
833(5
833(5 VWULQJ
&RQYHUWVORZHUFDVHVWULQJFKDUDFWHUVWRXSSHUFDVH
&K D UD F WH U )X Q FW LR Q V
Conversion Functions )XQFWLRQ
6\QWD[
'HVFULSWLRQ
72B&+$5
72B&+$5 QXPHULFBYDOXH
&RQYHUWVQXPHULFYDOXHVDQGGDWHVWRWH[WVWULQJV
72B&+$5 GDWH>IRUPDW@
72B'$7(
72B'$7( VWULQJ>IRUPDW@
&RQYHUWVDFKDUDFWHUVWULQJWRDGDWHGDWDW\SHLQWKHVDPHIRUPDW DVWKHFKDUDFWHUVWULQJ
72B'(&,0$/
72B'(&,0$/ YDOXH>VFDOH@
&RQYHUWVDQ\YDOXH H[FHSWELQDU\ WRDGHFLPDO
72B)/2$7
72B)/2$7 YDOXH
&RQYHUWVDQ\YDOXH H[FHSWELQDU\ WRDGRXEOHSUHFLVLRQIORDWLQJ SRLQWQXPEHU WKH'RXEOHGDWDW\SH
72B,17(*(5
72B,17(*(5 YDOXH
&RQYHUWVDQ\YDOXH H[FHSWELQDU\ WRDQLQWHJHUE\URXQGLQJWKH GHFLPDOSRUWLRQRIDYDOXH
72B180%(5
72B180%(5 VWULQJ
$SSHQGL[$ )XQFWLRQ 4 XLFN5HIHUHQFH
&RQYHUWVDVWULQJWRDGRXEOHYDOXH
Date Functions )XQFWLRQ
6\QWD[
'HVFULSWLRQ
$''B72B'$7(
$''B72B'$7( GDWHIRUPDWDPRXQW
$GGVDVSHFLILHGDPRXQWWRRQHSDUWRIDGDWHWLPHYDOXHDQG UHWXUQVDGDWHLQWKHVDPHIRUPDWDVWKHVSHFLILHGGDWH 1RWH ,I\RXGRQRWVSHFLI\WKH\HDUDVIRUPDW@
5RXQGVRQHSDUWRIDGDWH
6(7B'$7(B3$57
6(7B'$7(B3$57 GDWHIRUPDWYDOXH
6HWVRQHSDUWRIDGDWHWLPHYDOXHWRDVSHFLILHGYDOXH
7581&
7581& GDWH>IRUPDW@
7UXQFDWHVGDWHVWRDVSHFLILF\HDUPRQWKGD\KRXURUPLQXWH
' DW H )X Q FW LR Q V
Numeric Functions )XQFWLRQ
6\QWD[
'HVFULSWLRQ
$%6
$%6 QXPHULFBYDOXH
5HWXUQVWKHDEVROXWHYDOXHRIDQXPHULFYDOXH
&(,/
&(,/ QXPHULFBYDOXH
5HWXUQVWKHVPDOOHVWLQWHJHUJUHDWHUWKDQRUHTXDO WRWKHVSHFLILHGQXPHULFYDOXH
&80(
&80( QXPHULFBYDOXH>ILOWHUBFRQGLWLRQ@
5HWXUQVDUXQQLQJWRWDORIDOOQXPHULFYDOXHV
(;3
(;3 H[SRQHQW
5HWXUQVHUDLVHGWRWKHVSHFLILHGSRZHU H[SRQHQW ZKHUHH
)/225
)/225 QXPHULFBYDOXH
5HWXUQVWKHODUJHVWLQWHJHUOHVVWKDQRUHTXDOWRWKH VSHFLILHGQXPHULFYDOXH
/1
/1 QXPHULFBYDOXH
5HWXUQVWKHQDWXUDOORJDULWKPRIDQXPHULFYDOXH
/2*
/2* EDVHH[SRQHQW
5HWXUQVWKHORJDULWKPRIDQXPHULFYDOXH
02'
02' QXPHULFBYDOXHGLYLVRU
5HWXUQVWKHUHPDLQGHURIDGLYLVLRQFDOFXODWLRQ
029,1*$9*
029,1*$9* QXPHULFBYDOXHUHFRUGVHW>ILOWHUBFRQGLWLRQ@
5HWXUQVWKHDYHUDJH UHFRUGE\UHFRUG RID VSHFLILHGVHWRIUHFRUGV
029,1*680
029,1*680 QXPHULFBYDOXHUHFRUGVHW>ILOWHUBFRQGLWLRQ@
5HWXUQVWKHVXP UHFRUGE\UHFRUG RIDVSHFLILHG VHWRIUHFRUGV
32:(5
32:(5 EDVHH[SRQHQW
5HWXUQVDYDOXHUDLVHGWRWKHVSHFLILHGH[SRQHQW
5281'
5281' QXPHULFBYDOXH>SUHFLVLRQ@
5RXQGVQXPEHUVWRDVSHFLILHGGLJLW
6,*1
6,*1 QXPHULFBYDOXH
1RWHVZKHWKHUDQXPHULFYDOXHLVSRVLWLYH QHJDWLYHRU
6457
6457 QXPHULFBYDOXH
5HWXUQVWKHVTXDUHURRWRIDSRVLWLYHQXPHULFYDOXH
7581&
7581& QXPHULFBYDOXH>SUHFLVLRQ@
7UXQFDWHVQXPEHUVWRDVSHFLILFGLJLW
$SSHQGL[$ )XQFWLRQ 4 XLFN5HIHUHQFH
Scientific Functions )XQFWLRQ
6\QWD[
'HVFULSWLRQ
&26
&26 QXPHULFBYDOXH
5HWXUQVWKHFRVLQHRIDQXPHULFYDOXH H[SUHVVHGLQUDGLDQV
&26+
&26+ QXPHULFBYDOXH
5HWXUQVWKHK\SHUEROLFFRVLQHRIDQXPHULFYDOXH H[SUHVVHGLQUDGLDQV
6,1
6,1 QXPHULFBYDOXH
5HWXUQVWKHVLQRIDQXPHULFYDOXH H[SUHVVHGLQUDGLDQV
6,1+
6,1+ QXPHULFBYDOXH
5HWXUQVWKHK\SHUEROLFVLQRIDQXPHULFYDOXH H[SUHVVHGLQUDGLDQV
7$1
7$1 QXPHULFBYDOXH
5HWXUQVWKHWDQJHQWRIDQXPHULFYDOXH H[SUHVVHGLQUDGLDQV
7$1+
7$1+ QXPHULFBYDOXH
5HWXUQVWKHK\SHUEROLFWDQJHQWRIDQXPHULFYDOXH H[SUHVVHGLQ UDGLDQV
6F LH Q WL IL F )X Q FW LR Q V
Special Functions )XQFWLRQ
6\QWD[
'HVFULSWLRQ
$%257
$%257 VWULQJ
6WRSVWKHVHVVLRQDQGLVVXHVDVSHFLILHGHUURU PHVVDJH
'(&2'(
'(&2'( YDOXHILUVWBVHDUFKILUVWBUHVXOW>VHFRQGBVHDUFK
6HDUFKHVDSRUWIRUWKHVSHFLILHGYDOXH
VHFRQGBUHVXOW@«>GHIDXOW@
(5525
(5525 VWULQJ
&DXVHVWKH,QIRUPDWLFD6HUYHUWRVNLSDUHFRUGDQG LVVXHWKHVSHFLILHGHUURUPHVVDJH
,,)
,,) FRQGLWLRQYDOXH>YDOXH@
5HWXUQVRQHRIWZRYDOXHV\RXVSHFLI\EDVHGRQWKH UHVXOWVRIDFRQGLWLRQ
/22.83
/22.83 UHVXOWVHDUFKYDOXH>VHDUFKYDOXH@«
6HDUFKHVIRUDYDOXHLQDORRNXSVRXUFHFROXPQ ,QIRUPDWLFDUHFRPPHQGVXVLQJWKH/RRNXS WUDQVIRUPDWLRQ
$SSHQGL[$ )XQFWLRQ 4 XLFN5HIHUHQFH
Test Functions )XQFWLRQ
6\QWD[
'HVFULSWLRQ
,618//
,618// YDOXH
5HWXUQVZKHWKHUDYDOXHLV18//
,6B'$7(
,6B'$7( YDOXH
5HWXUQVZKHWKHUDYDOXHLVDYDOLGGDWH
,6B180%(5
,6B180%(5 YDOXH
5HWXUQVZKHWKHUDVWULQJLVDYDOLGQXPEHU
,6B63$&(6
,6B63$&(6 YDOXH
5HWXUQVZKHWKHUDYDOXHFRQVLVWVHQWLUHO\RIVSDFHV
7HV W )X Q FW LR Q V
Variable Functions )XQFWLRQ
6\QWD[
'HVFULSWLRQ
6(7&28179$5,$%/(
6(7&28179$5,$%/( 9DULDEOH
&RXQWVWKHURZVHYDOXDWHGE\WKHIXQFWLRQDQG LQFUHPHQWVWKHFXUUHQWYDOXHRIDPDSSLQJEDVHGRQ WKHFRXQW
6(70$;9$5,$%/(
6(70$;9$5,$%/( 9DULDEOHYDOXH
6HWVWKHFXUUHQWYDOXHRIDPDSSLQJYDULDEOHWRWKH KLJKHURIWZRYDOXHV WKHFXUUHQWYDOXHRIWKH YDULDEOHRUWKHYDOXHVSHFLILHG5HWXUQVWKHQHZ FXUUHQWYDOXH
6(70,19$5,$%/(
6(70,19$5,$%/( 9DULDEOHYDOXH
6HWVWKHFXUUHQWYDOXHRIDPDSSLQJYDULDEOHWRWKH ORZHURIWZRYDOXHV WKHFXUUHQWYDOXHRIWKHYDULDEOH RUWKHYDOXHVSHFLILHG5HWXUQVWKHQHZFXUUHQW YDOXH
6(79$5,$%/(
6(79$5,$%/( 9DULDEOHYDOXH
6HWVWKHFXUUHQWYDOXHRIDPDSSLQJYDULDEOHWRD YDOXH\RXVSHFLI\5HWXUQVWKHVSHFLILHGYDOXH
$SSHQGL[$ )XQFWLRQ 4 XLFN5HIHUHQFH
,QGH[
A ABORT function description 55 ABS function description 57 absolute values obtaining 57 ADD_TO_DATE function description 58 aggregate functions AVG 62 COUNT 71 description 48 FIRST 84 LAST 104 MAX (dates) 118 MAX (numbers) 119 MEDIAN 121 MIN (dates) 123 MIN (numbers) 125 null values 15, 50 PERCENTILE 133 STDDEV 161 SUM 166 summary 194 VARIANCE 190 AND reserved word 8
arithmetic date/time values 45 arithmetic operators description 19 using to convert data 19 ASCII See also Installation and Configuration Guide See also Session and Server Guide CHR function 65 converting ASCII values 65 converting characters to ASCII values 61 converting to Unicode values 66 ASCII function description 61 average aggregate functions for determining 62 returning 129 AVG function description 62
C calendars date types supported 32 capitalization strings 93, 113, 189 case converting to uppercase 189
CEIL function description 64 character functions ASCII 61 CHR 65 CHRCODE 66 CONCAT function 67 description 50 INITCAP 93 INSTR 94 LENGTH 108 LOWER 113 LPAD 114 LTRIM 116 RPAD 142 RTRIM 144 SUBSTR 163 summary 195 UPPER 189 character strings converting from dates 170 converting to dates 176 characters adding to strings 114, 142 ASCII characters 61, 65 capitalization 93, 113, 189 counting 163 removing from strings 116, 144 returning number 108 Unicode characters 61, 65, 66 CHR function description 65 inserting single quotes 5, 65 CHRCODE function description 66 comments adding to expressions 7 comparison operators description 22 CONCAT function description 67 inserting single quotes using 68 concatenating strings 21, 67 conditions returning results 89 constants DD_INSERT 11 DD_REJECT 12 DD_UPDATE 13 definition 2
,QGH[
FALSE 14 NULL 15 TRUE 16 conversion functions description 51 summary 196 TO_CHAR (dates) 170 TO_CHAR (numbers) 174 TO_DATE 176 TO_DECIMAL 180 TO_FLOAT 181 TO_INTEGER 182 TO_NUMBER 183 converting date strings 33 COS function description 69 COSH function description 70 cosine calculating 69 calculating hyperbolic cosine 70 COUNT function description 71 CUME function description 74
D datatypes Date/Time 32 date functions ADD_TO_DATE 58 DATE_COMPARE 76 DATE_DIFF 77 GET_DATE_PART 87 LAST_DAY 106 MAX (dates) 118 MIN (dates) 123 ROUND 137 SET_DATE_PART 148 summary 197 TRUNC (Dates) 184 date/time values adding 58 DATE_COMPARE function description 76 DATE_DIFF function description 77 dates
See also date functions converting to character strings 170 default format 35 flat files 35 format strings 37 functions 51 Julian 32 Modified Julian 32 overview 32 performing arithmetic 45 relational databases 35 rounding 137 truncating 184 year 2000 33 DD_DELETE constant reserved word 8 update strategy example 10 DD_INSERT constant description 11 reserved word 8 update strategy example 11 DD_REJECT constant reserved word 8 update strategy example 12 DD_UPDATE constant description 13 reserved word 8 update strategy example 13 decimal values converting 180 DECODE function description 80 internationalization 3 default date format defined 35 division calculation returning remainder 127 documentation conventions xv description xii online xiv double precision values converting strings 183 floating point numbers 181
E empty string testing for 108 ERROR function
description 82 EXP function description 83 exponent values calculating 83 returning 136 expressions See also Designer Guide adding comments 7 conditional 14 in transformations 2 overview 2 syntax 4 using operators 18 using SYSDATE 28 :EXT reference qualifier description 4 reserved word 8
F FALSE constant description 14 reserved word 8 filter conditions aggregate functions 50 null values 15 Filter transformation See also Designer Guide using ISNULL function 97 FIRST function description 84 flat files dates 35 floating point number See double precision values FLOOR function description 86 format from character string to date 176 from date to character string 170 format strings dates 37 definition 32 IS_DATE function 41 Julian day 38, 42 matching 42 Modified Julian day 38, 42 TO_CHAR function 38 TO_DATE function 41
,Q G H[
functions aggregate 48, 194 character 50, 195 conversion 51, 196 date 51, 197 definition 2 internationalization 3 numerical 52, 198 scientific 52, 199 special 53, 200 test 53, 201 variable 53
description 98 format strings 41 IS_NUMBER function description 101 IS_SPACES function description 103 ISNULL function description 97
J
GET_DATE_PART function description 87 Gregorian calendar in date functions 32
J format string using with IS_DATE 43 using with TO_CHAR 40 using with TO_DATE 43 Julian dates in date functions 32 Julian day format string 38, 42
H
L
hyperbolic cosine function 70 hyperbolic sine function 159 hyperbolic tangent function 169
LAST function description 104 LAST_DAY function description 106 LENGTH function description 108 empty string test 108 literals single quotes in 65, 68 single quotes requirement 5 :LKP reference qualifier description 4 reserved word 8 LN function description 109 local variables definition 2 description 29 LOG function description 110 logarithm returning 109, 110 logical operators description 23 LOOKUP function description 111 Lookup transformation See also Designer Guide
G
I IIF function description 89 internationalization 3 Informatica documentation xii Webzine xvi INITCAP function description 93 internationalization 3 INSTR function description 94 integers converting other values 182 internationalization functions affected 3 invalid expression 3 sort order 3 IS_DATE function
,QGH[
instead of LOOKUP function 111 LOWER function description 113 internationalization 3 LPAD function description 114 LTRIM function description 116
M mapping parameters See also Designer Guide definition 2 mapping variables See also Designer Guide definition 2 mappings See Designer Guide MAX (dates) function description 118 internationalization 3 MAX (numbers) function description 119 internationalization 3 MEDIAN function description 121 milliseconds truncation 32 MIN (dates) function description 123 internationalization 3 MIN (numbers) function description 125 internationalization 3 minimum value returning 123 MOD function description 127 Modified Julian day format string 38, 42 month returning last day 106 MOVINGAVG function description 129 MOVINGSUM function description 131 multiple searches example of TRUE constant 16
N negative values SIGN 157 nested expressions operators 18 nesting stored procedure 26 NOT reserved word 8 NULL constant description 15 reserved word 8 null values aggregate functions 15, 50 checking for 97 filter conditions 15 ISNULL 97 logical operators 23 operators 15 string operator 21 numbers rounding 140 truncating 187 numeric functions ABS 57 CEIL 64 CUME 74 EXP 83 FLOOR 86 LN 109 LOG 110 MOD 127 MOVINGAVG 129 MOVINGSUM 131 POWER 136 ROUND (numbers) 140 SIGN 157 SQRT 160 summary 198 TRUNC (numbers) 187 numeric values converting to text strings 174 returning absolute value 57 returning cosine 69 returning hyperbolic cosine of 70 returning hyperbolic sine 159 returning hyperbolic tangent 169 returning logarithms 109, 110 returning minimum 125 returning sine 158
,Q G H[
returning square root 160 returning standard deviation 161 returning tangent 168 SIGN 157 numerical functions description 52
PERCENTILE function description 133 ports syntax 4 positive values SIGN 157 POWER function description 136 PowerMart 3.5 functions LOOKUP 111 primary key constraint null values 15 PROC_RESULT variable description 26 example 26 reserved word 8
:EXT 4 :LKP 4 :SD 5 :SEQ 5 :SP 5 :TD 5 relational databases dates 35 reserved words list 8 return values definition 2 syntax 4 ROUND (dates) function description 137 ROUND (numbers) function description 140 rounding dates 137 numbers 140 rows avoiding spaces 103 counting 71 returning average 129 returning first row 84 returning last row 104 returning sum 131 running total 74 skipping 82 RPAD function description 142 RR format string description 33 difference between YY and RR 34 using with IS_DATE 44 using with TO_CHAR 40 using with TO_DATE 44 RTRIM function description 144 running total returning 74
Q
S
quotation marks inserting single using CHR function 5
scientific functions COS function 69 COSH 70 description 52 SIN 158 SINH 159
O operator precedence expressions 18 operators arithmetic 19 comparison operators 22 definition 2 logical operators 23 null values 15 string operators 21 OR reserved word 8
P
R reference qualifiers
,QGH[
summary 199 TAN 168 TANH 169 :SD reference qualifier description 5 reserved word 8 :SEQ reference qualifier description 5 reserved word 8 sessions stopping 55 $$$SessStartTime description 27 example using in date functions 27 SESSSTARTTIME variable description 28 reserved word 8 using in date functions 28, 45 SET_DATE_PART function description 148 SETCOUNTVARIABLE function 146 SETMAXVARIABLE function 151 SETMINVARIABLE function 153 SETVARIABLE function 155 SIGN function description 157 SIN function description 158 sine returning 158, 159 single quotes in string literals CHR function 65 using CHR and CONCAT functions 68 SINH function description 159 skipping rows 82 sort order internationalization 3 :SP reference qualifier description 5 reserved word 8 spaces avoiding in rows 103 removing with DD_REJECT 12 special functions ABORT 55
DECODE 80 description 53 ERROR 82 IIF 89 LOOKUP 111 summary 200 SPOUTPUT reserved word 8 SQRT function description 160 square root returning 160 SSSSS format string using with IS_DATE 44 using with TO_CHAR 40 using with TO_DATE 44 standard deviation returning 161 STDDEV function description 161 stopping sessions 55 stored procedure nesting 26 string conversion dates 33 string literals single quotes in 65, 68 single quotes requirement 5 string operators description 21 strings adding blanks 114 adding characters 114 capitalization 93, 113, 189 character set 94 concatenating 21, 67 converting character strings to dates 176 converting dates to characters 170 converting length 142 converting numeric values to text strings 174 converting to double values 183 number of characters 108 removing blanks 116 removing blanks and characters 144 removing characters 116 returning portion 163 SUBSTR function description 163 sum returning 131, 166
,Q G H[
SUM function description 166 syntax expression 4 general rules 6 ports 4 return values 4 SYSDATE variable description 28 example 28 reserved word 8 system variables description 26
T TAN function description 168 tangent returning 168, 169 TANH function description 169 :TD reference qualifier description 5 reserved word 8 test functions description 53 IS_DATE 98 IS_NUMBER 101 IS_SPACES 103 ISNULL 97 summary 201 text strings converting numeric values 174 TO_CHAR (dates) function description 170 examples 39 format strings 38 TO_CHAR (numbers) function description 174 TO_DATE function description 176 examples 43 format strings 41 TO_DECIMAL function description 180 TO_FLOAT function description 181 TO_INTEGER function description 182
,QGH[
TO_NUMBER function description 183 transformation expressions null constraints 15 overview 2 transformation language compared to SQL 4 operators 18 overview 2 reserved words 8 transformation language components overview 2 transformations See Designer Guide TRUE constant description 16 reserved word 8 TRUNC (dates) function description 184 TRUNC (numbers) function description 187 truncating date/time values 32 dates 184 numbers 187
U Unicode See also Installation and Configuration Guide See also Session and Server Guide converting characters to Unicode values 61 converting to ASCII values 66 converting Unicode values 65 update strategy DD_DELETE example 10 DD_INSERT example 11 DD_REJECT example 12 DD_UPDATE example 13 UPPER function description 189 internationalization 3
V variable functions description 53 SETCOUNTVARIABLE 146 SETMAXVARIABLE 151 SETMINVARIABLE 153 SETVARIABLE 155
with partitioned sessions 53 variables $$$SessStartTime 27 local variables 29 PROC_RESULT 26 SESSSTARTTIME 28 SYSDATE 28 system variables 26 VARIANCE function description 190
W webzine Informatica URL xvi
Y year 2000 dates 33 YY format string difference between RR and YY 34 using with IS_DATE 44 using with TO_CHAR 40 using with TO_DATE 44
,Q G H[
,QGH[
