Hi!

I've a table in which some rows refer to other rows from the same table. I want to add a computed field which returns a sum over those referred rows.

A select with a selfreferring subquery would look like this:

SELECT
BP.ID,
(( SELECT SUM(LI.MENGE) FROM BELEGPOS LI WHERE LI.BP_REFID = BP.ID ))
FROM
BELEGPOS BP

returning the following plan:
PLAN (LI INDEX (BELEGPOS_BP_REFID))
PLAN (BP NATURAL)

BP.ID in the inner select refers to the outer table BELEGPOS (alias BP).

Now I wanted to provide this as an extra (computed by) column:

ALTER TABLE BELEGPOS ADD MENGELI COMPUTED BY
(( SELECT SUM(LI.MENGE) FROM BELEGPOS LI WHERE LI.BP_REFID = BELEGPOS.ID));

I have to use "BELEGPOS" in the inner WHERE clause to reference back to the outer table.

The problem here is: BELEGPOS.ID in the where clause of the inner select doesn't refer to the outer table. The optimizer/FB thinks it refers to the inner FROM clause where a table named BELEGPOS already is referenced.

Therefore the plan is terrible and slow:
PLAN (LI NATURAL)
PLAN (BP NATURAL)

How can I tell FB the inner where clause refers to the outer table?

BTW, this query gives the same slow plan:

SELECT
BP.ID,
(( SELECT SUM(LI.MENGE) FROM BELEGPOS LI WHERE LI.BP_REFID = BELEGPOS.ID ))
FROM
BELEGPOS BP

Alex