October 5, 2009 at 5:49 am
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
October 5, 2009 at 7:50 am
"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_[highlight]SCARP[/highlight].PART_NUM
AND WIP_DTL_HIST.WKC=DAILY_SCRAP.WC_NUM
--Jeff Moden
Change is inevitable... Change for the better is not.
October 5, 2009 at 8:04 am
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)'.
October 5, 2009 at 8:09 am
What kind of data source are you pulling from using open row?
October 5, 2009 at 8:15 am
UNIDATA 7.1. I created a linked server named T4M. There is no problem with connection. Because I ran both queries seperately. It works.
October 5, 2009 at 8:57 am
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
October 5, 2009 at 9:05 am
Try changing the derived table aliases to something besides actual table names and see if the fixes it.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 5, 2009 at 9:07 am
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
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply