SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The multi-part identifier could not be bound


The multi-part identifier could not be bound

Author
Message
carol.he
carol.he
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)

Group: General Forum Members
Points: 205103 Visits: 41952
"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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
carol.he
carol.he
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 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)'.
Bradley Jacques
Bradley Jacques
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 Visits: 125
What kind of data source are you pulling from using open row?
carol.he
carol.he
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 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.
Bradley Jacques
Bradley Jacques
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 Visits: 125
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)

Group: General Forum Members
Points: 205103 Visits: 41952
Try changing the derived table aliases to something besides actual table names and see if the fixes it.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)

Group: General Forum Members
Points: 205103 Visits: 41952
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

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search