Multiple Part Identifier could not be bound in a Derived Table

Question

I'm trying to run the SUM of MAX values, and then updating them into a column. I think the derived table is the right way to go, but now I keep getting a multi-part id could not be bound error that I dont know how to get round.

I'm running this on SSMS, and it will be my Db for a PowerApp. I'm essentially trying to take the distinct or MAX values of Assessment Hours from multiple Units, then add those together, grouped by the Staff ID number. I'm working with a derived table to try and mix the two aggregate functions.

    WITH pretotalAssessment as
    (
    SELECT dbo.StaffTotals.Entry_ID, ISNULL(SUM(maxAssess),0) AS maxAssessHours
    FROM
    (
        SELECT dbo.StaffTotals.Entry_ID, dbo.Units.[Unit Name],
        ISNULL(MAX(dbo.Units.[Assessment Hours]),0) AS maxAssess
        FROM dbo.Units
        INNER JOIN dbo.StaffTotals ON dbo.StaffTotals.Entry_ID = dbo.Units.Entry_ID
        GROUP BY dbo.StaffTotals.Entry_ID,dbo.Units.[Unit Name]
        )Units
    )
    UPDATE preStaffTotals
    SET preStaffTotals.Assessment = pretotalAssessment.maxAssessHours
    FROM dbo.StaffTotals AS preStaffTotals
    INNER JOIN pretotalAssessment ON preStaffTotals.Entry_ID = pretotalAssessment.Entry_ID;

My error is:

"The multi-part identifier "dbo.StaffTotals.Entry_ID" could not be bound."

I'm still quite new to SQL, so this is all a learning curve for me!

score 0 · Answer 1 · answered Oct 08 '19 at 14:35

0

Your first SELECT in the CTE is from a derived table. That means that dbo.StaffTotals isn't accessible in the SELECT list.

You need to replace:

SELECT dbo.StaffTotals.Entry_ID

With:

SELECT Units.Entry_ID

answered Oct 08 '19 at 14:35

Tab Alleman

31,483
7
36
52

Thanks for that, but now I'm getting "Column 'Units.Entry_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." – Epic Triffid Oct 08 '19 at 14:50
https://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e – Tab Alleman Oct 08 '19 at 17:45

ChrisCarroll · Answer 2 · 2019-10-08T15:16:33.890

You are SELECTING the full [schema].[tableName].[columnName] of dbo.StaffTotals.Entry_ID FROM Units.

Change to the below and it should work;

WITH pretotalAssessment as
(
SELECT Entry_ID, ISNULL(SUM(maxAssess),0) AS maxAssessHours
FROM
    (
    SELECT dbo.StaffTotals.Entry_ID, dbo.Units.[Unit Name],
    ISNULL(MAX(dbo.Units.[Assessment Hours]),0) AS maxAssess
    FROM dbo.Units
    INNER JOIN dbo.StaffTotals ON dbo.StaffTotals.Entry_ID = dbo.Units.Entry_ID
    GROUP BY dbo.StaffTotals.Entry_ID,dbo.Units.[Unit Name]
    )Units
    GROUP BY Entry_ID

)
UPDATE preStaffTotals
SET preStaffTotals.Assessment = pretotalAssessment.maxAssessHours
FROM dbo.StaffTotals AS preStaffTotals
INNER JOIN pretotalAssessment ON preStaffTotals.Entry_ID = pretotalAssessment.Entry_ID;

I would also suggest using COALESCE() instead of ISNULL() as COALESCE() is the ANSI standard and ISNULL() isn't. Also COALESCE() can have multiple arguments, whereas ISNULL() can only have 1. So easier for future dev changes. so;

WITH pretotalAssessment as
(
SELECT Entry_ID, COALESCE(SUM(maxAssess),0) AS maxAssessHours
FROM
    (
    SELECT dbo.StaffTotals.Entry_ID, dbo.Units.[Unit Name],
    COALESCE(MAX(dbo.Units.[Assessment Hours]),0) AS maxAssess
    FROM dbo.Units
    INNER JOIN dbo.StaffTotals ON dbo.StaffTotals.Entry_ID = dbo.Units.Entry_ID
    GROUP BY dbo.StaffTotals.Entry_ID,dbo.Units.[Unit Name]
    )Units
    GROUP BY Entry_ID

)
UPDATE preStaffTotals
SET preStaffTotals.Assessment = pretotalAssessment.maxAssessHours
FROM dbo.StaffTotals AS preStaffTotals
INNER JOIN pretotalAssessment ON preStaffTotals.Entry_ID = pretotalAssessment.Entry_ID;

Thanks for that, but now I'm getting "Column 'Units.Entry_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." — Epic Triffid, Oct 08 '19 at 14:50
Add "GROUP BY Entry_ID" in a row after the word Units. sorry, I didn't notice the sum(), I only saw the max(). I've updated answer with full scripts — ChrisCarroll, Oct 08 '19 at 15:15

Multiple Part Identifier could not be bound in a Derived Table

2 Answers2