Non-Ansi *= remediation for SQL 2005
-
Trying to remediate a non-ansi SQL 2000 statement to REMOVE the *= reference. SQL 2000's Enterprise Manager Designer regenerated the "REDESIGNED SQL" from the original SQL below.. but when I run the regenerated SQL, I receive the error msg:
Msg 1016, Level 15, State 2, Line 13
Outer join operators cannot be specified in a query containing joined tables.
Any help is much appreciated..
DECLARE @ProposalId INT
SELECT @ProposalId = 6000
-- Redesigned SQL (from E/M's Designer)
SELECT T1.AssetClassId, T1.DefaultPercentage, T.AssetCategoryId, G.AssetGroupId, G.AssetGroup
FROM TAB_1 T1 INNER JOIN
(SELECT T4.AssetClassId, Round(100 * SUM(ISNULL(T2.AssetAmount, 0)) / T3.InitialInvestment, 1) AS AllocatedPercentage
FROM TAB_2 T2, TAB_3 T3, TAB_4 T4
WHERE T3.ProposalId = T2.ProposalId
AND T2.IsIncludedInProposal = 1
AND T4.AssetClassId *= T2.AssetClassId
AND T3.ProposalId = 6000
GROUP BY T4.AssetClassId, T3.InitialInvestment) UA
ON T1.AssetClassId = UA.AssetClassId
AND T1.DefaultPercentage > UA.AllocatedPercentage
INNER JOIN TAB_4 C ON T1.AssetClassId = C.AssetClassId INNER JOIN
lh_AssetCategories T ON C.AssetCategoryId = T.AssetCategoryId INNER JOIN
lh_AssetGroups G ON T.AssetGroupId = G.AssetGroupId
WHERE (T1.ProposalId = 6000)
ORDER BY T1.Percentage DESC
-- ORIGINAL SQL containing NON-ANSI *= reference
SELECT
T1.AssetClassId,
T1.DefaultPercentage,
T.AssetCategoryId,
G.AssetGroupId,
G.AssetGroup
FROM
TAB_1 T1,
(SELECT AC.AssetClassId, Round(100 * SUM(ISNULL(T2.AssetAmount, 0)) / T3.InitialInvestment, 1) AS AllocatedPercentage
FROM
TAB_2 T2,
TAB_3 P,
TAB_4 AC
WHERE
P.ProposalId = T2.ProposalId AND
T2.IsIncludedInProposal = 1 AND
AC.AssetClassId *=T2.AssetClassId AND
P.ProposalId = @ProposalId
GROUP BY
AC.AssetClassId,
P.InitialInvestment
 
UA,TAB_4 C,
lh_AssetCategories T,
lh_AssetGroups G
WHERE
T1.ProposalId = @ProposalId AND
T1.AssetClassId = UA.AssetClassId AND
UA.AllocatedPercentage < T1.DefaultPercentage AND
T1.AssetClassId = C.AssetClassId AND
C.AssetCategoryId = T.AssetCategoryId AND
T.AssetGroupId = G.AssetGroupId
ORDER BY
T1.Percentage DESC
BT -
Actually - the error stems from the following subset of the above SQL:
SELECT T3.AssetClassId, Round(100 * SUM(ISNULL(T1.AssetAmount, 0)) / T2.InitialInvestment, 1)
AS AllocatedPercentage
FROM TAB_1 T1, TAB_2 T2, TAB_3 T3
WHERE T2.ProposalId = T1.ProposalId
AND T1.IsIncludedInProposal = 1
AND T3.AssetClassId *= T1.AssetClassId
AND T2.ProposalId = 6000
GROUP BY T3.AssetClassId, T2.InitialInvestment
Msg 303, Level 16, State 1, Line 1
The table 'TAB_1' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.
BT -
Your Enterprise Manager Designer fiddle obviously does not cope with derived tables. If you want to do this you will have to do the derived tables separately.
I am not convinced that you are posting exact copies of the queries, but something like the following should work:
SELECT T1.AssetClassId, T1.DefaultPercentage, T.AssetCategoryId, G.AssetGroupId, G.AssetGroup
FROM TAB_1 T1
JOIN (
SELECT T4.AssetClassId
,ROUND(100 * ISNULL(SUM(T2.AssetAmount), 0) / T3.InitialInvestment, 1) AS AllocatedPercentage
FROM TAB_2 T2
JOIN TAB_3 T3
ON T2.ProposalId = T3.ProposalId
AND T3.ProposalId = 6000
AND T2.IsIncludedInProposal = 1
RIGHT JOIN TAB_4 T4
ON T2.AssetClassId = T4.AssetClassId
GROUP BY T4.AssetClassId, T3.InitialInvestment
) UA
ON T1.AssetClassId = UA.AssetClassId
AND T1.DefaultPercentage > UA.AllocatedPercentage
AND T1.ProposalId = 6000
JOIN TAB_4 C
ON T1.AssetClassId = C.AssetClassId
JOIN lh_AssetCategories T
ON C.AssetCategoryId = T.AssetCategoryId
JOIN lh_AssetGroups G
ON T.AssetGroupId = G.AssetGroupId
ORDER BY T1.Percentage DESC
-
Close but no cigar.. We've had 3 DBA's and a couple developers attack this one and still no resolution. Need to start by understanding why this SQL is returning error:
DECLARE @ProposalId INT
SELECT @ProposalId = 6000
SELECT
T3.AssetClassId,
Round(100 * SUM(ISNULL(T1.AssetAmount, 0)) / T2.InitialInvestment, 1) AS AllocatedPercentage
FROM
TAB_1 T1,
TAB_2 T2,
TAB_3 T3
WHERE
T2.ProposalId = T1.ProposalId AND
T1.IsIncludedInProposal = 1 AND
T3.AssetClassId *= T1.AssetClassId AND
T2.ProposalId = @ProposalId
GROUP BY
T3.AssetClassId,
T2.InitialInvestment
Msg 303, Level 16, State 1, Line 4
The table 'TAB_1' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.
BT -
Resolved !!! I needed to add the CASE statement check for NULLS. This resolved the issue -- here is the new SQL:
SELECT
T3.AssetClassId,
CASE WHEN Round(100 * SUM(ISNULL(T1.AssetAmount, 0)) / T2.InitialInvestment, 1) IS NULL THEN 0
ELSE Round(100 * SUM(ISNULL(T1.AssetAmount, 0)) / T2.InitialInvestment, 1)
END AS AllocatedPercentage
FROM TAB_1 T1
JOIN TAB_2 T2
ON T2.ProposalId = T1.ProposalId AND T1.IsIncludedInProposal = 1
AND T2.ProposalId = @ProposalId
RIGHT OUTER JOIN TAB_3 T3
ON T3.AssetClassId = T1.AssetClassId
GROUP BY
T3.AssetClassId,
T2.InitialInvestment
BT
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply