I have an SQL database with several SAP data tables and an SQL statement which looks like this:
SELECT DISTINCT
AFKO.PLNBEZ AS 'Material',
MAKT.MAKTX AS 'Material Number',
AFKO.AUFNR AS 'Order',
AFVC.VORNR AS 'Operation Number',
CRTX.KTEXT AS 'Operation Text',
AFVV.VGW01 AS 'Estimated Hours 1',
AFVV.VGW02 AS 'Estimated Hours 2',
AFVV.VGW03 AS 'Estimated Hours 3',
AFVV.VGW04 AS 'Estimated Hours 4',
AFVV.VGW05 AS 'Estimated Hours 5',
AFVV.VGW06 AS 'Estimated Hours 6',
AFVV.ISM01 AS 'Actual Hours 1',
AFVV.ISM02 AS 'Actual Hours 2',
AFVV.ISM03 AS 'Actual Hours 3',
AFVV.ISM04 AS 'Actual Hours 4',
AFVV.ISM05 AS 'Actual Hours 5',
AFVV.ISM06 AS 'Actual Hours 6',
(SELECT TOP 1
AFRU.ISDD
FROM AFRU
WHERE AUFNR = AFKO.AUFNR
AND RUECK = AFVC.RUECK
ORDER BY ISDD ASC
) AS 'Op Actual Start Date',
(SELECT TOP 1
AFRU.ISDZ
FROM AFRU
WHERE AUFNR = AFKO.AUFNR
AND RUECK = AFVC.RUECK
ORDER BY ISDZ ASC
) AS 'Op Actual Start Time',
(SELECT TOP 1
AFRU.IEDD
FROM AFRU
WHERE AUFNR = AFKO.AUFNR
AND RUECK = AFVC.RUECK
ORDER BY IEDD DESC
) AS 'Op Actual Finish Date',
(SELECT TOP 1
AFRU.IEDZ
FROM AFRU
WHERE AUFNR = AFKO.AUFNR
AND RUECK = AFVC.RUECK
ORDER BY IEDD DESC
) AS 'Op Actual Finish Time',
AFVC.RUECK AS 'Confirmation Number',
AFVC.ARBID AS 'OBJID',
AFKO.GSTRI AS 'Order Actual Start Date',
AFKO.GETRI AS 'Order Confirmed Finish Date',
COUNT(AFRU.RUECK) AS "No. of Confirmations",
CASE
WHEN COUNT(AFRU.RUECK) = 0 THEN 'Confirmed on mass'
WHEN COUNT(AFRU.RUECK) = 1 THEN 'Auto Confirmation'
ELSE 'User clocked on & off'
END AS Accuracy
FROM AFKO
INNER JOIN afvc ON afvc.AUFPL = AFKO.AUFPL
LEFT OUTER JOIN AFRU afru.rueck = afvc.rueck
INNER JOIN MAKT ON AFKO.PLNBEZ = MAKT.MATNR
INNER JOIN CRHD ON crhd.OBJID = afvc.ARBID
INNER JOIN CRTX ON AFVC.ARBID = CRTX.OBJID
INNER JOIN AFVV ON AFVC.AUFPL = AFVV.AUFPL
AND AFVC.APLZL = AFVV.APLZL
INNER JOIN AUFK ON AFKO.AUFNR = AUFK.AUFNR
WHERE AUFK.WERKS = 1000
AND (crhd.ARBPL LIKE 'BSCREBAR'
OR crhd.ARBPL LIKE 'BSCFISET'
OR crhd.ARBPL LIKE 'BSCWDSP'
OR crhd.ARBPL LIKE 'BSCPRSET'
OR crhd.ARBPL LIKE 'BSCCAST'
OR crhd.ARBPL LIKE 'BSCDEMLD' )
GROUP BY AFKO.PLNBEZ, MAKT.MAKTX, AFKO.AUFNR, AFVC.VORNR, CRTX.KTEXT, AFVV.VGW01, AFVV.VGW02, AFVV.VGW03, AFVV.VGW04, AFVV.VGW05, AFVV.VGW06,
AFVV.ISM01, AFVV.ISM02, AFVV.ISM03, AFVV.ISM04, AFVV.ISM05, AFVV.ISM06, AFRU.ISDD, AFRU.ISDZ, AFRU.IEDD, AFRU.IEDZ, AFVC.RUECK, AFVC.ARBID, AFKO.GSTRI, AFKO.GETRI
;
The issue is that my COUNT(AFRU.RUECK) AS "No. of Confirmations", returns the incorrect value, I believe it's something to do with one of my joins but I'm not sure.
Regardless, I changed the select statement to this:
(SELECT COUNT (*) FROM AFRU WHERE RUECK = AFVC.RUECK) AS 'No. of Confirmations',
CASE
WHEN (SELECT COUNT (*) FROM AFRU WHERE RUECK = AFVC.RUECK) = 0 THEN 'Confirmed on mass'
WHEN (SELECT COUNT (*) FROM AFRU WHERE RUECK = AFVC.RUECK) = 1 THEN 'Auto Confirmation'
ELSE 'User clocked on & off'
END AS 'Accuracy'
Which works perfectly, exactly what I wanted. However this isn't the most efficient way of selecting the data. The statement takes approximately 10 minutes to run because of the change.
So I attempted to change it from the above, to this:
@confs = (SELECT COUNT (*) FROM AFRU WHERE RUECK = AFVC.RUECK),
@confs AS 'No. of Confirmations',
CASE
WHEN (@confs) = 0 THEN 'Confirmed on mass'
WHEN (@confs) = 1 THEN 'Auto Confirmation'
ELSE 'User clocked on & off'
END AS 'Accuracy'
To eliminate the additional SELECTs, using a variable which I declared above the main SELECT with - DECLARE @confs int;.
However, I am facing an error message, saying:
Msg 141, Level 15, State 1, Line 3
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
How can I get around this? Is it even possible to get around this?
Every example I've seen for declaring variables in SQL excludes dynamic where clauses. I specifically need to reference another table (AFRU) to get the amount of records for a particular confirmation number (RUECK) - which comes from a different table (AFVC), as part of my main select statement.
EDIT:
Based on the SQL code above (before I made any changes), this is a sample of my complete output:
+------------------+-----------------+---------+------------------+-----------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------------+----------------------+-----------------------+-----------------------+---------------------+----------+-------------------------+-----------------------------+----------------------+-----------------------+
| Material | Material Number | Order | Operation Number | Operation Text | Estimated Hours 1 | Estimated Hours 2 | Estimated Hours 3 | Estimated Hours 4 | Estimated Hours 5 | Estimated Hours 6 | Actual Hours 1 | Actual Hours 2 | Actual Hours 3 | Actual Hours 4 | Actual Hours 5 | Actual Hours 6 | Op Actual Start Date | Op Actual Start Time | Op Actual Finish Date | Op Actual Finish Time | Confirmation Number | OBJID | Order Actual Start Date | Order Confirmed Finish Date | No. of Confirmations | Accuracy |
+------------------+-----------------+---------+------------------+-----------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------------+----------------------+-----------------------+-----------------------+---------------------+----------+-------------------------+-----------------------------+----------------------+-----------------------+
| 1900A-D14MSB-385 | Solid plank | 1713023 | 60 | BSC Casting | 0 | 0 | 2.132 | 0 | 0 | 0 | 0 | 0 | 2.132 | 0 | 0 | 0 | 20200302 | 100959 | 20200302 | 121124 | 7566152 | 10000385 | 20200226 | 20200303 | 3 | User clocked on & off |
+------------------+-----------------+---------+------------------+-----------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------------+----------------------+-----------------------+-----------------------+---------------------+----------+-------------------------+-----------------------------+----------------------+-----------------------+
| 1900A-D14MSB-406 | Solid plank | 1713025 | 60 | BSC Casting | 0 | 0 | 2.132 | 0 | 0 | 0 | 0 | 0 | 2.132 | 0 | 0 | 0 | 20200226 | 210329 | 20200226 | 210329 | 7566124 | 10000385 | 20200226 | 20200227 | 1 | Auto Confirmation |
+------------------+-----------------+---------+------------------+-----------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------------+----------------------+-----------------------+-----------------------+---------------------+----------+-------------------------+-----------------------------+----------------------+-----------------------+
| 1900A-D14MSB-414 | Solid plank | 1713026 | 40 | BSC Primary Set | 2.132 | 0 | 0 | 0 | 0 | 0 | 0.19 | 0 | 0 | 0 | 0 | 0 | 20200227 | 142442 | 20200227 | 152927 | 7566106 | 10000383 | 20200227 | 20200303 | 2 | User clocked on & off |
+------------------+-----------------+---------+------------------+-----------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------------+----------------------+-----------------------+-----------------------+---------------------+----------+-------------------------+-----------------------------+----------------------+-----------------------+
| 1900A-D14MSB-436 | Solid plank | 1713028 | 60 | BSC Casting | 0 | 0 | 0 | 2.132 | 0 | 0 | 0 | 0 | 2.132 | 0 | 0 | 0 | 20200224 | 142546 | 20200224 | 154025 | 7556163 | 10000385 | 20200221 | 20200225 | 2 | User clocked on & off |
+------------------+-----------------+---------+------------------+-----------------+-------------------+-------------------+-------------------+-------------------+-------------------+-------------------+----------------+----------------+----------------+----------------+----------------+----------------+----------------------+----------------------+-----------------------+-----------------------+---------------------+----------+-------------------------+-----------------------------+----------------------+-----------------------+
My AFRU table looks like this (for the confirmation number 0007566152):
In my above example, the 'number of confirmations' is 3 but the table actually contains 6 records, which means the value of 3 is incorrect, it should actually say 6.
+---------+----------+--------+---------+-------+----------+--------+----------+--------+-------+--------+-------+---------+-------+---------------+---------------+--+--+--+--+--+--+--+--+--+--+--+
| RUECK | ERSDA | ERZET | ERNAM | WERKS | ISDD | ISDZ | IEDD | IEDZ | AUERU | AUFPL | APLZL | AUFNR | VORNR | fwk_LineageID | fwk_VersionID | | | | | | | | | | | |
+---------+----------+--------+---------+-------+----------+--------+----------+--------+-------+--------+-------+---------+-------+---------------+---------------+--+--+--+--+--+--+--+--+--+--+--+
| 7566152 | 20200302 | 124517 | DHAWLEY | 1000 | 20200302 | 100959 | 20200302 | 124517 | X | 717464 | 19 | 1713023 | 60 | 2873485 | 4 | | | | | | | | | | | |
+---------+----------+--------+---------+-------+----------+--------+----------+--------+-------+--------+-------+---------+-------+---------------+---------------+--+--+--+--+--+--+--+--+--+--+--+
| 7566152 | 20200302 | 121124 | DHAWLEY | 1000 | 20200302 | 100959 | 20200302 | 121124 | | 717464 | 19 | 1713023 | 60 | 2873485 | 4 | | | | | | | | | | | |
+---------+----------+--------+---------+-------+----------+--------+----------+--------+-------+--------+-------+---------+-------+---------------+---------------+--+--+--+--+--+--+--+--+--+--+--+
| 7566152 | 20200302 | 124517 | DHAWLEY | 1000 | 20200302 | 100959 | 20200302 | 124517 | X | 717464 | 19 | 1713023 | 60 | 2873485 | 4 | | | | | | | | | | | |
+---------+----------+--------+---------+-------+----------+--------+----------+--------+-------+--------+-------+---------+-------+---------------+---------------+--+--+--+--+--+--+--+--+--+--+--+
| 7566152 | 20200302 | 102224 | DHAWLEY | 1000 | 20200302 | 100959 | 20200302 | 102224 | | 717464 | 19 | 1713023 | 60 | 2873485 | 4 | | | | | | | | | | | |
+---------+----------+--------+---------+-------+----------+--------+----------+--------+-------+--------+-------+---------+-------+---------------+---------------+--+--+--+--+--+--+--+--+--+--+--+
| 7566152 | 20200302 | 124517 | DHAWLEY | 1000 | 20200302 | 100959 | 20200302 | 124517 | X | 717464 | 19 | 1713023 | 60 | 2873485 | 4 | | | | | | | | | | | |
+---------+----------+--------+---------+-------+----------+--------+----------+--------+-------+--------+-------+---------+-------+---------------+---------------+--+--+--+--+--+--+--+--+--+--+--+
| 7566152 | 20200302 | 102224 | DHAWLEY | 1000 | 20200302 | 100959 | 20200302 | 102224 | | 717464 | 19 | 1713023 | 60 | 2873485 | 4 | | | | | | | | | | | |
+---------+----------+--------+---------+-------+----------+--------+----------+--------+-------+--------+-------+---------+-------+---------------+---------------+--+--+--+--+--+--+--+--+--+--+--+
In my top result, I expect to see the value of 6, not 3 for that particular record.
you can use outer apply instead of left join. And you don't also need group by
SELECT
....,
AFKO.GSTRI AS 'Order Actual Start Date',
AFKO.GETRI AS 'Order Confirmed Finish Date',
T.[No. of Confirmations],
T.Accuracy
FROM AFKO
INNER JOIN afvc ON afvc.AUFPL = AFKO.AUFPL
INNER JOIN MAKT ON AFKO.PLNBEZ = MAKT.MATNR
INNER JOIN CRHD ON crhd.OBJID = afvc.ARBID
INNER JOIN CRTX ON AFVC.ARBID = CRTX.OBJID
INNER JOIN AFVV ON AFVC.AUFPL = AFVV.AUFPL
AND AFVC.APLZL = AFVV.APLZL
INNER JOIN AUFK ON AFKO.AUFNR = AUFK.AUFNR
OUTER APPLY ( SELECT
COUNT(A.RUECK) AS "No. of Confirmations",
CASE
WHEN COUNT(A.RUECK) = 0 THEN 'Confirmed on mass'
WHEN COUNT(A.RUECK) = 1 THEN 'Auto Confirmation'
ELSE 'User clocked on & off'
END AS Accuracy
FROM AFRU A WHERE A.RUECK = AFVC.RUECK ) AS T
WHERE AUFK.WERKS = 1000
AND (crhd.ARBPL LIKE 'BSCREBAR'
OR crhd.ARBPL LIKE 'BSCFISET'
OR crhd.ARBPL LIKE 'BSCWDSP'
OR crhd.ARBPL LIKE 'BSCPRSET'
OR crhd.ARBPL LIKE 'BSCCAST'
OR crhd.ARBPL LIKE 'BSCDEMLD' )