MORE SQL Help

[MartinS]

Anyone?
Sorry - but have tried endless options to get this to work for the cases where the selection fields are from the financial table, but nothing works.
Martin

[uwila]

Martin,

firstly, are you now saying that the user should only be able to see the results where InputStage=2 and the InputStage=1 records should be ignored entirely?

If so, please let me know as the query should be changed radically. Anyhow, to answer your last question directly:




As you wish to use a number of columns from the temporary table, you should include them in the 'inner' query.


SELECT COUNT(DISTINCT CAST(V.ValuationID AS varchar(10))
+ CAST(F.InputStage AS varchar(10)))
FROM Valuations V
INNER JOIN (SELECT ValuationID, MAX(InputStage) AS InputStage, PriceInflation, NomDiscRateAccruedPenWid, DiscountRateType
FROM Financial
GROUP BY ValuationID, PriceInflation, NomDiscRateAccruedPenWid, DiscountRateType) AS F ON F.ValuationID = V.ValuationID
INNER JOIN ValuationResults VR ON VR.ValuationID = V.ValuationID AND F.InputStage = VR.InputStage
INNER JOIN LUAssetAllocation AA ON AA.ValuationID = V.ValuationID
AND (F.NomDiscRateAccruedPenWid IS NOT NULL) AND (F.PriceInflation IS NOT NULL)
AND AA.AssetCategoryID IN (1, 2, 3, 4, 5, 6)
AND F.DiscountRateType = 0



As a matter of style (and ease of maintenance by another person, and as a means of getting the query correct!!!), I consider it bad form to use the same alias (in this case 'F') in 2 different scopes. In your reply to me, you used F as an alias within the temporary table generation and as the alias for the temporary table. The 2 uses of F are totally independant and the inner one does not have scope outside of the query in which it appears. It is easy to confuse the 2 - which is what you did when you wondered why the columns from F were unknown.

[MartinS]

Chris
I have replied to your email directly - thanks.
Martin