OWB 11gR2 – Lookup operator
By David Allan-Oracle on Sep 13, 2010
The lookup operator in OWB 11gR2 has been supercharged letting you utilize it in many more scenarios and generate more efficient SQL and in general be more flexible than before. The enhancements were requested from large use case scenarios and are summarized below (and expand on the doc here);
Lookup operator supports multiple lookup objects - more efficient use of screen real estate.
Lookup operator supports complex lookup conditions including a range of condition types such as =, <, >, or BETWEEN...AND (any boolean expression basically).
Preferences for which row to return in case of multiple rows in lookup result (ANY, FIRST,LAST,nTH).
Below we can see that there are multiple output groups in the lookup operator. We can lookup all the surrogate keys for example of one sales tables in a single lookup.
For each output group you can specify the table which you will be looking up information in, below we see that OUTGRP3 is looking up information in CHANNELS_TAB, note we could rename OUTGRP3 to have a meaningful name.
The lookup condition as before by default is a simple column equals column style condition for each output group and input pair. Note now in the panel there is a 'Freestyle Editing' option.
If you select the 'Freestyle Editing' you will change into a more traditional OWB expression editor with the available inputs in the tree and now the ability to do different types of lookups including complex expressions.
Last but definitely not least, we have the ability to control for each output group how the lookup row is to returned, do we want a single row and let it be;
- any row (uses rownum = 1)
- first row - uses MIN(column) KEEP (DENSE_RANK FIRST ORDER BY column_4_order ASC)
- last row - uses MAX(column) KEEP (DENSE_RANK FIRST ORDER BY column_4_order ASC)
- nTH row
We can also decide to make the mapping error if there are multiple matching lookup entries. How does it error? Well the generated code would cause the map to error with the SQL error;
ORA-01427: single-row subquery returns more than one row
Or we can decide to return all the matching rows.
For the case where no row matches you can still specify the default values to return.
These defaults are then used in the generated code. Many more options than before as you can see.
You'll also see from the generated code that a scalar subquery is generated for the single-row style lookups.
Advice on Using The Wizard? How do I use the lookup?
Well the quickest way of building one up when you add in a lookup operator is to exit the lookup wizard at Step 4 when defining the Input Attributes. Its much easier just to use the mapper to draw the inputs rather than type them in. So after I exit from the lookup wizard I map the input attributes then edit the lookup operator and define the actual lookups etc.
Some interesting changes there that were included in the lookup operator for the OWB 11gR2 release.