The multi-part identifier could not be bound

  • 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

  • "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


    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.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • 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)'.

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

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

  • 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

  • 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.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • 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.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply