SQL Syntax to Generate a Mapping
You can use an ANSI-compliant SQL statement to generate a mapping in the Developer tool.
The Developer tool can generate a mapping from a standard SELECT query. For example:
SELECT column_list FROM table-name
[WHERE clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause]
If the SELECT SQL statement contains a correlated subquery, the query is valid if it is possible to flatten or rewrite the query as a single standard query.
ANSI SQL does not support some datatypes. For example, if the query requests results from a data source where one of the columns has the type timeStampTZ, the SQL is not valid.
Correlated Subqueries
A correlated subquery is a subquery that uses values from the outer query in its WHERE clause. The Data Integration Service flattens the correlated subqueries before it runs the query.
The following table shows the results of a correlated subquery that the Data Integration Service flattened:
Type | Query |
|---|
Non-flattened | SELECT huge.* FROM huge WHERE c1 IN (SELECT c1 FROM tiny) |
Flattened | SELECT huge.* FROM huge, tiny WHERE huge.c1 = tiny.c1 |
The Data Integration Service can flatten a correlated subquery when it meets the following requirements:
- •The type is IN or a quantified comparison.
- •It is not within an OR operator or part of a SELECT list.
- •It does not contain the LIMIT keyword.
- •It does not contain a GROUP BY clause, aggregates in a SELECT list, or an EXIST or NOT IN logical operator.
- •It generates unique results. One column in the corelated subquery is a primary key. For example, if r_regionkey column is a primary key for the vs.nation virtual table, you can issue the following query: SELECT * FROM vs.nation WHERE n_regionkey IN (SELECT b.r_regionkey FROM vs.region b WHERE b.r_regionkey = n_regionkey).
- •If it contains a FROM list, each table in the FROM list is a virtual table in the SQL data service.