Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

The multi-part identifier could not be bound Expand / Collapse
Author
Message
Posted Monday, October 5, 2009 5:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, October 5, 2009 7:50 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:17 AM
Points: 36,800, Visits: 31,261
"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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #797891
Posted Monday, October 5, 2009 8:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, October 5, 2009 8:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, February 8, 2012 11:14 AM
Points: 95, Visits: 123
What kind of data source are you pulling from using open row?

Post #797913
Posted Monday, October 5, 2009 8:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, October 5, 2009 8:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, February 8, 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
Posted Monday, October 5, 2009 9:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:17 AM
Points: 36,800, Visits: 31,261
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #797975
Posted Monday, October 5, 2009 9:07 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:17 AM
Points: 36,800, Visits: 31,261
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #797977
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse