April 28, 2010 at 5:50 pm
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "tdh.internal_empl_id" could not be bound.
Why is this being generated?
SQL that caused this error:
SELECT tdh.EMPLOYEE_ID, tdh.DISPLAY_NAME, tdh.EFFECTIVE_DT, tdh.EXPIRATION_DT,
tdl.effective_dt, tdl.day_total_hours/60 as day_hours,
ea.internal_supr_id as supv,
dpc.doc_phase_nm as Status,
tdl.evnt_typ_cd as Event,
tdl.actv_cd as Activity,
ISNULL(a.actv_nm,' ') as "Activity Desc",
ISNULL(tdl.func_cd,' ') as "Function code",
ISNULL(f.func_nm,' ') as "Function Desc",
ISNULL(tdl.task_ord_cd,' ') as "task order",
ISNULL(t.task_ord_nm,' ') as "Task Desc",
ISNULL(tdl.day_1_time_tot,' ') as "WK1 Satuday",
ISNULL(tdl.day_2_time_tot,' ') as "WK1 Sunday",
ISNULL(tdl.day_3_time_tot,' ') as "WK1 Monday",
ISNULL(tdl.day_4_time_tot,' ') as "WK1 Tuesday",
ISNULL(tdl.day_5_time_tot,' ') as "WK1 Wednesday",
ISNULL(tdl.day_6_time_tot,' ') as "WK1 Thursday",
ISNULL(tdl.day_7_time_tot,' ') as "WK1 Friday",
ISNULL(tdl.day_8_time_tot,' ') as "WK2 Saturday",
ISNULL(tdl.day_9_time_tot,' ') as "WK2 Sunday",
ISNULL(tdl.day_10_time_tot,' ') as "WK2 Monday",
ISNULL(tdl.day_11_time_tot,' ') as "WK2 Tuesday",
ISNULL(tdl.day_12_time_tot,' ') as "WK2 Wednesday",
ISNULL(tdl.day_13_time_tot,' ') as "WK2 Thursday",
ISNULL(tdl.day_14_time_tot,' ') as "WK2 Friday"
FROM dbo.TIMEI_DOC_HDR tdh, dbo.TIMEI_DOC_LINE tdl
left outer join finuacc.dbo.R_ACTV a ON tdl.actv_cd=a.actv_cd
left outer join finuacc.dbo.R_FUNC f ON tdl.func_cd=f.func_cd
left outer join finuacc.dbo.R_TASK_ORD t ON tdl.task_ord_cd=t.task_ord_cd
left outer join dbo.CVL_DOC_PHASE_CD dpc ON dpc.doc_phase_cd=tdl.doc_phase_cd
left outer join dbo.EMPL_ASGNMT ea ON tdh.internal_empl_id=ea.internal_empl_id
where tdh.doc_dept_cd=1930
and tdh.expiration_dt='01/29/2010'
and tdh.doc_id=tdl.doc_id
and tdh.internal_empl_id=tdl.internal_empl_id
April 28, 2010 at 6:02 pm
Change the following line
FROM dbo.TIMEI_DOC_HDR tdh, dbo.TIMEI_DOC_LINE tdl
To use an explicit join.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 30, 2010 at 6:23 am
Wow. I need to add an example like this into my SQL Saturday JOINS class.
Good catch, Jason.
April 30, 2010 at 10:37 am
Brandie Tarvin (4/30/2010)
Wow. I need to add an example like this into my SQL Saturday JOINS class.Good catch, Jason.
That sounds like a good idea. I think it is something worth talking about in UGs as well.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 1, 2010 at 11:27 am
CirquedeSQLeil (4/28/2010)
Change the following line
FROM dbo.TIMEI_DOC_HDR tdh, dbo.TIMEI_DOC_LINE tdlTo use an explicit join.
Could you please explain why? I mean, why is that line causing an error and what should it be instead? Something SQL Server didn't like about the alias?
May 1, 2010 at 11:48 am
Mike Seattle (5/1/2010)
CirquedeSQLeil (4/28/2010)
Change the following line
FROM dbo.TIMEI_DOC_HDR tdh, dbo.TIMEI_DOC_LINE tdlTo use an explicit join.
Could you please explain why? I mean, why is that line causing an error and what should it be instead? Something SQL Server didn't like about the alias?
If it was the only join in the query it would be fine. However, it is followed by several Outer Joins. Another problem with defining the join as such is the problem of a Cartesian product. Explicitly telling the system to use an Inner join and which fields to join on eases this problem.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 3, 2010 at 4:38 am
CirquedeSQLeil (5/1/2010)
Another problem with defining the join as such is the problem of a Cartesian product. Explicitly telling the system to use an Inner join and which fields to join on eases this problem.
Read his WHERE clause, Jason. He does actually join tdl and tdh there. Unfortunately, since he's using two different join methods, it's hard to find.
EDIT: All of which tells me you've got the wrong column name, which is why you're getting the error. Look at the last line of your code. Check that column name against what's actually in the table.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply