--- In firebird-support@yahoogroups.com, Chris Wallis wrote:

> select
> h0.HSD_DOC_ID,
> h0.SEC_ID,
> h0.HLDG_SHARES,
> h0.HLDG_MKT_VALUE,
> h0.HLDG_PAR_VALUE,
> h0.HLDG_SEQNO,
> h1.HSD_DOC_ID,
> h1.SEC_ID,
> h1.HLDG_SHARES,
> h1.HLDG_MKT_VALUE,
> h1.HLDG_PAR_VALUE,
> h1.HLDG_SEQNO
> from
> holdings h0 full join
> holdings h1 on h0.sec_id=h1.sec_id
> where
> (
> h0.hsd_doc_id=:IPREV_DOC_ID or
> h0.hsd_doc_id is null
> )
> and
> (
> h1.hsd_doc_id=:ADOCID
> )

Hi Chris!

Your where clause makes me suspect that what you're doing is not
really a full join, but a right join camuflaged as a full join.
Assuming hsd_doc_id has a value in every case, I think your SQL can be
simplified like this (also rewritten from right join to full join
since left joins are more common and I'm more likely to make an error
using right joins):

select
h0.HSD_DOC_ID,
h0.SEC_ID,
h0.HLDG_SHARES,
h0.HLDG_MKT_VALUE,
h0.HLDG_PAR_VALUE,
h0.HLDG_SEQNO,
h1.HSD_DOC_ID,
h1.SEC_ID,
h1.HLDG_SHARES,
h1.HLDG_MKT_VALUE,
h1.HLDG_PAR_VALUE,
h1.HLDG_SEQNO
from
join holdings h1
left join holdings h0 on h0.sec_id=h1.sec_id and
h0.hsd_doc_id=:IPREV_DOC_ID
where
h1.hsd_doc_id=:ADOCID

HTH,
Set