Self Join with Calculated field

[SimonPGreen]

Hi,

Is it possible to reference a calculated field in a query?

I am self joining a table with the following:

SELECT Applications.intValuationNumber, IIf(IsNull([Applications.dtePaymentExpected]),[Applications.
dtePaymentDueDate],[Applications.dtePaymentExpected]) AS [Due Date], IIf(IsNull([Applications.curCertificationValueReceived]),IIf(IsNull(
[Applications.curValuationExpected]),IIf(IsNull([Applications.
curValuationValueSubmitted]),[Applications.curForeCastValue],
[Applications.curValuationValueSubmitted]),[Applications.
curValuationExpected]),[Applications.curCertificationValueReceived]) AS [Gross Cumulative Value],[i] [Gross Cumulative Value]-iif(isnull([Gross Cumulative Value]),0,[b]Applications1.[Gross Cumulative Value][/b]) AS Movement[/i]
FROM Applications LEFT JOIN Applications AS Applications1 ON (Applications.intValuationNumber-1)=Applications1.intValuationNumber And Applications.fkPackageRef=Applications1.fkPackageRef;

The problem I have is that the in order to deduct the previous cumulative value (above in bold) which is calculated I have to reference it somehow.

Regards,

Simon

 

[Golom]

You cannot reference an alias defined in SELECT elsewhere in the same SELECT clause. You will need to repeat the definition of that field (i.e. all those IIFs) where you are referencing [Gross Cumulative Value].

I also notice that you have
- [Gross Cumulative Value] and
- Applications1.[Gross Cumulative Value].

Are those intended to be different things?

 

[SimonPGreen]

Golom,

Thanks for the prompt reply. I'm telling you what you know already but for the sake of clarity:

[Gross Cumulative Value] is an alias calculated via the iif function. Applications1.[Gross Cumulative Value] was my poor attempt at trying to specify the previous records value where applications is joined to itself as applications1 on the previous record - I know - hopeless!

I presume I just have to add all of the IIF statement that defines [Cumulative Gross Value] but change the references to Applications1.'fieldnames' instead and that should produce the previous value?

Regards,

Simon

 

[Golom]

'fraid not. Applications1 is a reference to fields in the table. [Gross Cumulative Value] however, is a computed field that exists only in this query. It isn't in the table to be referenced.

Using words like "previous" or "next" with respect to records is always problematic with SQL because there is no inherent ordering of records in an RDBMS.

[Gross Cumulative Value] is being computed from values in the current record. There isn't really any way to access results from the one before it using techniques like this. You would need to repeat the computation of [Gross Cumulative Value] (i.e. all those IIFs again) but using fields from Applications1 this time.

Incidently, your JOIN condition is

ON (Applications.intValuationNumber-1)=Applications1.intValuationNumber 
And Applications.fkPackageRef=Applications1.fkPackageRef

That may have an unintended effect. If the table is
[tt]Applications
intValuationNumber fkPackageRef
1 A
2 B
3 C
[/tt]
Then your LEFT JOIN will pull records from the table into the alias "Applications" but will have NULLs for the contents of "Applications1" because (for example), if intValuationNumber = 2 then

intValuationNumber - 1 = 1 but fkPackageRef values don't match on those two records so there is no matching record for "Applications1".

 

[SimonPGreen]

Golom,

Really appreciate the last post.

I have configured a simple test table in order to better understand this but still can't get the query correct:

SELECT table1.item,table1.value1,table1.value2,table1.value1+table1.value2 as CALC,CALC-IIF(IsNull(CALC,0,Table2.Value1+Table2.Value2))AS Movement FROM table1 LEFT JOIN table1 AS table2 ON (table1.item-1)=table2.item

Query should result in:

Item Value1 Value2 CALC Movement
1 100 100 200 200
2 200 300 500 300

Any ideas?

Simon