null
US
Sign In
Sign Up for Free
Sign Up
We have detected that Javascript is not enabled in your browser. The dynamic nature of our site means that Javascript must be enabled to function properly. Please read our
terms and conditions
for more information.
Next up
Copy and Edit
You need to log in to complete this action!
Register for Free
343953
Lookup query
Description
1 INFA (Lookup Transformation) Mind Map on Lookup query, created by dbrown on 09/11/2013.
No tags specified
infa
lookup transformation
infa
lookup transformation
1
Mind Map by
dbrown
, updated more than 1 year ago
More
Less
Created by
dbrown
almost 11 years ago
33
0
0
Resource summary
Lookup query
Default query
SELECT
All lookup ports
Can generate sql
Don't change ports/columns
ORDER BY
orders the columns by the same order they appear in the lookup
IS generates the order by
you can't see this when you generate sql in sql override
Overriding
ORDER BY clause with fewer colums
Increase performance
suppress ORDERBY with comment notation
cannot override or suppress when using pushdown optimization
-- at the end
If the table name or any column name in the lookup query contains a reserved word, you must ensure that all reserved words are enclosed in quotes
reswords.txt
can put reserved words in
put in installation dir
IS will look at this file and put quotes in query
Can use parameters and variables
in the query
as a query
IS expands variables at run time
/ slash char in column name
IS converts to _ underscore
1 generate default query
2 replace _ underscore with / slash
3 enclose in double quotes
add a WHERE clause
limit rows
DYNAMIC cache - use a filter before with same condition
uncached
must use "Use Any Value Lookup Policy on Mulitple Match condition to override the lookup query
IS does NOT build
uses alias's for column names
do not change
to reference input ports in the WHERE clause, configure parameter binding
SELECT EMPLOYEE.NAME as NAME, max(EMPLOYEE.ID) as ID from EMPLOYEE WHERE EMPLOYEE.NAME=?NAME1?
You cannot use subqueries in the SQL override for uncached lookups. Lookup
Filtering source rows
lookup source filter
caching enabled
pushdown optimization
creates a voew
properties tab
do not include WHERE
Enclose parms & vars in string identifies
Show full summary
Hide full summary
Want to create your own
Mind Maps
for
free
with GoConqr?
Learn more
.
Similar
Lookup trans
dbrown
Properties of Lookup
dbrown
Lookup condition
dbrown
Lookup Ports
dbrown
Returning multiple rows
dbrown
Lookup Cache
dbrown
Database Deadlock Resilience
dbrown
Transformation Functions
dbrown
Lookup Ports - created from Mind Map
dbrown
Lookup condition - created from Mind Map
dbrown
Lookup Cache - created from Mind Map
dbrown
Browse Library