Table of Contents

Search

  1. Preface
  2. Introduction to Data Validation Option
  3. Repositories
  4. XML Data Source
  5. Tests for XML Data Sources
  6. Connections
  7. Expressions
  8. Table Pairs
  9. Tests for Table Pairs
  10. Single-Table Constraints
  11. Tests for Single-Table Constraints
  12. Examples of Tests from Spreadsheets
  13. SQL Views
  14. Lookup Views
  15. Join Views
  16. Aggregate Views
  17. Business Intelligence and Reporting Tools Reports
  18. Dashboards
  19. DVOCmd Command Line Program
  20. Troubleshooting
  21. Appendix A: Datatype Reference
  22. Appendix B: Reporting Views
  23. Appendix C: Metadata Import Syntax
  24. Appendix D: Jasper Reports
  25. Appendix E: Glossary

Data Validation Option User Guide

Data Validation Option User Guide

Selecting a Valid PowerCenter Transformation Data Type for an Expression

Selecting a Valid PowerCenter Transformation Data Type for an Expression

The PowerCenter transformation data type that you select for a field expression must be compatible with the data type of the field in the expression. To know the data type of a field, view the properties of the field in the Expression Editor. In the Expression Editor, select the appropriate PowerCenter transformation data type for the expression.
For example, if PowerCenter imports a table from an Oracle database, and the table includes a date field called PurchaseDate, PowerCenter converts the field to the
Date/Time
transformation data type. If you use the PurchaseDate field in an expression, set the data type of the expression to
Date/Time
. Set the precision and scale of the expression to the precision and scale of the
Date/Time
transformation data type.
The following table shows the transformation data types to which PowerCenter converts data types from various relational sources:
PowerCenter Transformation Data Type
IBM DB2 Data Type
Oracle Data Type
Microsoft SQL Server Data Type
Sybase Data Type
Teradata Data Type
Netezza Data Type
Bigint
Bigint
-
Bigint
Bigint
Bigint
BigInt
Binary
Blob Char for bit data, Varchar for bit data
Blob, Raw(L), LongRaw
Binary(L), Image, Timestamp, Varbinary(L)
Binary (n), Image, Timestamp, Varbinary (n)
Byte, Varbyte
-
Date/Time
Date, Time, Timestamp
Date, Timestamp
Datetime, Datetime2, Smalldatetime
Date, Datetime, Smalldatetime, Time, Timestamp (Sybase IQ)
Date, Time, Timestamp
Date, Time, Timestamp
Decimal
Decimal(P,S), Numeric(P,S)
Number(P,S)
Decimal(P,S), Money, Numeric(P,S), Smallmoney
Decimal (P,S), Money, Numeric (P,S), Smallmoney
Decimal
Numeric
Double
Float
Number
Float
Float
Float
Float8, Float4
Integer
Integer
-
Int, Numeric Identity
Int
Integer
Integer
Nstring
Graphic
Nchar, Nvarchar2
Nchar, Nvarchar, Sysname
Nchar (n), Nvarchar (n), Unichar, Univarchar
-
-
Ntext
Dbclob, Long Vargraphic
Nclob
Ntext
-
-
-
Real
-
-
Real
Real
-
Real
Small integer
Smallint
-
Smallint, Tinyint
Smallint, Tinyint
Byteint, Smallint
ByteInt, SmallInt
String
Char(L), Long Varchar, Varchar, Vargraphic
Char(L), Varchar(L), Varchar2(L)
Bit, Char(L), Varchar(L)
Bit, Char (n), Varchar (n)
Char, Varchar
Bool, Char, NChar(m), NVarchar(m), Varchar
Text
Clob
Clob, Long, XMLType based CLOB storage
Text
Text
-
-
The following table shows the PowerCenter transformation data types to which PowerCenter converts application data types:
PowerCenter Transformation Data Type
SAS Data Type
SAP Data Type
Salesforce Data Type
Bigint
Numeric
-
-
Binary
-
LRAW, PREC, RAW
-
Date/time
Date, Time, Datetime
ACCP, DATS, TIMS
Date, DateTime, Time
Decimal
Numeric
CURR, DEC
Currency, Double, Percent
Double
Numeric
FLTP
-
Integer
Numeric
INT1, INT2, INT4
Boolean, Int
Nstring
-
-
-
Ntext
-
-
-
Real
Numeric
-
-
Small integer
Numeric
-
-
String
String
RAWSTRING, SSTRING, STRG
AnyType, Base64, DataCategoryGroupReference, Email, Encrypted String, ID, Multipicklist, Picklist, Reference, String, Textarea, Url
Text
-
CHAR, CLNT, CUKY, LANG, LCHR, NUMC, QUAN, UNIT, VARC
-
The following table shows the PowerCenter transformation data types to which PowerCenter converts flat file and COBOL data types:
PowerCenter Dataype
Flat File Data Type
COBOL Data Type
Bigint
Bigint
-
Binary
-
-
Date/time
Datetime
-
Decimal
Number
Number
Double
Double
-
Integer
Integer
-
Nstring
Nstring
-
Ntext
-
-
Real
-
-
Small integer
-
-
String
String
Nstring, String
Text
-
-