从联合中选择JOIN JOIN问题

Question

我正在尝试运行下面的查询，它基本上会从一个两个查询的UNION中提取三个列-我得到的错误是，第1行的多部分标识符H.po_number无法绑定。我知道这是一个JOIN问题，但不确定为什么？查询本身运行得很好，但是当我添加第一个SELECT时，我得到了JOIN问题。这实际上是在一个INSERT语句中，该语句提取前三个值并将其存储在另一个表中以进行错误跟踪。你知道为什么这会破坏它吗？它的格式现在很糟糕-在那个部分上工作(大写和小写，等等)

select H.po_number as [sap po number],
       getdate(),
      'sapnotgps' as [report_source]

from   

    (SELECT * FROM ( SELECT S.team_member_name as [ASSIGNED EMPLOYEE], -- Using NEW employee table
    H.po_type as [PO TYPE],
    G.order_no as [GPS PO #],
    H.po_number as [SAP PO NUMBER],
    CONVERT(VARCHAR(12),H.po_issue_date,101) AS [PO ISSUE DATE],
    M.department      as [DEPARTMENT],
    K.business_unit_desc  as [BU],
    M.description     as [DESCRIPTION],
    M.material        as [MATERIAL],
    H.po_ordered_quantity as [PO QUANTITY],-- Use header ordered qty to negate multi-line po issue (do not SUM!)
    MAX(P.comment) as [PO COMMENT],
    MIN(CONVERT(VARCHAR(12),E.[date],101)) as [FIRST SHOWN ON RPT]


from (
  select order_no, order_status, cst_order_no, status_date,last_conf_date_cst
  from asagdwpdx_prod.dbo.SimoxOrder1
  union all
  select order_no, order_status, cst_order_no, status_date,last_conf_date_cst
  from asagdwpdx_prod.dbo.SimoxOrder2
  union all
  select order_no, order_status, cst_order_no, status_date,last_conf_date_cst
  from asagdwpdx_prod.dbo.SimoxOrder3
  ) G
  join pdx_sap_user.dbo.vw_po_header H
    on G.order_no = H.ahag_number
  join pdx_sap_user.dbo.vw_po_item P
    on H.po_number = P.po_number
  join pdx_sap_user.dbo.vw_mm_material M 
    on P.material = M.material
  join pdx_sap_user.dbo.vw_kd_business_unit K 
    on M.business_unit_code = K.business_unit_code
  join adi_user_maintained.dbo.scm_po_employee_name S 
    on S.po_number = P.po_number
  join adi_user_maintained.dbo.scm_po_error_tracking E 
    on H.po_number = E.po_number 

  where G.order_status <> '90' 
  and not exists (
    select 1
    from pdx_sap_user.dbo.vw_po_item i
    where i.po_number = H.po_number
      and (i.del_indicator <> 'L' or i.del_indicator is null
      and M.business_segment_code not in ('421','420','422','424'))) --exclude adi golf

    group by G.order_no,
             G.last_conf_date_cst,
             H.po_number,
         H.po_issue_date,
             M.material,
             M.[description],
             K.business_unit_desc,
             M.department,
             H.po_created_by,
             S.team_member_name,
             H.po_type,
             H.po_ordered_quantity
       ) AS C

UNION ALL 

    (SELECT 

       S.team_member_name AS [ASSIGNED EMPLOYEE],
       H.po_type AS [PO TYPE],
       G.order_no AS [GPS PO #], 
       H.po_number AS [SAP PO NUMBER],
       CONVERT(VARCHAR(12),H.po_issue_date,101) AS [PO ISSUE DATE],
       M.department AS [DEPARTMENT],
       K.business_unit_desc AS [BU],
       M.[description] AS [DESCRIPTION],
       P.material AS [MATERIAL], 
       P.po_ordered_quantity AS [PO QUANTITY],
       P.comment AS [PO COMMENT],
       MIN(CONVERT(VARCHAR(12),E.[date],101)) AS [FIRST SHOWN ON RPT]

FROM   pdx_sap_user.dbo.vw_po_header H 
JOIN   pdx_sap_user.dbo.vw_po_item P 
ON     H.po_number = P.po_number 
JOIN   pdx_sap_user.dbo.vw_mm_material M
ON     P.material = M.material 
JOIN   pdx_sap_user.dbo.vw_kd_business_unit K
ON     M.business_unit_code = K.business_unit_code 
JOIN   adi_user_maintained.dbo.scm_po_employee_name S 
ON     H.po_number = S.po_number
JOIN   adi_user_maintained.dbo.scm_po_error_tracking E 
ON     H.po_number = E.po_number 
JOIN  
(SELECT order_no, 
        order_status, 
        status_date,
        right(cst_order_no,10) as [cst_order_no_10],
        last_conf_date_cst,
        cst_order_no

FROM asagdwpdx_prod.dbo.SimoxOrder1

UNION ALL

SELECT    order_no, 
          order_status, 
          status_date,
          right(cst_order_no,10) as [cst_order_no_10],
          last_conf_date_cst,
          cst_order_no

FROM asagdwpdx_prod.dbo.SimoxOrder2

UNION ALL 

SELECT    order_no, 
          order_status, 
          status_date,
          right(cst_order_no,10) as [cst_order_no_10],
          last_conf_date_cst,
          cst_order_no

FROM   asagdwpdx_prod.dbo.SimoxOrder3) G 
ON     P.po_number = G.cst_order_no_10

WHERE  H.ahag_number LIKE '0%'

AND    G.order_status <> '90'

AND NOT EXISTS  (
    SELECT 1
    FROM pdx_sap_user.dbo.vw_po_item i
    WHERE i.po_number = H.po_number
      AND (i.del_indicator <> 'L' or i.del_indicator IS NULL --find ONLY PO's where all lines are L
      AND M.business_segment_code NOT IN ('421','420','422','424'))) --exclude adi golf

GROUP BY S.team_member_name,
         H.po_type,
         G.order_no, 
         H.po_number,
         H.po_issue_date,
         G.last_conf_date_cst,
         M.department,
         K.business_unit_desc,
         M.[description],
         P.material, 
         P.po_ordered_quantity,
         P.comment)) AS D;

Answer 1

在最顶层的select子句中，别名h已经“过期”(并被别名“d”替换)。此外，通过子查询使用的所有列别名现在也处于活动状态。所以你的最终选择应该看起来像这样：

SELECT
      GETDATE()
    , 'sapnotgps' AS [report_source]
    , d.[assigned employee]
    , d.[po type]
    , d.[gps po #]
    , d.[sap po number]
    , d.[po issue date]
    , d.[department]
    , d.[bu]
    , d.[description]
    , d.[material]
    , d.[po quantity]
    , d.[po comment]
    , d.[first shown on rpt]

或者不使用别名d：

SELECT
      GETDATE()
    , 'sapnotgps' AS [report_source]
    , [assigned employee]
    , [po type]
    , [gps po #]
    , [sap po number]
    , [po issue date]
    , [department]
    , [bu]
    , [description]
    , [material]
    , [po quantity]
    , [po comment]
    , [first shown on rpt]
FROM (
      SELECT
            *
      FROM (
            SELECT
                  s.team_member_name                         AS [assigned employee]
                 -- Using NEW employee table
                , h.po_type                                  AS [po type]
                , g.order_no                                 AS [gps po #]
                , h.po_number                                AS [sap po number]
                , CONVERT(varchar(12), h.po_issue_date, 101) AS [po issue date]
                , m.department                               AS [department]
                , k.business_unit_desc                       AS [bu]
                , m.description                              AS [description]
                , m.material                                 AS [material]
                , h.po_ordered_quantity                      AS [po quantity]
                -- Use header ordered qty to negate multi-line po issue (do not SUM!)
                , MAX(p.comment)                             AS [po comment]
                , MIN(CONVERT(varchar(12), e.[date], 101))   AS [first shown on rpt]


            FROM (
                  SELECT
                        order_no
                      , order_status
                      , cst_order_no
                      , status_date
                      , last_conf_date_cst
                  FROM asagdwpdx_prod.dbo.SimoxOrder1
                  UNION ALL
                  SELECT
                        order_no
                      , order_status
                      , cst_order_no
                      , status_date
                      , last_conf_date_cst
                  FROM asagdwpdx_prod.dbo.SimoxOrder2
                  UNION ALL
                  SELECT
                        order_no
                      , order_status
                      , cst_order_no
                      , status_date
                      , last_conf_date_cst
                  FROM asagdwpdx_prod.dbo.SimoxOrder3
            ) g
            JOIN pdx_sap_user.dbo.vw_po_header h ON g.order_no = h.ahag_number
            JOIN pdx_sap_user.dbo.vw_po_item p ON h.po_number = p.po_number
            JOIN pdx_sap_user.dbo.vw_mm_material m ON p.material = m.material
            JOIN pdx_sap_user.dbo.vw_kd_business_unit k ON m.business_unit_code = k.business_unit_code
            JOIN adi_user_maintained.dbo.scm_po_employee_name s ON s.po_number = p.po_number
            JOIN adi_user_maintained.dbo.scm_po_error_tracking e ON h.po_number = e.po_number

            WHERE g.order_status <> '90'
            AND NOT EXISTS (
                  SELECT
                        1
                  FROM pdx_sap_user.dbo.vw_po_item i
                  WHERE i.po_number = h.po_number
                  AND (i.del_indicator <> 'L'
                  OR i.del_indicator IS NULL
                  AND m.business_segment_code NOT IN ('421', '420', '422', '424'))
            ) --exclude adi golf

            GROUP BY
                  g.order_no
                , g.last_conf_date_cst
                , h.po_number
                , h.po_issue_date
                , m.material
                , m.[description]
                , k.business_unit_desc
                , m.department
                , h.po_created_by
                , s.team_member_name
                , h.po_type
                , h.po_ordered_quantity
      ) AS c

      UNION ALL

      (SELECT

            s.team_member_name                         AS [assigned employee]
          , h.po_type                                  AS [po type]
          , g.order_no                                 AS [gps po #]
          , h.po_number                                AS [sap po number]
          , CONVERT(varchar(12), h.po_issue_date, 101) AS [po issue date]
          , m.department                               AS [department]
          , k.business_unit_desc                       AS [bu]
          , m.[description]                            AS [description]
          , p.material                                 AS [material]
          , p.po_ordered_quantity                      AS [po quantity]
          , p.comment                                  AS [po comment]
          , MIN(CONVERT(varchar(12), e.[date], 101))   AS [first shown on rpt]

      FROM pdx_sap_user.dbo.vw_po_header h
      JOIN pdx_sap_user.dbo.vw_po_item p ON h.po_number = p.po_number
      JOIN pdx_sap_user.dbo.vw_mm_material m ON p.material = m.material
      JOIN pdx_sap_user.dbo.vw_kd_business_unit k ON m.business_unit_code = k.business_unit_code
      JOIN adi_user_maintained.dbo.scm_po_employee_name s ON h.po_number = s.po_number
      JOIN adi_user_maintained.dbo.scm_po_error_tracking e ON h.po_number = e.po_number
      JOIN (
            SELECT
                  order_no
                , order_status
                , status_date
                , RIGHT(cst_order_no, 10) AS [cst_order_no_10]
                , last_conf_date_cst
                , cst_order_no

            FROM asagdwpdx_prod.dbo.SimoxOrder1

            UNION ALL

            SELECT
                  order_no
                , order_status
                , status_date
                , RIGHT(cst_order_no, 10) AS [cst_order_no_10]
                , last_conf_date_cst
                , cst_order_no

            FROM asagdwpdx_prod.dbo.SimoxOrder2

            UNION ALL

            SELECT
                  order_no
                , order_status
                , status_date
                , RIGHT(cst_order_no, 10) AS [cst_order_no_10]
                , last_conf_date_cst
                , cst_order_no

            FROM asagdwpdx_prod.dbo.SimoxOrder3
      ) g ON p.po_number = g.cst_order_no_10

      WHERE h.ahag_number LIKE '0%'

      AND g.order_status <> '90'

      AND NOT EXISTS (
            SELECT
                  1
            FROM pdx_sap_user.dbo.vw_po_item i
            WHERE i.po_number = h.po_number
            AND (i.del_indicator <> 'L'
            OR i.del_indicator IS NULL --find ONLY PO's where all lines are L
            AND m.business_segment_code NOT IN ('421', '420', '422', '424'))
      ) --exclude adi golf

      GROUP BY
            s.team_member_name
          , h.po_type
          , g.order_no
          , h.po_number
          , h.po_issue_date
          , g.last_conf_date_cst
          , m.department
          , k.business_unit_desc
          , m.[description]
          , p.material
          , p.po_ordered_quantity
          , p.comment)
) AS d;

Answer 2

只需替换

select H.po_number as [sap po number],
       getdate(),
      'sapnotgps' as [report_source]

使用

select D.[SAP PO NUMBER],
       getdate(),
      'sapnotgps' as [report_source]

谢谢。