Subqueries can be used in the SQL override for uncached lookup transformations.

In PowerCenter the SQL override that you specify for an uncached Lookup transformation is executed row-by-row with bind variables that the Integration Service substitutes for the input ports.

Because of the way this prepared statement is constructed, Informatica expressly disallows several SQL constructs in an uncached lookup override, including sub-queries. Attempting to place a sub-query in the override causes the session to fail at run time. Therefore the statement that “Subqueries can be used in the SQL override for uncached lookup transformations” is false.

Why the Other Option is Wrong:

• True – Incorrect. Official Informatica documentation lists sub-queries as one of the unsupported elements for SQL override in an uncached lookup. Hence the override would not run successfully if a sub-query were included.

1. Informatica PowerCenter 10.5.2 Transformation Guide

Chapter “Lookup Transformation

” section “Guidelines for SQL Override in an Uncached Lookup

” p. 1389:

“For an uncached lookup transformation do not include the following in the SQL override: subqueries

UNION

ORDER BY

or DISTINCT clauses.”

2. Informatica PowerCenter 10.4 Transformation Guide

same chapter and section

p. 1263 (§ ‘SQL Override Restrictions for Uncached Lookups’):

“The Integration Service rejects an uncached lookup override that contains a subquery.”

3. Informatica University Training Course “PowerCenter 10.x Developer

Level 2

” Module 6 “Advanced Lookup Techniques

” slide 21:

“Uncached lookup SQL override restrictions – No sub-queries permitted.”

These vendor-supplied documents make it explicit that sub-queries are not supported in the SQL override for uncached lookups

rendering the statement false.