none
Could not Bound error RRS feed

  • Question

  • Getting the following error in my sql query.

    The multi-part identifier "wfl.contact_id_performer" could not be bound.

    The multi-part identifier "wfl.contact_id_delegate" could not be bound.

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

    In the sql below.  How do I correct this?

    SELECT distinct
    lcomp.lcomp_code AS 'Legal Company',
    bsk_1.orga_node AS 'Plant',
    -- xoa.site_code 'Plant'
    toico._cobje_object_en AS 'Cost Object',
    tuc1.contact_lastname+'  '+tuc1.contact_firstname 'Next Approver',
    tu2.login_name 'Next Approver EID',
    tuc1.contact_email 'Next Approver Email Address',
    tuc2.contact_lastname+'  '+tuc2.contact_firstname 'Delegated To',
    cast(cce2._sap_cost_center as nvarchar(20))+'-'+cce2._cce_cdescription_$$ AS 'Cost Center',
    _network._network_aufnr AS 'Network Num',
    _network._descrptn_ktext AS 'Network Desc',
    _network._proj_defn AS 'Project Def',
    _network._proj_descrptn AS 'Project Desc',
    _asset._asset_num AS 'Asset Num',
    _asset._descrptn_text AS 'Asset Desc',
    _activity._activity_vornr AS 'Activity',
    _intorder._order_aufnr AS 'Internal Order',
    _intorder._descrptn_ktext AS 'Internal Order Desc',
    _network._order_type AS 'IO Type',
    alloc_1.acc_code AS 'Account',
    tsspg._purchgrp_label 'Buyer EID',
    contact.contact_firstname+','+contact.contact_lastname AS 'Buyer',
    tsspg._purchgrp_cd 'Buyer Code',
    sup.sup_code AS 'Supplier Number',
    sup.sup_name_en AS 'Supplier Name',
    contact_1.contact_firstname+' '+contact_1.contact_lastname  AS 'Supplier Contact',

    --(case when pitem.item_code is not null then 'Y'
    --else 'N' end) as 'Catalog Item -Y/N',

    oitem.oitem_sup_ref AS 'Punchout Supplier Item Ref',

    bsk_1.basket_from_punchout as 'Is a Punchout Requisition?',
     

    FORMAT(ord.created ,'MM/dd/yyyy', 'en-us')AS 'PO Date',
    ord.ord_code_calculated AS 'PO Number',
    oitem.oitem_label AS 'PO Item Description',
    COALESCE(ord_status.status_label_!$, ord.status_code) AS 'PO Status',
    cast(sssfa.sssfa_code as nvarchar(20))+'-'+sssfa.sssfa_label_$$ AS 'Category',
    SUBSTRING(RIGHT((sssfa.sssfa_code+'-'+sssfa.sssfa_label_en),5),1,4) AS 'Material Group',
    --sssfa._comm_pfc as 'Material Group',
    ctr.ctr_code_calculated +'-'+ctr.ctr_label_$$ AS 'Contract',
    ord.unit_code_currency AS 'Currency',
    FORMAT(oitem.oitem_delivery_date,'MM/dd/yyyy', 'en-us') AS 'Delivery Date',
    ord.payterm_code+'-'+paytrm._own_explanation_of_term_of_payment_$$ AS 'Payment Terms',
    bsk_1.bsk_code_calculated AS 'Req Number',
    oitem.oitem_seq AS 'Req Line Number',
    oitem.oitem_quantity AS 'Req QTY',
    contact_2.contact_firstname+' '+contact_2.contact_lastname AS 'Requester',
    FORMAT(bsk_1.created,'MM/dd/yyyy', 'en-us') AS 'Req Date',
    COALESCE(bsk_1_status.status_label_!$, bsk_1.status_code) AS 'Req Status',
    bsk_1.basket_label_en As 'Req Description',
    oitem.oitem_seq AS 'PO Line Item',
    oitem.oitem_price*oitem.oitem_quantity AS 'Total Value in USD',

    --COALESCE(deliv_status.status_label_!$, deliv.status_code) AS 'GR Status',
    oitem.oitem_quantity AS 'PO Qty',
    oitem.unit_code AS 'UoM',
    cast(oitem.oitem_price_entry as decimal(10,0)) AS 'Price',
    cast(oitem.oitem_price_entry * oitem.oitem_quantity as decimal(10,0)) AS 'PO Amount',
    x.Received_Qty  as 'GR Qty',
    (case when oitem.oitem_delivery_free = 'false' then 'N'
     else 'Y' end) as 'Final Delivery -Y/N',
    sup1.sup_code+'-'+sup1.sup_name_en AS 'OA Partner',
    sup2.sup_code+'-'+sup2.sup_name_en AS 'PI Partner',
    sup3.sup_code+'-'+sup3.sup_name_en AS 'GS Partner'

    FROM t_ord_order AS [ord]

    LEFT JOIN t_usr_contact AS [contact] ON [contact].contact_id = [ord]._contact_id_buyer
    LEFT JOIN t_sup_supplier AS [sup] ON [sup].sup_id = [ord]._sup_id_sap

    LEFT JOIN t_sup_supplier AS [sup1] ON [sup1].sup_id = [ord]._sup_id_oa
    LEFT JOIN t_sup_supplier AS [sup2] ON [sup2].sup_id = [ord]._sup_id_pi
    LEFT JOIN t_sup_supplier AS [sup3] ON [sup3].sup_id = [ord]._sup_id_gs

    --LEFT JOIN x_orga_all xoa ON tob.orga_id=xoa.orga_id



    LEFT JOIN t_ord_basket AS [bsk] ON [bsk].ord_id_previous = [ord].ord_id
    LEFT JOIN t_ord_item AS [oitem] ON [oitem].ord_id = [ord].ord_id
    LEFT JOIN t_ord_allocation AS [alloc] ON [alloc].oitem_id = [oitem].oitem_id
    LEFT JOIN t_ord_cost_center AS [cce2] ON [cce2].cce_code = [alloc].cce_code
    LEFT JOIN t_ord_basket AS [bsk_1] ON [bsk_1].basket_id = [ord].basket_id
    LEFT JOIN t_usr_contact AS [contact_2] ON [contact_2].contact_id = [ord]._contact_id_req_ref
    LEFT JOIN t_usr_contact AS [contact_1] ON [contact_1].contact_id = [ord].contact_id_supplier
    LEFT JOIN t_bas_legal_company AS [lcomp] ON [lcomp].lcomp_id = [ord].lcomp_id
    LEFT JOIN x_orga_all AS [xorga] ON [xorga].orga_node = [bsk_1].orga_node AND [xorga].orga_level = [bsk_1].orga_level
    Left JOIN t_usr_login tul on [contact_2].contact_id=tul.contact_id
    INNER JOIN t_usr_contact tuc1 on wfl.contact_id_performer=tuc1.contact_id
    INNER JOIN t_usr_contact tuc2 on wfl.contact_id_delegate=tuc2.contact_id
    LEFT JOIN t_ord_payment_term AS [paytrm] ON [paytrm].payterm_code = [ord].payterm_code
    LEFT JOIN t_bas_status AS ord_status ON [ord_status].tdesc_name = 't_ord_order' AND [ord_status].status_code = ord.status_code
    LEFT JOIN t_ctr_contract AS [ctr] ON [ctr].ctr_id = [oitem].ctr_id
    LEFT JOIN x_fam_all AS [xfam] ON [xfam].fam_level = [oitem].fam_level AND [xfam].fam_node = [oitem].fam_node
    LEFT JOIN t_buy_sub_sub_sub_family AS [sssfa] ON [sssfa].sssfa_id = [xfam].sssfa_id
    LEFT JOIN t_bas_status AS bsk_1_status ON [bsk_1_status].tdesc_name = 't_ord_basket' AND [bsk_1_status].status_code = bsk_1.status_code
    LEFT JOIN t_ord_cost_center AS [cce] ON [cce].cce_code = [bsk].cce_code
    LEFT JOIN t_ord_allocation AS [alloc_1] ON alloc_1.oitem_id = [oitem].oitem_id
    LEFT JOIN v_ord_delivery_item AS [delitm] ON [delitm].ord_id = [oitem].ord_id
    LEFT JOIN v_ord_delivery_item AS [delitm1] ON [delitm1].oitem_id = [oitem].oitem_id
    LEFT JOIN t_ord_delivery AS [deliv] ON [deliv].deliv_id = [delitm].deliv_id

    LEFT JOIN t_bas_status AS deliv_status ON [deliv_status].tdesc_name = 't_ord_delivery' AND [deliv_status].status_code = deliv.status_code

    LEFT JOIN t_ord_item_cost_objects_  tsco on oitem.oitem_id=tsco._oitem_id
    LEFT JOIN t_req_cost_objects_ toico on tsco._cobje_code=toico._cobje_code

    --left join t_pdt_product as product on product.pdt_id = oitem.item_id


    LEFT JOIN t_sup_sap_purchasing_group_ tsspg on bsk_1._purchgrp_id=tsspg._purchgrp_id

    LEFT JOIN t_ord_asset_ AS [_asset] ON [_asset]._asset_id = [alloc]._asset_id
    LEFT JOIN t_ord_network_ AS [_network] ON [_network]._network_id = [alloc]._network_id
    LEFT JOIN t_ord_activity_ AS [_activity] ON [_activity]._activity_id = [alloc]._activity_id
    LEFT JOIN t_ord_internal_order_ AS [_intorder] ON [_intorder]._intorder_id = [alloc]._intorder_id_one

    left JOIN (select distinct sum(ditem.ditem_quantity) as "Received_Qty",ditem.ord_id ,ditem.oitem_id
    from v_ord_delivery_item AS ditem 
    LEFT JOIN t_ord_item AS [oitem] ON [oitem].ord_id = ditem.ord_id  
    left JOIN t_ord_item AS [oitem1] ON [oitem1].oitem_id = ditem.oitem_id
    INNER JOIN t_ord_order AS [ord] ON [ord].ord_id = oitem.ord_id
    LEFT JOIN t_ord_order AS [ord1] ON [ord1].ord_id = ditem.ord_id
    group by ditem.ord_id,ditem.oitem_id,oitem.oitem_seq) x on ([ord].ord_id=x.ord_id and x.oitem_id = oitem.oitem_id)

    where

    (ord.status_code not in ('cam','can','del'))
    and 
    deliv.status_code != 'del'
    --and ord.ord_id = '62259'
    --and  
    --bsk_1.basket_from_punchout = 'False' 
    and
    (@startdate is null or @enddate is null or (ord.created between @startdate and @enddate))
    and
    (@compcode is null or [lcomp].lcomp_id in (select my_value from dbo.fGetSplitTable(1, @compcode, ',')))
    and(@org is null or [xorga].orga_x_id in (select my_value from dbo.fGetSplitTable(1, @org, ',')))
    and(@requester is null or tul.login_name in (select my_value from dbo.fGetSplitTable(1, @requester, ',')))
    and(@po is null or  [ord].ord_id in (select my_value from dbo.fGetSplitTable(1, @po, ',')))
    and(@status is null or ord_status.status_code in  (select my_value from dbo.fGetSplitTable(1, @status, ',')))
    and(@buy is null or [contact].contact_id in  (select my_value from dbo.fGetSplitTable(1, @buy, ',')))
    and(@sup is null or [contact_1].contact_id in  (select my_value from dbo.fGetSplitTable(1, @sup, ',')))
    and(@cat is null or [sssfa].sssfa_id in  (select substring(my_value,7,15) from dbo.fGetSplitTable(1, @cat, ',')))
    --and(@mg is null or [sssfa].sssfa_id  in (select substring(my_value,7,15) from dbo.fGetSplitTable(1, @mg, ',')))
    --and(@item=0 in (select my_value from dbo.fGetSplitTable(1, @item, ','))) 
      --OR
    --(@item =1)

      
    --and(@item is null or 'Catalog Item -Y/N' in  (select my_value from dbo.fGetSplitTable(1, @item, ',')))

    Wednesday, January 22, 2020 1:49 PM

Answers

  • For the columns you mentioned - wfl.contact_id_performer and wfl.contact_id_delegate, you have used alias as wfl. 

    But I can't find any any table which has this wfl alias. Please find the table which has these columns and give wfl alias or use alias which is already given.


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Wednesday, January 22, 2020 2:07 PM