HI Alexandre

>> Test SQL (direct)
>>
>> SELECT i.ItemCode, itn.name, sl.ShelfQty
>> FROM Item i
>> JOIN Itemname itn On itn.ItemRef = i.ITEMREF
>> JOIN Stocklevel sl ON (sl.ItemRef = i.ItemRef AND sl.RegionRef = 12621)
>> WHERE i.ItemCode = 'A'
>>
>> PLAN JOIN (I INDEX (ITEM_ITEMCODE), SL INDEX (STOCKLEVEL_REGIONITEMREF),
>> ITN INDEX (ITEMNAME_ITEMREF))
>>
>>
>
> I expected some kind of grouping here, like:
>
> SELECT i.ItemCode, itn.name, sum(sl.ShelfQty)
> FROM Item i
> JOIN Itemname itn On itn.ItemRef = i.ITEMREF
> JOIN Stocklevel sl ON (sl.ItemRef = i.ItemRef AND sl.RegionRef = 12621)
> WHERE i.ItemCode = 'A'
> GROUP BY i.ItemCode, itn.name
>
> So you could compare apples to apples.
>
>
>
>

Sorry but my issue is lots of non grouped queries that link to StockLevel.

>
> SELECT i.ItemCode, itn.name, sl.ShelfQty
> FROM Item i
> JOIN Itemname itn On itn.ItemRef = i.ITEMREF
> JOIN vwStocklevel sl ON (sl.ItemRef = i.ItemRef)
> WHERE i.ItemCode = 'A' AND sl.RegionRef = 12621
>
>
>

No improvement.

> another option to force the item table to be the first scanned table (as in you direct table access) would be:
>
> SELECT i.ItemCode, itn.name, sl.ShelfQty
> FROM Item i
> JOIN Itemname itn On itn.ItemRef = i.ITEMREF
> LEFT JOIN vwStocklevel sl ON (sl.ItemRef = i.ItemRef)
> WHERE i.ItemCode = 'A' AND sl.RegionRef = 12621
> and sl.ItemRef is not null
>
> pay attention to the LEFT OUTER JOIN added ! The last "AND" is in place to mimic the INNER JOIN behavior, depending on your data structure it's not necessary.
>
>

The left join is wonderful. It makes the view SQL run as well as the
direct query. I would never have thought of using Left join (which I
though usually makes queries slower). The improvement works fine
regardless of whether the sl.RegionRef - 12621 is in the join line or
the where line.

Thanks very mush for your help. I will create this view and use it
where applicable.

Thanks Alexandre :)

Rob

>