v2.6 query, aliases un-mangled (as there's no inheritance, this is safe)
SELECT [LPLA_28].[PortfolioNo],
[LPLA_28].[SecurityEName],
[LPLA_28].[AmountCcyBalance]
FROM ((SELECT COALESCE([LPLA_1].[PFNo],
0 /* @LO01 /) AS [PortfolioNo],
COALESCE([LPLA_1].[SecCode],
0 / @LO02 /) AS [SecurityEName],
COALESCE([LPLA_1].[AmountBalance],
0 / @LO03 /) AS [AmountCcyBalance]
FROM (((SELECT [LPLA_5].[LPAV_1] AS [TrxNo],
[LPLA_5].[SecCode]
FROM (SELECT [LPLA_2].[PFNo],
[LPLA_2].[SecCode],
MAX([LPLA_2].[TrxNo]) AS [LPAV],
MAX([LPLA_2].[TrxNo]) AS [LPAV_1]
FROM ([LLBLTest].[dbo].[Transactions] [LPLA_2]
INNER JOIN [LLBLTest].[dbo].[PortfolioData] [LPLA_3]
ON [LPLA_2].[PFNo] = [LPLA_3].[PortfolioID])
WHERE (((([LPLA_2].[TrxDate] <= '2011-01-01T00:00:00.00' / @TrxDate4 /)
AND ([LPLA_2].[trxTypeNormal] = 1 / @TrxTypeNormal5 /))))
GROUP BY [LPLA_2].[PFNo],
[LPLA_2].[SecCode]) [LPLA_5]
WHERE ((([LPLA_5].[LPAV] > 0 / @LPAV_6 /)))) [LPLA_9]
INNER JOIN [LLBLTest].[dbo].[Transactions] [LPLA_1]
ON [LPLA_1].[TrxNo] = [LPLA_9].[TrxNo])
INNER JOIN [LLBLTest].[dbo].[SecurityData] [LPLA_12]
ON [LPLA_1].[SecCode] = [LPLA_12].[Code])) [LPLA_30]
LEFT JOIN (SELECT COALESCE([LPLA_15].[PFNo],
0 / @LO07 /) AS [PortfolioNo],
COALESCE([LPLA_15].[SecCode],
0 / @LO08 /) AS [SecurityEName],
COALESCE([LPLA_15].[AmountBalance],
0 / @LO09 /) AS [AmountCcyBalance]
FROM (((SELECT [LPLA_19].[LPAV_1] AS [TrxNo],
[LPLA_19].[SecCode]
FROM (SELECT [LPLA_30].[PFNo],
[LPLA_30].[SecCode],
MAX([LPLA_14].[TrxNo]) AS [LPAV],
MAX([LPLA_14].[TrxNo]) AS [LPAV_1]
FROM ([LLBLTest].[dbo].[Transactions] [LPLA_16]
INNER JOIN [LLBLTest].[dbo].[PortfolioData] [LPLA_17]
ON [LPLA_16].[PFNo] = [LPLA_17].[PortfolioID])
WHERE (((([LPLA_30].[TrxDate] <= '2011-01-01T00:00:00.00' / @TrxDate10 /)
AND ([LPLA_30].[trxTypeNormal] = 1 / @TrxTypeNormal11 /))))
GROUP BY [LPLA_30].[PFNo],
[LPLA_30].[SecCode]) [LPLA_19]
WHERE ((([LPLA_19].[LPAV] > 0 / @LPAV_12 */)))) [LPLA_23]
INNER JOIN [LLBLTest].[dbo].[Transactions] [LPLA_15]
ON [LPLA_15].[TrxNo] = [LPLA_23].[TrxNo])
INNER JOIN [LLBLTest].[dbo].[SecurityData] [LPLA_26]
ON [LPLA_15].[SecCode] = [LPLA_26].[Code])) [LPLA_28]
ON ([LPLA_30].[PortfolioNo] = [LPLA_28].[PortfolioNo]
AND [LPLA_30].[SecurityEName] = [LPLA_28].[SecurityEName]))
v3.0 query, not beautified by LLBLGenProf, as it bugs currently with queries with too many subqueries, so hand beautified:
SELECT [LPLA_34].[PortfolioNo], [LPLA_34].[SecurityEName], [LPLA_34].[AmountCcyBalance]
FROM ( (
SELECT COALESCE([LPLA_1].[PFNo], @p2) AS [PortfolioNo], COALESCE([LPLA_1].[SecCode], @p4) AS [SecurityEName], COALESCE([LPLA_1].[AmountBalance], @p6) AS [AmountCcyBalance]
FROM ((( (
SELECT [LPLA_5].[LPAV_1] AS [TrxNo], [LPLA_5].[SecCode]
FROM (
SELECT [LPLA_2].[PFNo], [LPLA_2].[SecCode], MAX([LPLA_2].[TrxNo]) AS [LPAV], MAX([LPLA_2].[TrxNo]) AS [LPAV_1]
FROM ( [LLBLTest].[dbo].[Transactions] [LPLA_2]
INNER JOIN [LLBLTest].[dbo].[PortfolioData] [LPLA_3] ON [LPLA_2].[PFNo] = [LPLA_3].[PortfolioID])
WHERE ( ( ( ( [LPLA_2].[TrxDate] <= @p7) AND ( [LPLA_2].[trxTypeNormal] = @p8 ))))
GROUP BY [LPLA_2].[PFNo], [LPLA_2].[SecCode]
) [LPLA_5]
WHERE ( ( ( [LPLA_5].[LPAV] > @p10)))
) [LPLA_9]
INNER JOIN [LLBLTest].[dbo].[Transactions] [LPLA_1] ON [LPLA_1].[TrxNo] = [LPLA_9].[TrxNo])
INNER JOIN [LLBLTest].[dbo].[PortfolioData] [LPLA_12] ON [LPLA_1].[PFNo] = [LPLA_12].[PortfolioID])
INNER JOIN [LLBLTest].[dbo].[SecurityData] [LPLA_15] ON [LPLA_1].[SecCode] = [LPLA_15].[Code])
) [LPLA_36]
LEFT JOIN (
SELECT COALESCE([LPLA_18].[PFNo], @p12) AS [PortfolioNo], COALESCE([LPLA_18].[SecCode], @p14) AS [SecurityEName], COALESCE([LPLA_18].[AmountBalance], @p16) AS [AmountCcyBalance]
FROM ((( (
SELECT [LPLA_22].[LPAV_1] AS [TrxNo], [LPLA_22].[SecCode]
FROM (
SELECT [LPLA_19].[PFNo], [LPLA_19].[SecCode], MAX([LPLA_19].[TrxNo]) AS [LPAV], MAX([LPLA_19].[TrxNo]) AS [LPAV_1]
FROM ( [LLBLTest].[dbo].[Transactions] [LPLA_19]
INNER JOIN [LLBLTest].[dbo].[PortfolioData] [LPLA_20] ON [LPLA_19].[PFNo] = [LPLA_20].[PortfolioID])
WHERE ( ( ( ( [LPLA_19].[TrxDate] <= @p17) AND ( [LPLA_19].[trxTypeNormal] = @p18 ))))
GROUP BY [LPLA_19].[PFNo], [LPLA_19].[SecCode]
) [LPLA_22]
WHERE ( ( ( [LPLA_22].[LPAV] > @p20)))
) [LPLA_26]
INNER JOIN [LLBLTest].[dbo].[Transactions] [LPLA_18] ON [LPLA_18].[TrxNo] = [LPLA_26].[TrxNo])
INNER JOIN [LLBLTest].[dbo].[PortfolioData] [LPLA_29] ON [LPLA_18].[PFNo] = [LPLA_29].[PortfolioID])
INNER JOIN [LLBLTest].[dbo].[SecurityData] [LPLA_32] ON [LPLA_18].[SecCode] = [LPLA_32].[Code])
) [LPLA_34] ON ( [LPLA_36].[PortfolioNo] = [LPLA_34].[PortfolioNo] AND [LPLA_36].[SecurityEName] = [LPLA_34].[SecurityEName]))
Error on v2.6:
The multi-part identifier "LPLA_30.TrxDate" could not be bound.
The multi-part identifier "LPLA_30.trxTypeNormal" could not be bound.
The multi-part identifier "LPLA_30.PFNo" could not be bound.
The multi-part identifier "LPLA_30.SecCode" could not be bound.
The multi-part identifier "LPLA_30.PFNo" could not be bound.
The multi-part identifier "LPLA_30.SecCode" could not be bound.
The multi-part identifier "LPLA_14.TrxNo" could not be bound.
The multi-part identifier "LPLA_14.TrxNo" could not be bound.
I still think this is due to re-aliasing of the same anonymous type, however I'll look into whether this is indeed the case, or that another issue we've fixed in v3 is the reason why it works in v3.
