Hi

The optimizer has a problem that if you put a left outer join in a
view and then join it to another table it doesn't choose the correct
plan. Arno mentioned this in one of his sessions at the firebird
conference. I have had problems with this issue on a number of
occasions and have generally removed the left outer join from the
query. The other day I thought of a different way around the problem
which is to put the left outer join table into the field selection but
I am unsure if there is any down side to it. Below is simplified
version of one of our views.

CREATE VIEW "IDENTTIYLIST" (
"INVESTORNAME",
"ACCOUNTNAME"
) AS
select
InvestorRelate.InvestorName,
coalesce((select AcctName.acctnames from acctname where
acctname.invheadref = invhead.invheadref), '') as AccountName
from
InvestorRelate
inner join invhead on
InvestorRelate.InvestorIDRef = InvHead.IDRef

The original SQL was

CREATE VIEW "IDENTTIYLIST" (
"INVESTORNAME",
"ACCOUNTNAME"
) AS
select
InvestorRelate.InvestorName,
coalesce(AcctName.acctnames, '') as AccountName
from
InvestorRelate
inner join invhead on
InvestorRelate.InvestorIDRef = InvHead.IDRef
left outer join AcctName on
AcctName where acctname.invheadref = invhead.invheadref

The AcctName table is related to the InvHead table in a 0..1
relationship. I understand that if the relationship was a 0..N or
1..N this approach wouldn't work.

I notice that when I use the view, if I don't use the AccountName
field it doesn't do the select on the acctname table which helps
performance.

On a more general issue I know that the optimizer doesn't take into
account left outer joins but I wondered why it couldn't treat left
outer joins as inner joins when working out the streams (I think that
was the term Arno used) but then ignore any stream that would have a
left outer join before an inner join.

Thanks

Bernard