CTE Group By not grouping results correctly
-
I have the following CTE that is doing a SUM on the field MERCHANDISE_AMT, seperately from 2 different queries and then I'm joining those results together in the final select. I'm having a problem with the Group By on either the inner queries and/or the final Select statement. I want to be able to group the LINE_NBR and PO_DIST_LINE_NUM together so that I have 2 rows as the final output. I need to have these columns selected though in order to reference them in the Join on the final query.
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 D
INNER JOIN PS_PO_LINE 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 A ON A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.PO_ID = C.PO_ID
INNER JOIN PS_DEPT_TBL E ON E.DEPTID = D.DEPTID AND E.SETID = D.BUSINESS_UNIT_GL
--LEFT OUTER JOIN PS_VOUCHER_LINE F ON F.PO_ID = D.PO_ID AND F.BUSINESS_UNIT_PO = D.BUSINESS_UNIT AND F.LINE_NBR = D.LINE_NBR AND F.SCHED_NBR = D.SCHED_NBR
INNER JOIN PS_ROLEXLATOPR 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.MERCHANDISE_AMT <> 0.00
--AND Threshold > .80
AND D.PO_ID = 'J010000185'
AND D.BUSINESS_UNIT = '50000'
AND E.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_DEPT_TBL 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, E.SETID + '_' + E.DEPTID , H.ROLEUSER_SUPR, D.LINE_NBR, D.SCHED_NBR, D.DISTRIB_LINE_NUM )
--HAVING (SUM(F.MERCHANDISE_AMT) / SUM(D.MERCHANDISE_AMT)) > .80 --, F.MERCHANDISE_AMT, D.MERCHANDISE_AMT --, C.LINE_NBR, D.QTY_PO, D.MERCHANDISE_AMT, D.DEPTID, 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 G
INNER JOIN PS_PO_LINE 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 A ON A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.PO_ID = C.PO_ID
INNER JOIN PS_DEPT_TBL E ON E.DEPTID = G.DEPTID AND E.SETID = G.BUSINESS_UNIT_GL
INNER JOIN PS_ROLEXLATOPR 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 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 DISTINCT * -- 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, NULLIF((G.SUM_MERCH / D.SUM_MERCH),0) AS 'Threshold'
FROM CTE D
LEFT OUTER JOIN CTE2 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,
D.LINE_NBR, D.REQUESTOR, D.SCHED_NBR, D.DISTRIB_LINE_NUM, D.SUM_MERCH, G.BUSINESS_UNIT, G.PO_ID, G.PO_TYPE, G.PO_STATUS, G.PO_DT, G.VENDOR_SETID, G.VENDOR_ID, G.BUYER_ID, G.DEPTID, G.ROLEUSER_SUPR, G.SUM_MERCH,
G.BUSINESS_UNIT_PO, G.LINE_NBR, G.SCHED_NBR, G.PO_DIST_LINE_NUM
Results from entire CTEBUSINESS_UNIT PO_ID PO_TYPE PO_STATUS (No column name) VENDOR_SETID VENDOR_ID BUYER_ID DEPTID ROLEUSER_SUPR Threshold
50000 J010000185 AGR D 2017-05-30 SHARE 90238A EDARPINO 744 MHOPSON 0.557487
50000 J010000185 AGR D 2017-05-30 SHARE 90238A EDARPINO 744 MHOPSON 0.750000
50000 J010000185 AGR D 2017-05-30 SHARE 90238A EDARPINO 744 TVORHIS 0.557487
50000 J010000185 AGR D 2017-05-30 SHARE 90238A EDARPINO 744 TVORHIS 0.750000Results from Inner Select (Alias 'CTE'):
BUSINESS_UNIT PO_ID PO_TYPE PO_STATUS PO_DT VENDOR_SETID VENDOR_ID BUYER_ID DEPTID LINE_NBR SCHED_NBR DISTRIB_LINE_NUM SUM_MERCH REQUESTOR ROLEUSER_SUPR
50000 J010000185 AGR D 2017-05-30 SHARE 90238A EDARPINO 744 1 1 1 39240.000 11000_744 TVORHIS
50000 J010000185 AGR D 2017-05-30 SHARE 90238A EDARPINO 744 2 1 1 7000.000 11000_744 TVORHIS
50000 J010000185 AGR D 2017-05-30 SHARE 90238A EDARPINO 744 1 1 2 26160.000 41000_744 MHOPSON
50000 J010000185 AGR D 2017-05-30 SHARE 90238A EDARPINO 744 2 1 2 7000.000 41000_744 MHOPSONHow can I modify so that I can group on the DISTRIB_LINE_NUM so that it outputs as 2 rows, one for REQUESTOR 11000_744 and the other for 41000_744 ? I feel like this is a simple issue, not sure what I am missing though.
I can provide CREATE TABLE / INSERT scripts if that's helpful, just thought someone might be able to easily see what my flaw is though. -
Looking at your results from (Alias 'CTE') the reason you are not getting one row per requestor is the 'Line_NBR' column.
It doesn't look like you are using that field in your final results, try removing it.
Try applying some formatting to make the SQL easier to read next time.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, E.SETID + '_' + E.DEPTID , H.ROLEUSER_SUPR, D.LINE_NBR, D.SCHED_NBR, D.DISTRIB_LINE_NUM
)
,
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 DISTINCT * -- 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, NULLIF((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,
D.LINE_NBR, D.REQUESTOR, D.SCHED_NBR, D.DISTRIB_LINE_NUM, D.SUM_MERCH, G.BUSINESS_UNIT, G.PO_ID, G.PO_TYPE, G.PO_STATUS, G.PO_DT, G.VENDOR_SETID, G.VENDOR_ID, G.BUYER_ID, G.DEPTID, G.ROLEUSER_SUPR, G.SUM_MERCH,
G.BUSINESS_UNIT_PO, G.LINE_NBR, G.SCHED_NBR, G.PO_DIST_LINE_NUM
;-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret. - below86 - Tuesday, March 26, 2019 12:41 PM
If I take out LINE_NBR and/or DISTRIB_LINE_NUM from the CTE inner (top) query, then I can't join those fields in the final select statement (Invalid Column Name 'LINE_NBR').
-
I've modified the query to do the SUM aggregation in the final Select query, and I'm just getting the row data in the inner queries. The data is correct in each of the inner queries (CTE and CTE2), and even the final select with CTE D is fine, but when I join to CTE2 I am missing rows from the final output.
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,
D.MERCHANDISE_AMT, E.SETID + '_' + E.DEPTID AS 'REQUESTOR', H.ROLEUSER_SUPR
FROM PS_PO_LINE_DISTRIB D
INNER JOIN PS_PO_LINE 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 A ON A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.PO_ID = C.PO_ID
INNER JOIN PS_DEPT_TBL E ON E.DEPTID = D.DEPTID AND E.SETID = D.BUSINESS_UNIT_GL
INNER JOIN PS_ROLEXLATOPR 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.MERCHANDISE_AMT <> 0.00
--AND Threshold > .80
AND D.PO_ID = 'J010000185'
AND D.BUSINESS_UNIT = '50000'
AND E.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_DEPT_TBL 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)) ), 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,
G.MERCHANDISE_AMT, G.BUSINESS_UNIT_PO, G.SCHED_NBR, G.LINE_NBR, G.PO_DIST_LINE_NUM
FROM PS_DISTRIB_LINE G
INNER JOIN PS_PO_LINE 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 A ON A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.PO_ID = C.PO_ID
INNER JOIN PS_DEPT_TBL E ON E.DEPTID = G.DEPTID AND E.SETID = G.BUSINESS_UNIT_GL
INNER JOIN PS_ROLEXLATOPR 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 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)) )SELECT D.REQUESTOR, 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, --NULLIF((SUM(G.MERCHANDISE_AMT) / SUM(D.MERCHANDISE_AMT)),0) AS 'Threshold'
G.MERCHANDISE_AMT,
D.MERCHANDISE_AMT
FROM CTE D
LEFT OUTER JOIN CTE2 G ON G.PO_ID = D.PO_ID AND G.BUSINESS_UNIT_PO = D.BUSINESS_UNIT AND G.SCHED_NBR = D.SCHED_NBR AND G.LINE_NBR = D.LINE_NBR AND G.PO_DIST_LINE_NUM = D.DISTRIB_LINE_NUM
GROUP BY D.REQUESTOR, 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_SUPRThis results in the following:
The issue is that D.MERCHANDISE_AMT is being duplicated against the rows from PS_DISTRIB_LINE G. Only the green highlighted rows from PS_PO_LINE_DISTRIB should be getting summed.
-
With out test data it's hard to say, but try this: (ALWAYS post your code within the SQL code tags) I removed a bunch of columns in your group by that I'm assuming aren't needed.
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, E.SETID + '_' + E.DEPTID , H.ROLEUSER_SUPR, D.LINE_NBR, D.SCHED_NBR, D.DISTRIB_LINE_NUM
)
,
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,
NULLIF((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. -
There is no way that we can help you troubleshoot this with actual data to work with. I will say that it looks like you're over-complicating the two CTEs. I think that most of those joins should be in your main query and the CTEs should only deal with the PS_PO_LINE_DISTRIB and the PS_DISTRIB_LINE tables.
Drew
PS: Your table aliases are HORRIBLE. Your aliases should be at least somewhat mnemonic.
For example,
PS_PO_LINE has no C anywhere in the name, so WHY did you choose that instead of POL or even L?
PS_PO_HDR has no A anywhere in the name, although you could argue that it appears in the full name heAder, but WHY did you choose that instead of POH or even H?
PS_DEPT_TBL does have an E, but it makes much more sense to use the FIRST letter in a name, so D would be much more intuitive.
PS_ROLEXLATOPR has no H anywhere in the name, so WHY did you choose that instead of R?J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - below86 - Tuesday, March 26, 2019 1:38 PM
Thanks SSChamption for the help. I'm getting an error message - Invalid column name 'LINE_NBR' . It looks like you commented it out on one of the queries, I tried adding it back but still results in this error. Did you edit the original query or the latest one I posted?
-
I modified the SQL I posted, I didn't want to take the time to reformat your last post.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret. - below86 - Tuesday, March 26, 2019 1:55 PM
Thanks - still seems to be erroring on LINE_NBR, I am now getting 2 errors, from the final Select statement:
Msg 207, Level 16, State 1, Line 132
Invalid column name 'LINE_NBR'.
Msg 207, Level 16, State 1, Line 137
Invalid column name 'LINE_NBR'. - kdrymer - Tuesday, March 26, 2019 2:04 PM
Looks like PeopleSoft Financials. My question is there a column in the table PS_DISTRIB_LINE named LINE_NBR?
- Lynn Pettis - Tuesday, March 26, 2019 2:13 PM
Hi Lynn - Yes, LINE_NBR does exist in the table PS_DISTRIB_LINE (and PS_PO_LINE_DISTRIB). And yes this is PeopleSoft Financials 🙂
-
It looks like because G.LINE_NBR was commented out in CTE2, it can't be used as a join condition in the final Select statement, which is the original issue I had. If I add it back into the Select list of CTE2 then I also have to add it back to the final Select statement's Group By.
-
I reformatted your original code like this:
WITH [CTE]
AS
(
SELECT
[A].[BUSINESS_UNIT]
, [A].[PO_ID]
, [A].[PO_TYPE]
, [A].[PO_STATUS]
, [PO_DT] = (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]
, [SUM_MERCH] = SUM([D].[MERCHANDISE_AMT])
, [REQUESTOR] = [E].[SETID] + '_' + [E].[DEPTID]
, [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]
--LEFT OUTER JOIN PS_VOUCHER_LINE F ON F.PO_ID = D.PO_ID AND F.BUSINESS_UNIT_PO = D.BUSINESS_UNIT AND F.LINE_NBR = D.LINE_NBR AND F.SCHED_NBR = D.SCHED_NBR
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.MERCHANDISE_AMT <> 0.00
--AND Threshold > .80
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]
, [E].[SETID] + '_' + [E].[DEPTID]
, [H].[ROLEUSER_SUPR]
, [D].[LINE_NBR]
, [D].[SCHED_NBR]
, [D].[DISTRIB_LINE_NUM]
)
--HAVING (SUM(F.MERCHANDISE_AMT) / SUM(D.MERCHANDISE_AMT)) > .80 --, F.MERCHANDISE_AMT, D.MERCHANDISE_AMT --, C.LINE_NBR, D.QTY_PO, D.MERCHANDISE_AMT, D.DEPTID, [CTE2]
AS
(
SELECT
[A].[BUSINESS_UNIT]
, [A].[PO_ID]
, [A].[PO_TYPE]
, [A].[PO_STATUS]
, [PO_DT] = (CONVERT(char(10), [A].[PO_DT], 121))
, [A].[VENDOR_SETID]
, [A].[VENDOR_ID]
, [A].[BUYER_ID]
, [G].[DEPTID]
, [H].[ROLEUSER_SUPR]
, [SUM_MERCH] = SUM([G].[MERCHANDISE_AMT])
, [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 DISTINCT
[D].[PO_DT]
, [D].[SUM_MERCH]
, [D].[REQUESTOR]
, [G].[PO_DT]
, [G].[SUM_MERCH] -- 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, NULLIF((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]
, [D].[LINE_NBR]
, [D].[REQUESTOR]
, [D].[SCHED_NBR]
, [D].[DISTRIB_LINE_NUM]
, [D].[SUM_MERCH]
, [G].[BUSINESS_UNIT]
, [G].[PO_ID]
, [G].[PO_TYPE]
, [G].[PO_STATUS]
, [G].[PO_DT]
, [G].[VENDOR_SETID]
, [G].[VENDOR_ID]
, [G].[BUYER_ID]
, [G].[DEPTID]
, [G].[ROLEUSER_SUPR]
, [G].[SUM_MERCH]
, [G].[BUSINESS_UNIT_PO]
, [G].[LINE_NBR]
, [G].[SCHED_NBR]
, [G].[PO_DIST_LINE_NUM];You should do the same thing with the code you are now running and then look at the lines where the errors are indicated. I don't have access to a PeopleSoft database so I can't run any of the code. The error message you are getting are indicating that the column name LINE_NBR is invalid, doesn't exist. Hard to know exactly what line of code it is talking about and is something you need to identify.
- Lynn Pettis - Tuesday, March 26, 2019 2:28 PM
So many square brackets :crazy: IMHO should only be used when necessary. 😀
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret. - below86 - Tuesday, March 26, 2019 3:08 PM
I have starting using them all the time out of self defense. I write enough dynamic SQL that I have had code break because others that think they know what they are doing do things in production that cause my code to break. Just makes it better to just use them rather than wait for things to break.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply