Query calling query issue

[SimonPGreen]

Hi all,

Hope someone can help me as I am lost here!

Have a base query as follows:

SELECT Packages.pkPackageRef, applications.intValuationNumber, IIf(IsNull([Applications.dtePaymentExpected]),[Applications.dtePaymentDueDate],[Applications.dtePaymentExpected]) AS [Date], IIf(IsNull([Applications.curCertificationValueReceived]),IIf(IsNull([Applications.curValuationExpected]),IIf(IsNull([Applications.curValuationValueSubmitted]),[Applications.curForeCastValue],[Applications.curValuationValueSubmitted]),[Applications.curValuationExpected]),[Applications.curCertificationValueReceived]) AS GrossCumulativeValue, IIf(packages.blnMainContractorsPrePostRetention=True,[GrossCumulativeValue]*Packages.dblMainContractorsDiscount,[GrossCumulativeValue]*(1-Packages.dblRetentionValue)*Packages.dblMainContractorsDiscount) AS CumulativeMCDValue, IIf(Applications.blnReleaseFinalRetention=False,IIf(Applications.blnFinalApplication=False,[GrossCumulativeValue]*Packages.dblRetentionValue,[GrossCumulativeValue]*Packages.dblRetentionValue/2),[GrossCumulativeValue]*0) AS CumulativeRetention, [GrossCumulativeValue]-[CumulativeMCDValue]-[CumulativeRetention] AS CumulativePaymentValue, Packages.dblVATValue*CumulativePaymentValue AS VAT, CumulativePaymentValue+ VAT AS GrossCumulativePayment
FROM Packages INNER JOIN Applications ON Packages.PKPackageRef=Applications.FKPackageRef;

This is then called from another query as follows:

SELECT consolidatedquery.pkPackageRef, consolidatedquery.intValuationNumber, consolidatedquery.Date, ConsolidatedQuery.GrossCumulativePayment, ConsolidatedQuery.GrossCumulativePayment-iif(isnull(tmp.GrossCumulativePayment),0,tmp.GrossCumulativePayment) AS NetPayment
FROM ConsolidatedQuery LEFT JOIN ConsolidatedQuery AS tmp ON (ConsolidatedQuery.intValuationNumber-1)=tmp.intValuationNumber
WHERE ConsolidatedQuery.pkPackageRef=tmp.pkPackageRef;

I have the iif statement to use 0 where tmp.GrossCumulativePayment isnull i.e. intValuationNumber < 1.

The problem I have is the where statement at the end matches the pkPackageRef on the tmp table to ensure the movement calcs are correct - however this value doesn't exist for a line previous to intApplicationNumber 1 and hence the first line isn't calculated and returned as it doesn't meet the select criteria.

Any ideas?

Simon

 

[PHV]

Have you tried this (typed, untested) ?
...
FROM ConsolidatedQuery LEFT JOIN ConsolidatedQuery AS tmp ON (ConsolidatedQuery.intValuationNumber-1) = tmp.intValuationNumber AND ConsolidatedQuery.pkPackageRef = tmp.pkPackageRef;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SimonPGreen]

PH,

Yeah works a treat.

Thanks

Simon