Hi Marcel,
The WHERE clause filters out cars w/o both make and model. The NVL() is necessary if you don't want to see NULLs in the result in case when one of them (make or color) is not present in the cars table.
For optimization info see
Using Rushmore Query Optimization to Speed Data Access in the help
>
>That helps me get a clearer picture. I made a slight alteration because the where clause seems to handle the same thing as NVL(). When you say it is not optimizable do you mean just using the EMPTY() function? Is there information somewhere on optimizable and non-optimizable functions? I changed it to look as follows:
>
>
>SELECT vehicles.order_key, lm.lup_value as make, lc.lup_value as color ;
> FROM vehicles ;
> LEFT JOIN lookup lm ;
> ON lm.lup_id = vehicles.make_key ;
> LEFT JOIN lookup lc ;
> ON lc.lup_id = vehicles.color_key ;
> WHERE vehicles.make_key <> 0 OR vehicles.color_key <> 0
>
>
>
--sb--