CODEWITHSUNDEEP
sqlsql-servervisual-studioreporting-servicesssrs-2008
CJ Boat
CJ Boat

Reputation: 71

SQL / SSRS: The multi-part identifier " " could not be bound

Working on a report that currently is in two columns on two datasets, an dam trying to combine the datasets into one single query. When I do the following query, I get The multi-part identifier "fa.InternalUserID" could not be bound.

--TST Group
SELECT        A.AuditID, 
A.FileID, 
A.Description, 
A.UserID, 
IU.FirstName + ' ' + IU.LastName AS UserName, 
FM.FileNumber, 
SWITCHOFFSET(CONVERT(datetimeoffset, A.Date),'-05:00') AS 'LocalDateTime', 
CONVERT(VARCHAR(10), A.Date, 101) AS 'Date', 
CONVERT(VARCHAR(10), A.Date, 14) AS 'UnadjustedTime',
COUNT(FA.FileActionsID) AS ActionCount
FROM            FileMain fm
INNER JOIN InternalUser AS IU ON fa.InternalUserID = IU.InternalUserID 
JOIN FileActions FA on FA.FileID = FM.FileID
LEFT OUTER JOIN Audit AS A ON A.FileID = FM.FileID
WHERE        (FM.OfficeID = 1) 
AND (A.Description = 'File Opened' 
OR A.Description = 'File Closed') 
AND (A.Date >= GETDATE() - 2) 
AND (IU.InternalUserID IN 
    (
--ID's go here
    )
)
ORDER BY UserName, A.AuditID

Here are the original two queries I am combining:

--TST Group
SELECT        A.AuditID, 
A.FileID, 
A.Description, 
A.UserID, 
IU.FirstName + ' ' + IU.LastName AS UserName, 
FM.FileNumber, 
SWITCHOFFSET(CONVERT(datetimeoffset, A.Date),'-05:00') AS 'LocalDateTime', 
CONVERT(VARCHAR(10), A.Date, 101) AS 'Date', 
CONVERT(VARCHAR(10), A.Date, 14) AS 'UnadjustedTime',
COUNT(FA.FileActionsID) AS ActionCount
FROM            Audit AS A 
INNER JOIN InternalUser AS IU ON A.UserID = IU.InternalUserID 
LEFT OUTER JOIN FileMain AS FM ON A.FileID = FM.FileID
WHERE        (FM.OfficeID = 1) 
AND (A.Description = 'File Opened' 
OR A.Description = 'File Closed') 
AND (A.Date >= GETDATE() - 2) 
AND (IU.InternalUserID IN 
    (
--ID's Go here
    )
)
ORDER BY UserName, A.AuditID

and

SELECT        IU.FirstName AS NAME, 
COUNT(FA.FileActionsID) AS ActionCount
FROM            FileActions AS FA 
INNER JOIN InternalUser AS IU ON FA.ReceivedUserID = IU.InternalUserID
WHERE        (FA.ReceivedDate > GETDATE() - 0) 
AND (FA.ReceivedUserID IN   (
--ID's go here
    )
)
GROUP BY IU.FirstName

Upvotes: 0

Views: 5983

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

You have your joins in the wrong order. Currently you are trying to join InternalUser and FileActions when you've only mentioned FileMain and InternalUser (in that order) - you can't specify a condition against a table that hasn't been introduced to the join yet:

FROM            FileMain fm
INNER JOIN InternalUser AS IU ON fa.InternalUserID = IU.InternalUserID 
JOIN FileActions FA on FA.FileID = FM.FileID
LEFT OUTER JOIN Audit AS A ON A.FileID = FM.FileID

Should be (with obligatory schema prefixes added):

FROM dbo.FileMain fm
INNER JOIN dbo.FileActions FA on FA.FileID = FM.FileID
INNER JOIN dbo.InternalUser AS IU ON FA.InternalUserID = IU.InternalUserID 
LEFT OUTER JOIN dbo.Audit AS A ON A.FileID = FM.FileID

Upvotes: 4

Related Questions