I'm not sure if this is the right place to post this...but here goes:

I am trying to make an updateable query which uses a subquery to
calculate one of its columns. It seems to work ok except I get the
same value in each row for "Previously Invoiced". The problem appears
to be in the (DOCLINE.ITEM_ID = DOCLINE.ITEM_ID). What I'm trying to
achieve is that the subquery should recalculate its value for each row
using the main row's Item_id. Is it possible to do this? If so, how?

Many thanks

Mitch

SELECT
DOCLINE.DOCLINE_ID,
DOCLINE.ITEM_ID,
DOCLINE.DOCLINE_QTY,
DOCLINE.DOCLINE_TOTAL_VALUE,
(
SELECT SUM( DOCLINE.DOCLINE_TOTAL_VALUE ) FROM DOC
INNER JOIN DOCLINE ON (DOC.DOC_ID = DOCLINE.DOC_ID)
WHERE
(DOC.DOC_ID < :DOC_ID) AND (DOCLINE.ITEM_ID = DOCLINE.ITEM_ID) AND
(DOC.PROJ_ID = :PROJ_ID)
)
AS PREVIOUSLY_INVOICED,
LOC_NAME

FROM LOC
RIGHT OUTER JOIN ITEM ON (LOC.LOC_ID = ITEM.LOC_ID)
INNER JOIN DOCLINE ON (ITEM.ITEM_ID = DOCLINE.ITEM_ID)

WHERE
DOCLINE.DOC_ID = :DOC_ID