The multi-part identifier could not be bound

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by J.D. Gonzalez » The multi-part identifier could not be bound

The multi-part identifier could not be bound

Rate Topic

Display Mode

Topic Options

Author

Message

carol.he

Posted Monday, October 05, 2009 5:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, November 11, 2010 6:21 AM
Points: 3, Visits: 40

Here is the query. I got the error message saying "The multi-part identifier could not be bound." . I ran both queries separetely, there is no problem. After joining them, it gives the error for each ON field.

SELECT WIP_DTL_HIST.GH_WC_DESC,WIP_DTL_HIST.DATE_WRK,WIP_DTL_HIST.PAYROLL_SFT_WRK,WIP_DTL_HIST.PART_NUM,WIP_DTL_HIST.WKC,
WIP_DTL_HIST.[SUM(GH_DT_HRS)] AS GH_DT_HRS,WIP_DTL_HIST.[SUM(HOURS)] AS UP_HRS, WIP_DTL_HIST.[SUM(PCS)] AS QTY,
WIP_DTL_HIST.[SUM(A_HOURS)] AS A_HOURS
FROM OPENQUERY(T4M,
'SELECT V_WIP_DTL_HIST.GH_WC_DESC,
V_WIP_DTL_HIST.DATE_WRK,
V_WIP_DTL_HIST.PAYROLL_SFT_WRK,
V_WIP_DTL_HIST.PART_NUM,
V_WIP_DTL_HIST.WKC,
SUM (V_WIP_DTL_HIST.GH_DT_HRS),
SUM (V_WIP_DTL_HIST.HOURS),
SUM (V_WIP_DTL_HIST.PCS),
SUM (V_WIP_DTL_HIST.A_HOURS),
AVG(V_WIP_DTL_HIST.CH_UNIT_PER_HRS)
FROM V_WIP_DTL_HIST,V_INV_MSTR
WHERE V_WIP_DTL_HIST.PART_NUM NOT LIKE ''PH%''
AND V_INV_MSTR.USER_DEF_FLD LIKE ''%MSM-S%''
and V_INV_MSTR.PART_TYPE_CODE <> ''0''
and V_WIP_DTL_HIST.WKC <> ''022''
and V_INV_MSTR.PART_NUM = V_WIP_DTL_HIST.PART_NUM
GROUP BY
V_WIP_DTL_HIST.GH_WC_DESC,
V_WIP_DTL_HIST.DATE_WRK,
V_WIP_DTL_HIST.PAYROLL_SFT_WRK,
V_WIP_DTL_HIST.PART_NUM,
V_WIP_DTL_HIST.WKC ') AS WIP_DTL_HIST

LEFT JOIN
(SELECT DAILY_SCRAP.SFT_DATE, DAILY_SCRAP.SFT_NUM,DAILY_SCRAP.PART_NUM, DAILY_SCRAP.WC_NUM,
DAILY_SCRAP.[SUM(QTY_SCRAP)],DAILY_SCRAP.INV_LOC, DAILY_SCRAP.[SUM(SCRAP_TOT_COST)]
FROM OPENQUERY(T4M,
'SELECT
V_DAILY_SCRAP.SFT_DATE,
V_DAILY_SCRAP.SFT_NUM,
V_DAILY_SCRAP.PART_NUM,
SUM (V_DAILY_SCRAP.QTY_SCRAP),
V_DAILY_SCRAP.INV_LOC,
V_RTG_DTL.WC_NUM,
SUM (V_DAILY_SCRAP.SCRAP_TOT_COST)
FROM V_RTG_DTL, V_INV_MSTR, V_DAILY_SCRAP
WHERE V_DAILY_SCRAP.PART_NUM NOT LIKE ''PH%''
AND V_DAILY_SCRAP.INV_LOC LIKE ''%S''
AND V_DAILY_SCRAP.PART_NUM = V_INV_MSTR.PART_NUM
AND V_DAILY_SCRAP.PART_NUM = V_RTG_DTL.PART_NUM
GROUP BY
V_DAILY_SCRAP.SFT_DATE,
V_DAILY_SCRAP.SFT_NUM,
V_DAILY_SCRAP.PART_NUM,
V_DAILY_SCRAP.INV_LOC,
V_RTG_DTL.WC_NUM ')) DAILY_SCRAP
ON WIP_DTL_HIST.DATE_WRK =DAILY_SCRAP.SFT_DATE
AND WIP_DTL_HIST.PAYROLL_SFT_WRK=DAILY_SCRAP.SFT_NUM
AND WIP_DTL_HIST.PART_NUM=DAILY_SCARP.PART_NUM
AND WIP_DTL_HIST.WKC=DAILY_SCRAP.WC_NUM

Post #797798

Jeff Moden

Posted Monday, October 05, 2009 7:50 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 32,893, Visits: 26,767

"Must look eye"...

ON WIP_DTL_HIST.DATE_WRK =DAILY_SCRAP.SFT_DATE 
AND WIP_DTL_HIST.PAYROLL_SFT_WRK=DAILY_SCRAP.SFT_NUM
AND WIP_DTL_HIST.PART_NUM=DAILY_SCARP.PART_NUM
AND WIP_DTL_HIST.WKC=DAILY_SCRAP.WC_NUM

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #797891

carol.he

Posted Monday, October 05, 2009 8:04 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, November 11, 2010 6:21 AM
Points: 3, Visits: 40

I changed it to SCRAP. I got the same error message. It complains on each ON field.

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "DAILY_SCRAP.SFT_DATE" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "DAILY_SCRAP.SFT_NUM" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "DAILY_SCRAP.PART_NUM" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "DAILY_SCRAP.WC_NUM" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "DAILY_SCRAP.SUM(QTY_SCRAP)" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "DAILY_SCRAP.INV_LOC" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "DAILY_SCRAP.SUM(SCRAP_TOT_COST)" could not be bound.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'SUM(CH_UNIT_PER_HRS)'.

Post #797910

Bradley Jacques

Posted Monday, October 05, 2009 8:09 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, February 08, 2012 11:14 AM
Points: 95, Visits: 123

What kind of data source are you pulling from using open row?

Post #797913

carol.he

Posted Monday, October 05, 2009 8:15 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, November 11, 2010 6:21 AM
Points: 3, Visits: 40

UNIDATA 7.1. I created a linked server named T4M. There is no problem with connection. Because I ran both queries seperately. It works.

Post #797920

Bradley Jacques

Posted Monday, October 05, 2009 8:57 AM

SSC Journeyman

Group: General Forum Members
Last Login: Wednesday, February 08, 2012 11:14 AM
Points: 95, Visits: 123

It feels like to me there is something jacked up in your aliases. I'm not sure off hand, if you want to review it there's an article at the bottom of the microsoft technet page on OPEN QUERY on how aliases work.

http://technet.microsoft.com/en-us/library/ms188427.aspx

I hope that helps...

Thanks,

Bradley Jacques

Post #797968

Jeff Moden

Posted Monday, October 05, 2009 9:05 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 32,893, Visits: 26,767

Try changing the derived table aliases to something besides actual table names and see if the fixes it.

--Jeff Moden

Post #797975

Jeff Moden

Posted Monday, October 05, 2009 9:07 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 32,893, Visits: 26,767

Jeff Moden (10/5/2009)

Try changing the derived table aliases to something besides actual table names and see if the fixes it.

Never mind... I missed the V_ in the derived tables.

--Jeff Moden

Post #797977

« Prev Topic | Next Topic »

Permissions