I have a table that represents the parameters of a contract - including their change over time through addendums. The first addendum is a "special" addendum representing the parameters when the contract was first signed.

ProjectID is a FK to the Projects table whose primary key is also called ProjectID.

I want DeadlineDate to be a calculated field, calculated like so:

DeadlineDate COMPUTE BY ((
select first 1 AddMonth(contract.BeginDate, DeadlineMonths)
from addendums contract
where contract.projectid = projectid
order by contract.BeginDate ))

The problem is that in contract.projectid = projectid the second ProjectID has to reference the current row being computed, not the current row in the select statement (which is the same as contract.projectid).

I'm using Firebird. I need the column in the table and NOT in a SELECT statement because of ORM issues in the application using the database.

How do you know which row is the one you want to compute against? Why in your example are you computing rows 1 & 3 against the value in row 1 rather than the value in row 3?
–
Jeremy SteinSep 23 '09 at 14:57

1

Because row 1 is the first row for the project with ProjectID=20, hence it holds the date at which the contract was initiated. BeginDate for row 3 is the date at which the addendum was signed, but the deadline has to be computed based on the date the contract was signed.
–
AlexSep 23 '09 at 15:03