Exclude results from first table in stored procedure with union and derived alias table

Question

I have a stored procedure with a union and derived table situation. The stored procedure basically needs to return all Cases rows where Cases.AssnKey = AssnCtrl.pKey, including ones that don't meet the criteria in the first table (hence the second).

I've tried several approaches to this. My first stab at it returned all the rows that met the criteria, then I learned that the customer wants to see ALL cases included. At one point I had a version that returned all Case rows PLUS duplicates for the ones that met the criteria in the first table. In the second table I'm trying to exclude records from the first to prevent the duplication.

I've arrived at the following, which parses, but throws an error

"Invalid object name 'A'".

I did not include my input parameters or declarations, but they are all working fine. Any help is greatly appreciated!

SELECT * 
FROM
   (SELECT 
       c.pKey, c.Name, c.LName, c.Balance, 
       SUM(cs.CAmount * @InterestRate * @DaysDiff) AS InterestAccrued, 
       ch.CollDesc, ac.Name AS AssociationName, (ac.IntrRate/100) AS IntrRate,
       SUM(CAmount) AS ChargeCodeBalance, c.MgmtKey,
       CASE c.PayPlanFlg
            WHEN 0 THEN 'No'
            WHEN 1 THEN 'Yes'
       END AS PayPlanFlg,
       CASE c.HoldFlg
            WHEN 0 THEN 'No'
            WHEN 1 THEN 'Yes'               
       END AS HoldFlg,
       CASE ac.AssmtChrgFreq
            WHEN 'D' THEN 'Daily'
            WHEN 'M' THEN 'Monthly'
            WHEN 'B' THEN 'Bi-monthly'
            WHEN 'Q' THEN 'Quarterly'
            WHEN 'A' THEN 'Annually'
            WHEN 'S' THEN 'Semi-annually'
            ELSE 'Unknown'
        END AS AssmtChrgFreq
    FROM Cases c 
    LEFT JOIN CaseSumm cs ON c.ClientKey = cs.ClientKey AND c.pKey = cs.CaseKey 
    INNER JOIN CollectHead ch ON c.ClientKey = ch.ClientKey AND ch.pKey = c.CollHeadKey 
    INNER JOIN AssnCtrl ac ON c.ClientKey = ac.ClientKey AND c.AssnKey = ac.pKey
    WHERE 
         c.ClientKey = @ClientKey
         AND c.AssnKey = @AssnKey   
         AND cs.TranCode IN (SELECT ChargeCode.RefNum.value('.', 'INT')
                             FROM @ChargeCodesXML.nodes('/chargecode/refnum') AS ChargeCode(RefNum))
        AND cs.TranDate >= @BeginDate 
        AND cs.TranDate <= @EndDate
        AND c.Active = 1
    GROUP BY c.pKey, c.Name, c.LName, c.Balance, ch.CollDesc, c.PayPlanFlg, c.HoldFlg, ac.Name, ac.IntrRate, ac.AssmtChrgFreq, c.MgmtKey) As A

    UNION       

    SELECT * FROM
    (SELECT c2.pKey, c2.Name, c2.LName, c2.Balance, 0 AS InterestAccrued, ch2.CollDesc, ac2.Name AS AssociationName, 0 AS IntrRate,
        0 AS ChargeCodeBalance, c2.MgmtKey,
        CASE c2.PayPlanFlg
            WHEN 0 THEN 'No'
            WHEN 1 THEN 'Yes'
        END
        AS PayPlanFlg,
        CASE c2.HoldFlg
            WHEN 0 THEN 'No'
            WHEN 1 THEN 'Yes'               
        END
        AS HoldFlg,
        CASE ac2.AssmtChrgFreq
            WHEN 'D' THEN 'Daily'
            WHEN 'M' THEN 'Monthly'
            WHEN 'B' THEN 'Bi-monthly'
            WHEN 'Q' THEN 'Quarterly'
            WHEN 'A' THEN 'Annually'
            WHEN 'S' THEN 'Semi-annually'
            ELSE 'Unknown'
        END
        AS AssmtChrgFreq
    FROM Cases c2 INNER JOIN CollectHead ch2
        ON c2.ClientKey = ch2.ClientKey
        AND ch2.pKey = c2.CollHeadKey INNER JOIN AssnCtrl ac2
        ON c2.ClientKey = ac2.ClientKey
        AND c2.AssnKey = ac2.pKey
    WHERE c2.ClientKey = @ClientKey
        AND c2.AssnKey = @AssnKey               
        AND c2.Active = 1
        AND c2.pKey NOT IN (SELECT pKey FROM A)
    GROUP BY c2.pKey, c2.Name, c2.LName, c2.Balance, ch2.CollDesc, c2.PayPlanFlg, c2.HoldFlg, ac2.Name, ac2.IntrRate, ac2.AssmtChrgFreq, c2.MgmtKey) As B

score 0 · Answer 1 · answered Oct 02 '12 at 21:22

In your (select pKey from A) near the end, you are trying to use A from the subquery in the first part of the UNION. You cannot access it from there. The way to do that would be to define A as a CTE, and access it twice.

However, beware that A is evaluated twice and that in very rare circumstances, the two A's can actually be very slightly different.

;WITH A AS (
    SELECT 
       c.pKey, c.Name, c.LName, c.Balance, 
       SUM(cs.CAmount * @InterestRate * @DaysDiff) AS InterestAccrued, 
       ch.CollDesc, ac.Name AS AssociationName, (ac.IntrRate/100) AS IntrRate,
       SUM(CAmount) AS ChargeCodeBalance, c.MgmtKey,
       CASE c.PayPlanFlg
            WHEN 0 THEN 'No'
            WHEN 1 THEN 'Yes'
       END AS PayPlanFlg,
       CASE c.HoldFlg
            WHEN 0 THEN 'No'
            WHEN 1 THEN 'Yes'               
       END AS HoldFlg,
       CASE ac.AssmtChrgFreq
            WHEN 'D' THEN 'Daily'
            WHEN 'M' THEN 'Monthly'
            WHEN 'B' THEN 'Bi-monthly'
            WHEN 'Q' THEN 'Quarterly'
            WHEN 'A' THEN 'Annually'
            WHEN 'S' THEN 'Semi-annually'
            ELSE 'Unknown'
        END AS AssmtChrgFreq
    FROM Cases c 
    LEFT JOIN CaseSumm cs ON c.ClientKey = cs.ClientKey AND c.pKey = cs.CaseKey 
    INNER JOIN CollectHead ch ON c.ClientKey = ch.ClientKey AND ch.pKey = c.CollHeadKey 
    INNER JOIN AssnCtrl ac ON c.ClientKey = ac.ClientKey AND c.AssnKey = ac.pKey
    WHERE 
         c.ClientKey = @ClientKey
         AND c.AssnKey = @AssnKey   
         AND cs.TranCode IN (SELECT ChargeCode.RefNum.value('.', 'INT')
                             FROM @ChargeCodesXML.nodes('/chargecode/refnum') AS ChargeCode(RefNum))
        AND cs.TranDate >= @BeginDate 
        AND cs.TranDate <= @EndDate
        AND c.Active = 1
    GROUP BY c.pKey, c.Name, c.LName, c.Balance, ch.CollDesc, c.PayPlanFlg, c.HoldFlg, ac.Name, ac.IntrRate, ac.AssmtChrgFreq, c.MgmtKey
)
SELECT * 
FROM A

    UNION       

    SELECT * FROM
    (SELECT c2.pKey, c2.Name, c2.LName, c2.Balance, 0 AS InterestAccrued, ch2.CollDesc, ac2.Name AS AssociationName, 0 AS IntrRate,
        0 AS ChargeCodeBalance, c2.MgmtKey,
        CASE c2.PayPlanFlg
            WHEN 0 THEN 'No'
            WHEN 1 THEN 'Yes'
        END
        AS PayPlanFlg,
        CASE c2.HoldFlg
            WHEN 0 THEN 'No'
            WHEN 1 THEN 'Yes'               
        END
        AS HoldFlg,
        CASE ac2.AssmtChrgFreq
            WHEN 'D' THEN 'Daily'
            WHEN 'M' THEN 'Monthly'
            WHEN 'B' THEN 'Bi-monthly'
            WHEN 'Q' THEN 'Quarterly'
            WHEN 'A' THEN 'Annually'
            WHEN 'S' THEN 'Semi-annually'
            ELSE 'Unknown'
        END
        AS AssmtChrgFreq
    FROM Cases c2 INNER JOIN CollectHead ch2
        ON c2.ClientKey = ch2.ClientKey
        AND ch2.pKey = c2.CollHeadKey INNER JOIN AssnCtrl ac2
        ON c2.ClientKey = ac2.ClientKey
        AND c2.AssnKey = ac2.pKey
    WHERE c2.ClientKey = @ClientKey
        AND c2.AssnKey = @AssnKey               
        AND c2.Active = 1
        AND c2.pKey NOT IN (SELECT pKey FROM A)
    GROUP BY c2.pKey, c2.Name, c2.LName, c2.Balance, ch2.CollDesc, c2.PayPlanFlg, c2.HoldFlg, ac2.Name, ac2.IntrRate, ac2.AssmtChrgFreq, c2.MgmtKey) As B

Storing the results of A in a temporary table might be a good idea then. — Andriy M, Oct 02 '12 at 21:26

Exclude results from first table in stored procedure with union and derived alias table

1 Answers1