Lookup query

Lookup query

Default query
1. SELECT
  1. All lookup ports
  2. Can generate sql
  3. Don't change ports/columns
2. ORDER BY
  1. orders the columns by the same order they appear in the lookup
  2. IS generates the order by
  3. you can't see this when you generate sql in sql override
Overriding
1. ORDER BY clause with fewer colums
  1. Increase performance
  2. suppress ORDERBY with comment notation
  3. cannot override or suppress when using pushdown optimization
  4. -- at the end
2. 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
  1. reswords.txt
    1. can put reserved words in
    2. put in installation dir
    3. IS will look at this file and put quotes in query
3. Can use parameters and variables
  1. in the query
  2. as a query
  3. IS expands variables at run time
4. / slash char in column name
  1. IS converts to _ underscore
    1. 1 generate default query
    2. 2 replace _ underscore with / slash
    3. 3 enclose in double quotes
5. add a WHERE clause
  1. limit rows
  2. DYNAMIC cache - use a filter before with same condition
6. uncached
  1. must use "Use Any Value Lookup Policy on Mulitple Match condition to override the lookup query
  2. IS does NOT build
  3. uses alias's for column names
    1. do not change
  4. to reference input ports in the WHERE clause, configure parameter binding
    1. SELECT EMPLOYEE.NAME as NAME, max(EMPLOYEE.ID) as ID from EMPLOYEE WHERE EMPLOYEE.NAME=?NAME1?
  5. You cannot use subqueries in the SQL override for uncached lookups. Lookup
Filtering source rows
1. lookup source filter
2. caching enabled
3. pushdown optimization
  1. creates a voew
4. properties tab
5. do not include WHERE
6. Enclose parms & vars in string identifies

Next up

Description

Resource summary

Similar

	Created by dbrown over 10 years ago