Lookup query - created from Mind Map_1

Seite 1

Default query

SELECT

ORDER BY

All lookup ports

Can generate sql

Don't change ports/columns

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

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

Can use parameters and variables

/ slash char in column name

add a WHERE clause

uncached

Increase performance

suppress ORDERBY with comment notation

cannot override or suppress when using pushdown optimization

-- at the end

reswords.txt

can put reserved words in

put in installation dir

IS will look at this file and put quotes in query

in the query

as a query

IS expands variables at run time

IS converts to _ underscore

1 generate default query

2 replace _ underscore with / slash

3 enclose in double quotes

limit rows

DYNAMIC cache - use a filter before with same condition

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

to reference input ports in the WHERE clause, configure parameter binding

You cannot use subqueries in the SQL override for uncached lookups. Lookup

do not change

SELECT EMPLOYEE.NAME as NAME, max(EMPLOYEE.ID) as ID from EMPLOYEE WHERE EMPLOYEE.NAME=?NAME1?

Filtering source rows

lookup source filter

caching enabled

pushdown optimization

properties tab

do not include WHERE

Enclose parms & vars in string identifies

creates a voew

Lookup query