Table of Contents

Search

  1. Preface
  2. Mappings
  3. Mapplets
  4. Mapping Parameters
  5. Where to Assign Parameters
  6. Mapping Outputs
  7. Generate a Mapping from an SQL Query
  8. Dynamic Mappings
  9. How to Develop and Run a Dynamic Mapping
  10. Dynamic Mapping Use Cases
  11. Mapping Administration
  12. Export to PowerCenter
  13. Import From PowerCenter
  14. Performance Tuning
  15. Pushdown Optimization
  16. Partitioned Mappings
  17. Developer Tool Naming Conventions

Developer Mapping Guide

Developer Mapping Guide

Correlated Subqueries

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.

0 COMMENTS

We’d like to hear from you!