Function Support in Queries that Generate a Mapping
Informatica supports functions that meet the ANSI SQL-92 standard.
In addition, some functions have specific syntax requirements.
The following table lists the functions and supported syntax:
Function | Syntax |
|---|
DATE( ) | To specify the format of a date: DATE(format '<format>') where <format> is a standard date format. Example: SELECT DATE(format 'dd-mm-yyyy') from table |
POSITION( ) | To determine the position of a substring in a literal string: POSTITION('<substring>', '<string>'
) Example: POSITION('MA', 'James Martin') To determine the position of a substring in a table column: POSTITION('<substring>', <column_name>
) Example: POSITION('MA', FULL_NAME) |
Generate a Mapping from an SQL Query with an Unsupported Function
When the Developer tool generates a mapping from SQL, it validates the functions in the query. Use ANSI-compliant SQL to ensure valid mapping generation.
If the Developer tool encounters an unknown function in a valid SQL statement, it might generate a mapping that contains a transformation labeled FIX_ME or an expression labeled FIX_EXPR. Edit these objects to fix the mapping and get valid results. Unknown functions appear as a warning message in the mapping log file.
For example, you use the following SQL statement to generate a mapping:
SELECT unknownFunctionABC(c_custkey,c_comment) from customer
The following image shows how the mapping generated from this SQL statement includes an Expression transformation that requires fixing:
Notice that the Expression transformation is marked with an error icon. Use the Ports tab to edit the erroneous expression. The mapping is not valid until you correct the error.
INSERT, UPDATE and DELETE Syntax
Use the following syntax to create valid INSERT, UPDATE and DELETE statements:
- •Use the following syntax for an INSERT statement:
INSERT INTO <TABLENAME> [<list>]
<select query>
- •Use the following syntax for an UPDATE statement:
UPDATE [schema .] { table | view} [ alias ]
SET column = { expr | subquery }
[, column = { expr | subquery }]... [WHERE condition]
- •Use the following syntax for a DELETE statement:
DELETE FROM <Table> [[<AS>] <ALIAS>] [WHERE condition]
Rules and Guidelines for INSERT, UPDATE, and DELETE Statements
Consider the following rules and guidelines for INSERT, UPDATE, and DELETE statements:
- •An INSERT, UPDATE, or DELETE statement creates source and target objects in the mapping that are logical data objects.
- •Only one INSERT, UPDATE, or DELETE statement is valid. For example, a statement that contains an INSERT and a nested UPDATE statement is not valid.
- •When the INSERT, UPDATE, or DELETE SQL statement contains a correlated subquery, the Developer tool cannot generate a mapping.
- •An UPDATE or DELETE statement creates an Update Strategy transformation in a mapping. Because an Update Strategy transaction requires a primary key, the data target must contain a primary key. After mapping generation, verify the primary keys.
- •The Developer tool ignores any INSERT statement in an ORDER BY clause because relational databases do not follow ordering when inserting data.