Set a variable dynamically within a SELECT statement

Question

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.

All those SELECT TOP 1 queries are essentially joins too. This is a bad way to return related rows and extremely slow. The COUNT(AFRU.RUECK) part is the only one that works correctly and would return the correct count based on this GROUP BY. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 6, 2020 at 10:53
What are trying to do? What are the sample data, what result do you get and what is the expected result? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 6, 2020 at 10:54
The reason for doing that is to return the 'earliest' and 'latest' date/time values for particular fields. What would be another way to achieve the same output? I'll update my question with a sample of my current table data, current result and expected result — nopassport1
– nopassport1, Commented Mar 6, 2020 at 10:54
PS crhd.ARBPL LIKE 'BSCREBAR' is just a crhd.ARBPL = 'BSCREBAR' and that entire clause is essentially a verbose way to write crhd.ARBPL IN 'BSCREBAR', 'BSCFISET',...) — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 6, 2020 at 10:55
@MarcGuillot it probably is simple though - a wrong expectation because the other rows are filtered out by the JOINs — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 6, 2020 at 11:41

Serkan Arslan · Accepted Answer · 2020-03-06 11:44:15Z

2

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' )

answered Mar 6, 2020 at 11:44

Serkan Arslan

13.4k4 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nopassport1 Over a year ago

This is perfect. Works like a charm. Thank you for introducing OUTER APPLY to me. I guess the issue was with my LEFT JOIN, and GROUP BY as mentioned in the comments.

Collectives™ on Stack Overflow

Set a variable dynamically within a SELECT statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related