locked
Multi-part identifier in SQL RRS feed

  • Question

  • User639567535 posted

    i tried this query

    SELECT
        customer.custm_no, customer.name,customer.name2,
    
        orderhdr.otype,orderhdr.saleorder,
    
        orderdtl.sitno,orderdtl.qty,
    
        delvdtl.qty,delvdtl.issueqty
    
        material.name,develhdr.nodelv
    
    FROM
        customer
    
        left join orderhdr on customer.custm_no = orderhdr.custm_no
        left join orderdtl on orderhdr.otype = orderdtl.otype
          and        orderhdr.SALEORDER = orderdtl.SALEORDER
        left join delvdtl on orderdtl.SALEORDER = delvdtl.SALEORDER
        AND  orderdtl.sitno = delvdtl.sitno
        left join  develhdr on delvdtl.nodelv = develhdr.nodelv
        left join MATMSTA on   material.MATNO = MATMSTA.MATNO
        left join material on  MATMSTA.MATNO = material.MATNO
        and MATMSTA.MATNO = material.MATNO
        left join  plantcocode on plant.plant = plantcocode.plant
       left join plant on orderdtl.plant = plantcocode.plant
       left join cocode  on plantcocode.cocode = cocode.cocode
    WHERE
        1 = 1
    order by
     orderdtl.saleorder, orderdtl.sitno

    but this shows me

    The multi-part identifier "material .MATNO" could not be bound.

    The multi-part identifier "plant.plant" could not be bound.

    any solution?

    Tuesday, March 29, 2016 5:56 AM

Answers

  • User-62323503 posted

    1. Move Plant join before plantcocode join

    2.  Below join is incorrect. Join of  MATMSTA should be with some other table

      left join MATMSTA on   material.MATNO = MATMSTA.MATNO
        left join material on  MATMSTA.MATNO = material.MATNO

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 29, 2016 6:18 AM
  • User-595703101 posted

    The following FROM clause declaration is wrong.

    Join to material table and plant table should be before join to MATMSTA and plantcocode tables correspondingly.

    left join MATMSTA on   material.MATNO = MATMSTA.MATNO
    left join material on  MATMSTA.MATNO = material.MATNO and MATMSTA.MATNO = material.MATNO
    
    left join  plantcocode on plant.plant = plantcocode.plant
    left join plant on orderdtl.plant = plantcocode.plant

    I realized an insteresting point about the Material table join

    You used "material.MATNO = MATMSTA.MATNO" for tree times, am I wrong. And these two tables are refereced in each other, but not joined from an other table.

    It would cause unexpected results to return, please check it again

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 29, 2016 6:25 AM

All replies

  • User-62323503 posted

    1. Move Plant join before plantcocode join

    2.  Below join is incorrect. Join of  MATMSTA should be with some other table

      left join MATMSTA on   material.MATNO = MATMSTA.MATNO
        left join material on  MATMSTA.MATNO = material.MATNO

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 29, 2016 6:18 AM
  • User-595703101 posted

    The following FROM clause declaration is wrong.

    Join to material table and plant table should be before join to MATMSTA and plantcocode tables correspondingly.

    left join MATMSTA on   material.MATNO = MATMSTA.MATNO
    left join material on  MATMSTA.MATNO = material.MATNO and MATMSTA.MATNO = material.MATNO
    
    left join  plantcocode on plant.plant = plantcocode.plant
    left join plant on orderdtl.plant = plantcocode.plant

    I realized an insteresting point about the Material table join

    You used "material.MATNO = MATMSTA.MATNO" for tree times, am I wrong. And these two tables are refereced in each other, but not joined from an other table.

    It would cause unexpected results to return, please check it again

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 29, 2016 6:25 AM