CTE Group By not grouping results correctly

• below86

  SSChampion

  Points: 11513

  More actions

  March 26, 2019 at 3:18 pm

  #2031835

  I see Lynn, but I'll take my chances for now.
  WITH CTE AS
  (SELECT A.BUSINESS_UNIT, A.PO_ID, A.PO_TYPE, A.PO_STATUS,
   (CONVERT(CHAR(10),A.PO_DT,121)) AS PO_DT,
   A.VENDOR_SETID, A.VENDOR_ID, A.BUYER_ID, D.DEPTID, D.LINE_NBR, D.SCHED_NBR, D.DISTRIB_LINE_NUM,
   SUM(D.MERCHANDISE_AMT) AS SUM_MERCH,
   E.SETID + '_' + E.DEPTID AS REQUESTOR,
   H.ROLEUSER_SUPR
  FROM PS_PO_LINE_DISTRIB AS D
   INNER JOIN PS_PO_LINE AS C
    ON D.BUSINESS_UNIT = C.BUSINESS_UNIT
    AND D.PO_ID = C.PO_ID
    AND C.LINE_NBR = D.LINE_NBR
   INNER JOIN PS_PO_HDR AS A
    ON A.BUSINESS_UNIT = C.BUSINESS_UNIT
    AND A.PO_ID = C.PO_ID
   INNER JOIN PS_DEPT_TBL AS E
    ON E.DEPTID = D.DEPTID
    AND E.SETID = D.BUSINESS_UNIT_GL
   INNER JOIN PS_ROLEXLATOPR AS H
    ON H.ROLEUSER = E.SETID + '_' + E.DEPTID
  WHERE
   A.PO_TYPE IN ('AGR','BO')
   AND A.PO_STATUS IN ('A','D','O')
   AND D.PO_ID = 'J010000185'
   AND D.BUSINESS_UNIT = '50000'
   AND E.EFFDT = (SELECT MAX(A_ED.EFFDT)
        FROM PS_DEPT_TBL AS A_ED
        WHERE E.SETID = A_ED.SETID
          AND E.DEPTID = A_ED.DEPTID
          AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
  GROUP BY A.BUSINESS_UNIT, A.PO_ID, A.PO_TYPE, A.PO_STATUS,
        (CONVERT(CHAR(10),A.PO_DT,121)),
        A.VENDOR_SETID, A.VENDOR_ID, A.BUYER_ID, D.DEPTID, D.LINE_NBR, D.SCHED_NBR, D.DISTRIB_LINE_NUM
        E.SETID + '_' + E.DEPTID, H.ROLEUSER_SUPR
  )
,
CTE2 AS
  (SELECT A.BUSINESS_UNIT, A.PO_ID, A.PO_TYPE, A.PO_STATUS,
   (CONVERT(CHAR(10),A.PO_DT,121)) AS PO_DT,
   A.VENDOR_SETID, A.VENDOR_ID, A.BUYER_ID, G.DEPTID, H.ROLEUSER_SUPR,
   SUM(G.MERCHANDISE_AMT) AS SUM_MERCH,
   G.BUSINESS_UNIT_PO, G.LINE_NBR, G.SCHED_NBR, G.PO_DIST_LINE_NUM
  FROM PS_DISTRIB_LINE AS G
   INNER JOIN PS_PO_LINE AS C
    ON G.BUSINESS_UNIT = C.BUSINESS_UNIT
    AND G.PO_ID = C.PO_ID
    AND C.LINE_NBR = G.LINE_NBR
   INNER JOIN PS_PO_HDR AS A
    ON A.BUSINESS_UNIT = C.BUSINESS_UNIT
    AND A.PO_ID = C.PO_ID
   INNER JOIN PS_DEPT_TBL AS E
    ON E.DEPTID = G.DEPTID
    AND E.SETID = G.BUSINESS_UNIT_GL
   INNER JOIN PS_ROLEXLATOPR AS H
    ON H.ROLEUSER = E.SETID + '_' + E.DEPTID
  WHERE G.BUSINESS_UNIT = '50000'
   AND G.PO_ID = 'J010000185'
   AND E.EFFDT = (SELECT MAX(A_ED.EFFDT)
        FROM PS_DEPT_TBL AS A_ED
        WHERE E.SETID = A_ED.SETID
          AND E.DEPTID = A_ED.DEPTID
          AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
  GROUP BY A.BUSINESS_UNIT, A.PO_ID, A.PO_TYPE, A.PO_STATUS,
        (CONVERT(CHAR(10),A.PO_DT,121)),
        A.VENDOR_SETID, A.VENDOR_ID, A.BUYER_ID, G.DEPTID, H.ROLEUSER_SUPR,
        G.BUSINESS_UNIT_PO, G.LINE_NBR, G.SCHED_NBR, G.PO_DIST_LINE_NUM
        --, E.SETID + '_' + E.DEPTID
  )
SELECT D.BUSINESS_UNIT, D.PO_ID, D.PO_TYPE, D.PO_STATUS,
    (CONVERT(CHAR(10),D.PO_DT,121)),
    D.VENDOR_SETID, D.VENDOR_ID, D.BUYER_ID, D.DEPTID, D.ROLEUSER_SUPR,
    COALESCE((G.SUM_MERCH / D.SUM_MERCH), 0) AS Threshold
FROM CTE AS D
  LEFT OUTER JOIN CTE2 AS G
   ON G.PO_ID = D.PO_ID
   AND G.BUSINESS_UNIT_PO = D.BUSINESS_UNIT
   AND G.LINE_NBR = D.LINE_NBR
   AND G.SCHED_NBR = D.SCHED_NBR
   AND G.PO_DIST_LINE_NUM = D.DISTRIB_LINE_NUM
GROUP BY D.BUSINESS_UNIT, D.PO_ID, D.PO_TYPE, D.PO_STATUS,
    (CONVERT(CHAR(10),D.PO_DT,121)),
    D.VENDOR_SETID, D.VENDOR_ID, D.BUYER_ID, D.DEPTID, D.ROLEUSER_SUPR
;

  -------------------------------------------------------------
  we travel not to escape life but for life not to escape us
  Don't fear failure, fear regret.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 26, 2019 at 3:27 pm

  #2031838

  below86 - Tuesday, March 26, 2019 3:18 PM

  I see Lynn, but I'll take my chances for now.
  WITH CTE AS
  (SELECT A.BUSINESS_UNIT, A.PO_ID, A.PO_TYPE, A.PO_STATUS,
   (CONVERT(CHAR(10),A.PO_DT,121)) AS PO_DT,
   A.VENDOR_SETID, A.VENDOR_ID, A.BUYER_ID, D.DEPTID, D.LINE_NBR, D.SCHED_NBR, D.DISTRIB_LINE_NUM,
   SUM(D.MERCHANDISE_AMT) AS SUM_MERCH,
   E.SETID + '_' + E.DEPTID AS REQUESTOR,
   H.ROLEUSER_SUPR
  FROM PS_PO_LINE_DISTRIB AS D
   INNER JOIN PS_PO_LINE AS C
    ON D.BUSINESS_UNIT = C.BUSINESS_UNIT
    AND D.PO_ID = C.PO_ID
    AND C.LINE_NBR = D.LINE_NBR
   INNER JOIN PS_PO_HDR AS A
    ON A.BUSINESS_UNIT = C.BUSINESS_UNIT
    AND A.PO_ID = C.PO_ID
   INNER JOIN PS_DEPT_TBL AS E
    ON E.DEPTID = D.DEPTID
    AND E.SETID = D.BUSINESS_UNIT_GL
   INNER JOIN PS_ROLEXLATOPR AS H
    ON H.ROLEUSER = E.SETID + '_' + E.DEPTID
  WHERE
   A.PO_TYPE IN ('AGR','BO')
   AND A.PO_STATUS IN ('A','D','O')
   AND D.PO_ID = 'J010000185'
   AND D.BUSINESS_UNIT = '50000'
   AND E.EFFDT = (SELECT MAX(A_ED.EFFDT)
        FROM PS_DEPT_TBL AS A_ED
        WHERE E.SETID = A_ED.SETID
          AND E.DEPTID = A_ED.DEPTID
          AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
  GROUP BY A.BUSINESS_UNIT, A.PO_ID, A.PO_TYPE, A.PO_STATUS,
        (CONVERT(CHAR(10),A.PO_DT,121)),
        A.VENDOR_SETID, A.VENDOR_ID, A.BUYER_ID, D.DEPTID, D.LINE_NBR, D.SCHED_NBR, D.DISTRIB_LINE_NUM
        E.SETID + '_' + E.DEPTID, H.ROLEUSER_SUPR
  )
,
CTE2 AS
  (SELECT A.BUSINESS_UNIT, A.PO_ID, A.PO_TYPE, A.PO_STATUS,
   (CONVERT(CHAR(10),A.PO_DT,121)) AS PO_DT,
   A.VENDOR_SETID, A.VENDOR_ID, A.BUYER_ID, G.DEPTID, H.ROLEUSER_SUPR,
   SUM(G.MERCHANDISE_AMT) AS SUM_MERCH,
   G.BUSINESS_UNIT_PO, G.LINE_NBR, G.SCHED_NBR, G.PO_DIST_LINE_NUM
  FROM PS_DISTRIB_LINE AS G
   INNER JOIN PS_PO_LINE AS C
    ON G.BUSINESS_UNIT = C.BUSINESS_UNIT
    AND G.PO_ID = C.PO_ID
    AND C.LINE_NBR = G.LINE_NBR
   INNER JOIN PS_PO_HDR AS A
    ON A.BUSINESS_UNIT = C.BUSINESS_UNIT
    AND A.PO_ID = C.PO_ID
   INNER JOIN PS_DEPT_TBL AS E
    ON E.DEPTID = G.DEPTID
    AND E.SETID = G.BUSINESS_UNIT_GL
   INNER JOIN PS_ROLEXLATOPR AS H
    ON H.ROLEUSER = E.SETID + '_' + E.DEPTID
  WHERE G.BUSINESS_UNIT = '50000'
   AND G.PO_ID = 'J010000185'
   AND E.EFFDT = (SELECT MAX(A_ED.EFFDT)
        FROM PS_DEPT_TBL AS A_ED
        WHERE E.SETID = A_ED.SETID
          AND E.DEPTID = A_ED.DEPTID
          AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
  GROUP BY A.BUSINESS_UNIT, A.PO_ID, A.PO_TYPE, A.PO_STATUS,
        (CONVERT(CHAR(10),A.PO_DT,121)),
        A.VENDOR_SETID, A.VENDOR_ID, A.BUYER_ID, G.DEPTID, H.ROLEUSER_SUPR,
        G.BUSINESS_UNIT_PO, G.LINE_NBR, G.SCHED_NBR, G.PO_DIST_LINE_NUM
        --, E.SETID + '_' + E.DEPTID
  )
SELECT D.BUSINESS_UNIT, D.PO_ID, D.PO_TYPE, D.PO_STATUS,
    (CONVERT(CHAR(10),D.PO_DT,121)),
    D.VENDOR_SETID, D.VENDOR_ID, D.BUYER_ID, D.DEPTID, D.ROLEUSER_SUPR,
    COALESCE((G.SUM_MERCH / D.SUM_MERCH), 0) AS Threshold
FROM CTE AS D
  LEFT OUTER JOIN CTE2 AS G
   ON G.PO_ID = D.PO_ID
   AND G.BUSINESS_UNIT_PO = D.BUSINESS_UNIT
   AND G.LINE_NBR = D.LINE_NBR
   AND G.SCHED_NBR = D.SCHED_NBR
   AND G.PO_DIST_LINE_NUM = D.DISTRIB_LINE_NUM
GROUP BY D.BUSINESS_UNIT, D.PO_ID, D.PO_TYPE, D.PO_STATUS,
    (CONVERT(CHAR(10),D.PO_DT,121)),
    D.VENDOR_SETID, D.VENDOR_ID, D.BUYER_ID, D.DEPTID, D.ROLEUSER_SUPR
;

  I also believe that each column of a SELECT should be on its own line.  And actually, the schema name should also be specified along with the table names.

• kdrymer

  SSC Veteran

  Points: 262

  More actions

  March 26, 2019 at 4:13 pm

  #2031839

  below86 - Tuesday, March 26, 2019 3:18 PM

  I see Lynn, but I'll take my chances for now.
  WITH CTE AS
  (SELECT A.BUSINESS_UNIT, A.PO_ID, A.PO_TYPE, A.PO_STATUS,
   (CONVERT(CHAR(10),A.PO_DT,121)) AS PO_DT,
   A.VENDOR_SETID, A.VENDOR_ID, A.BUYER_ID, D.DEPTID, D.LINE_NBR, D.SCHED_NBR, D.DISTRIB_LINE_NUM,
   SUM(D.MERCHANDISE_AMT) AS SUM_MERCH,
   E.SETID + '_' + E.DEPTID AS REQUESTOR,
   H.ROLEUSER_SUPR
  FROM PS_PO_LINE_DISTRIB AS D
   INNER JOIN PS_PO_LINE AS C
    ON D.BUSINESS_UNIT = C.BUSINESS_UNIT
    AND D.PO_ID = C.PO_ID
    AND C.LINE_NBR = D.LINE_NBR
   INNER JOIN PS_PO_HDR AS A
    ON A.BUSINESS_UNIT = C.BUSINESS_UNIT
    AND A.PO_ID = C.PO_ID
   INNER JOIN PS_DEPT_TBL AS E
    ON E.DEPTID = D.DEPTID
    AND E.SETID = D.BUSINESS_UNIT_GL
   INNER JOIN PS_ROLEXLATOPR AS H
    ON H.ROLEUSER = E.SETID + '_' + E.DEPTID
  WHERE
   A.PO_TYPE IN ('AGR','BO')
   AND A.PO_STATUS IN ('A','D','O')
   AND D.PO_ID = 'J010000185'
   AND D.BUSINESS_UNIT = '50000'
   AND E.EFFDT = (SELECT MAX(A_ED.EFFDT)
        FROM PS_DEPT_TBL AS A_ED
        WHERE E.SETID = A_ED.SETID
          AND E.DEPTID = A_ED.DEPTID
          AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
  GROUP BY A.BUSINESS_UNIT, A.PO_ID, A.PO_TYPE, A.PO_STATUS,
        (CONVERT(CHAR(10),A.PO_DT,121)),
        A.VENDOR_SETID, A.VENDOR_ID, A.BUYER_ID, D.DEPTID, D.LINE_NBR, D.SCHED_NBR, D.DISTRIB_LINE_NUM
        E.SETID + '_' + E.DEPTID, H.ROLEUSER_SUPR
  )
,
CTE2 AS
  (SELECT A.BUSINESS_UNIT, A.PO_ID, A.PO_TYPE, A.PO_STATUS,
   (CONVERT(CHAR(10),A.PO_DT,121)) AS PO_DT,
   A.VENDOR_SETID, A.VENDOR_ID, A.BUYER_ID, G.DEPTID, H.ROLEUSER_SUPR,
   SUM(G.MERCHANDISE_AMT) AS SUM_MERCH,
   G.BUSINESS_UNIT_PO, G.LINE_NBR, G.SCHED_NBR, G.PO_DIST_LINE_NUM
  FROM PS_DISTRIB_LINE AS G
   INNER JOIN PS_PO_LINE AS C
    ON G.BUSINESS_UNIT = C.BUSINESS_UNIT
    AND G.PO_ID = C.PO_ID
    AND C.LINE_NBR = G.LINE_NBR
   INNER JOIN PS_PO_HDR AS A
    ON A.BUSINESS_UNIT = C.BUSINESS_UNIT
    AND A.PO_ID = C.PO_ID
   INNER JOIN PS_DEPT_TBL AS E
    ON E.DEPTID = G.DEPTID
    AND E.SETID = G.BUSINESS_UNIT_GL
   INNER JOIN PS_ROLEXLATOPR AS H
    ON H.ROLEUSER = E.SETID + '_' + E.DEPTID
  WHERE G.BUSINESS_UNIT = '50000'
   AND G.PO_ID = 'J010000185'
   AND E.EFFDT = (SELECT MAX(A_ED.EFFDT)
        FROM PS_DEPT_TBL AS A_ED
        WHERE E.SETID = A_ED.SETID
          AND E.DEPTID = A_ED.DEPTID
          AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
  GROUP BY A.BUSINESS_UNIT, A.PO_ID, A.PO_TYPE, A.PO_STATUS,
        (CONVERT(CHAR(10),A.PO_DT,121)),
        A.VENDOR_SETID, A.VENDOR_ID, A.BUYER_ID, G.DEPTID, H.ROLEUSER_SUPR,
        G.BUSINESS_UNIT_PO, G.LINE_NBR, G.SCHED_NBR, G.PO_DIST_LINE_NUM
        --, E.SETID + '_' + E.DEPTID
  )
SELECT D.BUSINESS_UNIT, D.PO_ID, D.PO_TYPE, D.PO_STATUS,
    (CONVERT(CHAR(10),D.PO_DT,121)),
    D.VENDOR_SETID, D.VENDOR_ID, D.BUYER_ID, D.DEPTID, D.ROLEUSER_SUPR,
    COALESCE((G.SUM_MERCH / D.SUM_MERCH), 0) AS Threshold
FROM CTE AS D
  LEFT OUTER JOIN CTE2 AS G
   ON G.PO_ID = D.PO_ID
   AND G.BUSINESS_UNIT_PO = D.BUSINESS_UNIT
   AND G.LINE_NBR = D.LINE_NBR
   AND G.SCHED_NBR = D.SCHED_NBR
   AND G.PO_DIST_LINE_NUM = D.DISTRIB_LINE_NUM
GROUP BY D.BUSINESS_UNIT, D.PO_ID, D.PO_TYPE, D.PO_STATUS,
    (CONVERT(CHAR(10),D.PO_DT,121)),
    D.VENDOR_SETID, D.VENDOR_ID, D.BUYER_ID, D.DEPTID, D.ROLEUSER_SUPR
;

  Hi below86 - This is causing the following aggregation errors:

  Msg 8120, Level 16, State 1, Line 138
Column 'CTE2.SUM_MERCH' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 138
Column 'CTE.SUM_MERCH' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 138
Column 'CTE2.SUM_MERCH' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 138
Column 'CTE.SUM_MERCH' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

  Perhaps because in the final select the expression is not identified as an aggregate function at this point, so therefore it's required to be in the Group By ?

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 26, 2019 at 9:51 pm

  #2031851

  kdrymer - Tuesday, March 26, 2019 4:13 PM

  below86 - Tuesday, March 26, 2019 3:18 PM

  I see Lynn, but I'll take my chances for now.
  WITH CTE AS
  (SELECT A.BUSINESS_UNIT, A.PO_ID, A.PO_TYPE, A.PO_STATUS,
   (CONVERT(CHAR(10),A.PO_DT,121)) AS PO_DT,
   A.VENDOR_SETID, A.VENDOR_ID, A.BUYER_ID, D.DEPTID, D.LINE_NBR, D.SCHED_NBR, D.DISTRIB_LINE_NUM,
   SUM(D.MERCHANDISE_AMT) AS SUM_MERCH,
   E.SETID + '_' + E.DEPTID AS REQUESTOR,
   H.ROLEUSER_SUPR
  FROM PS_PO_LINE_DISTRIB AS D
   INNER JOIN PS_PO_LINE AS C
    ON D.BUSINESS_UNIT = C.BUSINESS_UNIT
    AND D.PO_ID = C.PO_ID
    AND C.LINE_NBR = D.LINE_NBR
   INNER JOIN PS_PO_HDR AS A
    ON A.BUSINESS_UNIT = C.BUSINESS_UNIT
    AND A.PO_ID = C.PO_ID
   INNER JOIN PS_DEPT_TBL AS E
    ON E.DEPTID = D.DEPTID
    AND E.SETID = D.BUSINESS_UNIT_GL
   INNER JOIN PS_ROLEXLATOPR AS H
    ON H.ROLEUSER = E.SETID + '_' + E.DEPTID
  WHERE
   A.PO_TYPE IN ('AGR','BO')
   AND A.PO_STATUS IN ('A','D','O')
   AND D.PO_ID = 'J010000185'
   AND D.BUSINESS_UNIT = '50000'
   AND E.EFFDT = (SELECT MAX(A_ED.EFFDT)
        FROM PS_DEPT_TBL AS A_ED
        WHERE E.SETID = A_ED.SETID
          AND E.DEPTID = A_ED.DEPTID
          AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
  GROUP BY A.BUSINESS_UNIT, A.PO_ID, A.PO_TYPE, A.PO_STATUS,
        (CONVERT(CHAR(10),A.PO_DT,121)),
        A.VENDOR_SETID, A.VENDOR_ID, A.BUYER_ID, D.DEPTID, D.LINE_NBR, D.SCHED_NBR, D.DISTRIB_LINE_NUM
        E.SETID + '_' + E.DEPTID, H.ROLEUSER_SUPR
  )
,
CTE2 AS
  (SELECT A.BUSINESS_UNIT, A.PO_ID, A.PO_TYPE, A.PO_STATUS,
   (CONVERT(CHAR(10),A.PO_DT,121)) AS PO_DT,
   A.VENDOR_SETID, A.VENDOR_ID, A.BUYER_ID, G.DEPTID, H.ROLEUSER_SUPR,
   SUM(G.MERCHANDISE_AMT) AS SUM_MERCH,
   G.BUSINESS_UNIT_PO, G.LINE_NBR, G.SCHED_NBR, G.PO_DIST_LINE_NUM
  FROM PS_DISTRIB_LINE AS G
   INNER JOIN PS_PO_LINE AS C
    ON G.BUSINESS_UNIT = C.BUSINESS_UNIT
    AND G.PO_ID = C.PO_ID
    AND C.LINE_NBR = G.LINE_NBR
   INNER JOIN PS_PO_HDR AS A
    ON A.BUSINESS_UNIT = C.BUSINESS_UNIT
    AND A.PO_ID = C.PO_ID
   INNER JOIN PS_DEPT_TBL AS E
    ON E.DEPTID = G.DEPTID
    AND E.SETID = G.BUSINESS_UNIT_GL
   INNER JOIN PS_ROLEXLATOPR AS H
    ON H.ROLEUSER = E.SETID + '_' + E.DEPTID
  WHERE G.BUSINESS_UNIT = '50000'
   AND G.PO_ID = 'J010000185'
   AND E.EFFDT = (SELECT MAX(A_ED.EFFDT)
        FROM PS_DEPT_TBL AS A_ED
        WHERE E.SETID = A_ED.SETID
          AND E.DEPTID = A_ED.DEPTID
          AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
  GROUP BY A.BUSINESS_UNIT, A.PO_ID, A.PO_TYPE, A.PO_STATUS,
        (CONVERT(CHAR(10),A.PO_DT,121)),
        A.VENDOR_SETID, A.VENDOR_ID, A.BUYER_ID, G.DEPTID, H.ROLEUSER_SUPR,
        G.BUSINESS_UNIT_PO, G.LINE_NBR, G.SCHED_NBR, G.PO_DIST_LINE_NUM
        --, E.SETID + '_' + E.DEPTID
  )
SELECT D.BUSINESS_UNIT, D.PO_ID, D.PO_TYPE, D.PO_STATUS,
    (CONVERT(CHAR(10),D.PO_DT,121)),
    D.VENDOR_SETID, D.VENDOR_ID, D.BUYER_ID, D.DEPTID, D.ROLEUSER_SUPR,
    COALESCE((G.SUM_MERCH / D.SUM_MERCH), 0) AS Threshold
FROM CTE AS D
  LEFT OUTER JOIN CTE2 AS G
   ON G.PO_ID = D.PO_ID
   AND G.BUSINESS_UNIT_PO = D.BUSINESS_UNIT
   AND G.LINE_NBR = D.LINE_NBR
   AND G.SCHED_NBR = D.SCHED_NBR
   AND G.PO_DIST_LINE_NUM = D.DISTRIB_LINE_NUM
GROUP BY D.BUSINESS_UNIT, D.PO_ID, D.PO_TYPE, D.PO_STATUS,
    (CONVERT(CHAR(10),D.PO_DT,121)),
    D.VENDOR_SETID, D.VENDOR_ID, D.BUYER_ID, D.DEPTID, D.ROLEUSER_SUPR
;

  Hi below86 - This is causing the following aggregation errors:

  Msg 8120, Level 16, State 1, Line 138
Column 'CTE2.SUM_MERCH' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 138
Column 'CTE.SUM_MERCH' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 138
Column 'CTE2.SUM_MERCH' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 138
Column 'CTE.SUM_MERCH' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

  Perhaps because in the final select the expression is not identified as an aggregate function at this point, so therefore it's required to be in the Group By ?

  Sounds like it.

• below86

  SSChampion

  Points: 11513

  More actions

  March 27, 2019 at 7:41 am

  #2033939

  Lynn Pettis - Tuesday, March 26, 2019 3:27 PM

  I also believe that each column of a SELECT should be on its own line.  And actually, the schema name should also be specified along with the table names.

  I agree with the schema name.  I used to do each column on a line, gets rather lengthy sometimes.  I've adopted the approach of limiting 5 columns per line.  If that column has any conversion/manipulation/sum... then it gets it own line. 
  Consistent formatting is what I try and preach to the younger developers here.

  kdrymer, I'm not sure what you are trying to accomplish, and again no test data, so it's hard for me to know if I'm getting the code correct.  You are doing a group by in the last SQL statement but you are not summing the data.
  Here is one more attempt:
  WITH CTE AS
(SELECT A.BUSINESS_UNIT, A.PO_ID, A.PO_TYPE, A.PO_STATUS,
  (CONVERT(CHAR(10),A.PO_DT,121)) AS PO_DT,
  A.VENDOR_SETID, A.VENDOR_ID, A.BUYER_ID, D.DEPTID, D.LINE_NBR, D.SCHED_NBR, D.DISTRIB_LINE_NUM,
  SUM(D.MERCHANDISE_AMT) AS SUM_MERCH,
  E.SETID + '_' + E.DEPTID AS REQUESTOR,
  H.ROLEUSER_SUPR
FROM PS_PO_LINE_DISTRIB AS D
  INNER JOIN PS_PO_LINE AS C
  ON D.BUSINESS_UNIT = C.BUSINESS_UNIT
  AND D.PO_ID = C.PO_ID
  AND C.LINE_NBR = D.LINE_NBR
  INNER JOIN PS_PO_HDR AS A
  ON A.BUSINESS_UNIT = C.BUSINESS_UNIT
  AND A.PO_ID = C.PO_ID
  INNER JOIN PS_DEPT_TBL AS E
  ON E.DEPTID = D.DEPTID
  AND E.SETID = D.BUSINESS_UNIT_GL
  INNER JOIN PS_ROLEXLATOPR AS H
  ON H.ROLEUSER = E.SETID + '_' + E.DEPTID
WHERE
  A.PO_TYPE IN ('AGR','BO')
  AND A.PO_STATUS IN ('A','D','O')
  AND D.PO_ID = 'J010000185'
  AND D.BUSINESS_UNIT = '50000'
  AND E.EFFDT = (SELECT MAX(A_ED.EFFDT)
   FROM PS_DEPT_TBL AS A_ED
   WHERE E.SETID = A_ED.SETID
    AND E.DEPTID = A_ED.DEPTID
    AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
GROUP BY A.BUSINESS_UNIT, A.PO_ID, A.PO_TYPE, A.PO_STATUS,
   (CONVERT(CHAR(10),A.PO_DT,121)),
   A.VENDOR_SETID, A.VENDOR_ID, A.BUYER_ID, D.DEPTID, D.LINE_NBR, D.SCHED_NBR, D.DISTRIB_LINE_NUM
   E.SETID + '_' + E.DEPTID, H.ROLEUSER_SUPR
)
,
CTE2 AS
(SELECT A.BUSINESS_UNIT, A.PO_ID, A.PO_TYPE, A.PO_STATUS,
  (CONVERT(CHAR(10),A.PO_DT,121)) AS PO_DT,
  A.VENDOR_SETID, A.VENDOR_ID, A.BUYER_ID, G.DEPTID, H.ROLEUSER_SUPR,
  SUM(G.MERCHANDISE_AMT) AS SUM_MERCH,
  G.BUSINESS_UNIT_PO, G.LINE_NBR, G.SCHED_NBR, G.PO_DIST_LINE_NUM
FROM PS_DISTRIB_LINE AS G
  INNER JOIN PS_PO_LINE AS C
  ON G.BUSINESS_UNIT = C.BUSINESS_UNIT
  AND G.PO_ID = C.PO_ID
  AND C.LINE_NBR = G.LINE_NBR
  INNER JOIN PS_PO_HDR AS A
  ON A.BUSINESS_UNIT = C.BUSINESS_UNIT
  AND A.PO_ID = C.PO_ID
  INNER JOIN PS_DEPT_TBL AS E
  ON E.DEPTID = G.DEPTID
  AND E.SETID = G.BUSINESS_UNIT_GL
  INNER JOIN PS_ROLEXLATOPR AS H
  ON H.ROLEUSER = E.SETID + '_' + E.DEPTID
WHERE G.BUSINESS_UNIT = '50000'
  AND G.PO_ID = 'J010000185'
  AND E.EFFDT = (SELECT MAX(A_ED.EFFDT)
   FROM PS_DEPT_TBL AS A_ED
   WHERE E.SETID = A_ED.SETID
    AND E.DEPTID = A_ED.DEPTID
    AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
GROUP BY A.BUSINESS_UNIT, A.PO_ID, A.PO_TYPE, A.PO_STATUS,
   (CONVERT(CHAR(10),A.PO_DT,121)),
   A.VENDOR_SETID, A.VENDOR_ID, A.BUYER_ID, G.DEPTID, H.ROLEUSER_SUPR,
   G.BUSINESS_UNIT_PO, G.LINE_NBR, G.SCHED_NBR, G.PO_DIST_LINE_NUM
   --, E.SETID + '_' + E.DEPTID
)
SELECT D.BUSINESS_UNIT, D.PO_ID, D.PO_TYPE, D.PO_STATUS,
  (CONVERT(CHAR(10),D.PO_DT,121)),
  D.VENDOR_SETID, D.VENDOR_ID, D.BUYER_ID, D.DEPTID, D.ROLEUSER_SUPR,
  SUM(COALESCE(G.SUM_MERCH, 0)) / SUM(COALESCE(D.SUM_MERCH, 0)) AS Threshold
FROM CTE AS D
LEFT OUTER JOIN CTE2 AS G
  ON G.PO_ID = D.PO_ID
  AND G.BUSINESS_UNIT_PO = D.BUSINESS_UNIT
  AND G.LINE_NBR = D.LINE_NBR
  AND G.SCHED_NBR = D.SCHED_NBR
  AND G.PO_DIST_LINE_NUM = D.DISTRIB_LINE_NUM
GROUP BY D.BUSINESS_UNIT, D.PO_ID, D.PO_TYPE, D.PO_STATUS,
  (CONVERT(CHAR(10),D.PO_DT,121)),
  D.VENDOR_SETID, D.VENDOR_ID, D.BUYER_ID, D.DEPTID, D.ROLEUSER_SUPR
;

  -------------------------------------------------------------
  we travel not to escape life but for life not to escape us
  Don't fear failure, fear regret.

• kdrymer

  SSC Veteran

  Points: 262

  More actions

  March 27, 2019 at 1:01 pm

  #2034012

  below86 - Wednesday, March 27, 2019 7:41 AM

  Lynn Pettis - Tuesday, March 26, 2019 3:27 PM

  I also believe that each column of a SELECT should be on its own line.  And actually, the schema name should also be specified along with the table names.

  I agree with the schema name.  I used to do each column on a line, gets rather lengthy sometimes.  I've adopted the approach of limiting 5 columns per line.  If that column has any conversion/manipulation/sum... then it gets it own line. 
  Consistent formatting is what I try and preach to the younger developers here.

  kdrymer, I'm not sure what you are trying to accomplish, and again no test data, so it's hard for me to know if I'm getting the code correct.  You are doing a group by in the last SQL statement but you are not summing the data.
  Here is one more attempt:
  WITH CTE AS
(SELECT A.BUSINESS_UNIT, A.PO_ID, A.PO_TYPE, A.PO_STATUS,
  (CONVERT(CHAR(10),A.PO_DT,121)) AS PO_DT,
  A.VENDOR_SETID, A.VENDOR_ID, A.BUYER_ID, D.DEPTID, D.LINE_NBR, D.SCHED_NBR, D.DISTRIB_LINE_NUM,
  SUM(D.MERCHANDISE_AMT) AS SUM_MERCH,
  E.SETID + '_' + E.DEPTID AS REQUESTOR,
  H.ROLEUSER_SUPR
FROM PS_PO_LINE_DISTRIB AS D
  INNER JOIN PS_PO_LINE AS C
  ON D.BUSINESS_UNIT = C.BUSINESS_UNIT
  AND D.PO_ID = C.PO_ID
  AND C.LINE_NBR = D.LINE_NBR
  INNER JOIN PS_PO_HDR AS A
  ON A.BUSINESS_UNIT = C.BUSINESS_UNIT
  AND A.PO_ID = C.PO_ID
  INNER JOIN PS_DEPT_TBL AS E
  ON E.DEPTID = D.DEPTID
  AND E.SETID = D.BUSINESS_UNIT_GL
  INNER JOIN PS_ROLEXLATOPR AS H
  ON H.ROLEUSER = E.SETID + '_' + E.DEPTID
WHERE
  A.PO_TYPE IN ('AGR','BO')
  AND A.PO_STATUS IN ('A','D','O')
  AND D.PO_ID = 'J010000185'
  AND D.BUSINESS_UNIT = '50000'
  AND E.EFFDT = (SELECT MAX(A_ED.EFFDT)
   FROM PS_DEPT_TBL AS A_ED
   WHERE E.SETID = A_ED.SETID
    AND E.DEPTID = A_ED.DEPTID
    AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
GROUP BY A.BUSINESS_UNIT, A.PO_ID, A.PO_TYPE, A.PO_STATUS,
   (CONVERT(CHAR(10),A.PO_DT,121)),
   A.VENDOR_SETID, A.VENDOR_ID, A.BUYER_ID, D.DEPTID, D.LINE_NBR, D.SCHED_NBR, D.DISTRIB_LINE_NUM
   E.SETID + '_' + E.DEPTID, H.ROLEUSER_SUPR
)
,
CTE2 AS
(SELECT A.BUSINESS_UNIT, A.PO_ID, A.PO_TYPE, A.PO_STATUS,
  (CONVERT(CHAR(10),A.PO_DT,121)) AS PO_DT,
  A.VENDOR_SETID, A.VENDOR_ID, A.BUYER_ID, G.DEPTID, H.ROLEUSER_SUPR,
  SUM(G.MERCHANDISE_AMT) AS SUM_MERCH,
  G.BUSINESS_UNIT_PO, G.LINE_NBR, G.SCHED_NBR, G.PO_DIST_LINE_NUM
FROM PS_DISTRIB_LINE AS G
  INNER JOIN PS_PO_LINE AS C
  ON G.BUSINESS_UNIT = C.BUSINESS_UNIT
  AND G.PO_ID = C.PO_ID
  AND C.LINE_NBR = G.LINE_NBR
  INNER JOIN PS_PO_HDR AS A
  ON A.BUSINESS_UNIT = C.BUSINESS_UNIT
  AND A.PO_ID = C.PO_ID
  INNER JOIN PS_DEPT_TBL AS E
  ON E.DEPTID = G.DEPTID
  AND E.SETID = G.BUSINESS_UNIT_GL
  INNER JOIN PS_ROLEXLATOPR AS H
  ON H.ROLEUSER = E.SETID + '_' + E.DEPTID
WHERE G.BUSINESS_UNIT = '50000'
  AND G.PO_ID = 'J010000185'
  AND E.EFFDT = (SELECT MAX(A_ED.EFFDT)
   FROM PS_DEPT_TBL AS A_ED
   WHERE E.SETID = A_ED.SETID
    AND E.DEPTID = A_ED.DEPTID
    AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),121), 1, 10))
GROUP BY A.BUSINESS_UNIT, A.PO_ID, A.PO_TYPE, A.PO_STATUS,
   (CONVERT(CHAR(10),A.PO_DT,121)),
   A.VENDOR_SETID, A.VENDOR_ID, A.BUYER_ID, G.DEPTID, H.ROLEUSER_SUPR,
   G.BUSINESS_UNIT_PO, G.LINE_NBR, G.SCHED_NBR, G.PO_DIST_LINE_NUM
   --, E.SETID + '_' + E.DEPTID
)
SELECT D.BUSINESS_UNIT, D.PO_ID, D.PO_TYPE, D.PO_STATUS,
  (CONVERT(CHAR(10),D.PO_DT,121)),
  D.VENDOR_SETID, D.VENDOR_ID, D.BUYER_ID, D.DEPTID, D.ROLEUSER_SUPR,
  SUM(COALESCE(G.SUM_MERCH, 0)) / SUM(COALESCE(D.SUM_MERCH, 0)) AS Threshold
FROM CTE AS D
LEFT OUTER JOIN CTE2 AS G
  ON G.PO_ID = D.PO_ID
  AND G.BUSINESS_UNIT_PO = D.BUSINESS_UNIT
  AND G.LINE_NBR = D.LINE_NBR
  AND G.SCHED_NBR = D.SCHED_NBR
  AND G.PO_DIST_LINE_NUM = D.DISTRIB_LINE_NUM
GROUP BY D.BUSINESS_UNIT, D.PO_ID, D.PO_TYPE, D.PO_STATUS,
  (CONVERT(CHAR(10),D.PO_DT,121)),
  D.VENDOR_SETID, D.VENDOR_ID, D.BUYER_ID, D.DEPTID, D.ROLEUSER_SUPR
;

  Works beautifully!!! Think it was an issue with the grouping. Thanks very much!

• below86

  SSChampion

  Points: 11513

  More actions

  March 27, 2019 at 1:43 pm

  #2034019

  kdrymer - Wednesday, March 27, 2019 1:01 PM

  Works beautifully!!! Think it was an issue with the grouping. Thanks very much!

  Your Group BY and the fact you weren't Summing the 'SUM_MERCH' amounts from both CTE's.

  -------------------------------------------------------------
  we travel not to escape life but for life not to escape us
  Don't fear failure, fear regret.