SQL - Reference current row in a computed column with SELECT

Question

Hi, 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.

Here's how the table should look like:

ID ProjectID BeginDate   DeadlineMonths DeadlineDate
1  20        20-12-2006  24             <computed= 20-12-2006 + 24 months>
2  23        12-03-2007  12             <computed= 12-03-2008 + 12 months>
3  20        06-09-2007  36             <computed= **20-12-2006** + 36 months>

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.

Answer 1

Can you create a view over your query and use it in the ORM?

CREATE VIEW v_addendums
AS
SELECT  ID, ProjectID, BeginDate, DeadlineMonths, 
        (
        SELECT  first 1 AddMonth(contract.BeginDate, DeadlineMonths)
        FROM    addendums contract
        WHERE   contract.projectid = a.projectid
        ORDER BY
                contract.BeginDate
        )
FROM    addendums a

Answer 2

Just prefix the field with table name of the current table:

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