TomEnniss TomEnniss - 1 month ago 5
SQL Question

This query keeps giving me mulit-part bind error

I am working on a software project with quite a large Database. I am required to display some financial data selected from a database.

Essentially In the Table TimeEntryDetails I need to multiply hours*rate and then group it against the people who worked those hours, and then group those people agaist the City they work in. So in the end we will have Melbourne with a total of $9999999 made up of a sum of the people who worked that amount together. The tables are linked in this manner. BusinessUnit (City) -> Resource (Person) -> TimeEntry -> TemEntryDetails.

I need the financial in the last table to be sorted against the first. I thought if I inner join the tables based on the Primary/Foreign keys it would work.

This is my query:

SELECT TOP (1000) BU.bu_name, RE.resource_name,
TED.[tedetail_invoiced_hours]*TED.[tedetail_invoiced_hours_rate] AS TOTAL,TED.[modified] //This is the date modified

FROM [GasLiteTCM].[dbo].[tblTimeEntryDetail], [GasLiteTCM].[dbo].[tblBU] as BU,[GasLiteTCM].[dbo].[tblResource]

INNER JOIN [GasLiteTCM].[dbo].[tblTimeEntryDetail] as TED ON TED.[tedetail_te_id] = TE.[te_id]

INNER JOIN [GasLiteTCM].[dbo].[tblTimeEntry] as TE ON TE.[te_resource_id] = RE.[resource_id]

INNER JOIN [GasLiteTCM].[dbo].[tblResource] as RE ON RE.[resource_businessunit_id] = BU.[bu_id]

WHERE TED.tedetail_invoiced_hours IS NOT NULL AND TED.tedetail_invoiced_hours>0

ORDER BY modified DESC;


This is the error:


Msg 4104, Level 16, State 1, Line 5 The multi-part identifier
"TE.te_id" could not be bound. Msg 4104, Level 16, State 1, Line 7 The
multi-part identifier "RE.resource_id" could not be bound. Msg 4104,
Level 16, State 1, Line 9 The multi-part identifier "BU.bu_id" could
not be bound. Msg 209, Level 16, State 1, Line 13 Ambiguous column
name 'modified'. Msg 4104, Level 16, State 1, Line 21 The multi-part
identifier "TE.te_id" could not be bound. Msg 4104, Level 16, State 1,
Line 23 The multi-part identifier "RE.resource_id" could not be bound.
Msg 4104, Level 16, State 1, Line 25 The multi-part identifier
"BU.bu_id" could not be bound. Msg 209, Level 16, State 1, Line 29
Ambiguous column name 'modified'.


I have looked at many solutions on this forum but cannot find the answer from them. I appreciate any help/advice as I am still learning SQL.

Answer

You have used the Alias name [TE] before it is created that is the reason for error.

   INNER JOIN [GasLiteTCM].[dbo].[tblTimeEntryDetail] AS TED
           ON TED.[tedetail_te_id] = TE.[te_id] -- used before it is created
   INNER JOIN [GasLiteTCM].[dbo].[tblTimeEntry] AS TE -- Created here

Also you have combined Implicit and Explicit Joins that is reason for "RE.resource_id" & "BU.bu_id" error

Here is the correct way

SELECT TOP (1000) BU.bu_name,
                  RE.resource_name,
                  TED.[tedetail_invoiced_hours] * TED.[tedetail_invoiced_hours_rate] AS TOTAL,
                  TED.[modified]
FROM   [GasLiteTCM].[dbo].[tblBU] AS BU
       INNER JOIN [GasLiteTCM].[dbo].[tblResource] AS RE
               ON RE.[resource_businessunit_id] = BU.[bu_id]
       INNER JOIN [GasLiteTCM].[dbo].[tblTimeEntry] AS TE
               ON TE.[te_resource_id] = RE.[resource_id]
       INNER JOIN [GasLiteTCM].[dbo].[tblTimeEntryDetail] AS TED
               ON TED.[tedetail_te_id] = TE.[te_id]
WHERE  TED.tedetail_invoiced_hours IS NOT NULL
       AND TED.tedetail_invoiced_hours > 0
ORDER  BY modified DESC;