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, Mar 06 '20 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, Mar 06 '20 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, Mar 06 '20 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, Mar 06 '20 at 10:55
To return earliest and latest use the `FIRST_VALUE` and `LAST_VALUE` functions. What you have now executes a separate `SELECT TOP 1` query for every single outer row. With the analytic functions, SQL Server could calculate all values in a single pass — Panagiotis Kanavos, Mar 06 '20 at 10:55
Thanks for the heads up, I changed my where clause to use 'IN' however, `FIRST_VALUE` returns - `Msg 195, Level 15, State 10, Line 3 'FIRST_VALUE' is not a recognized built-in function name.`. I have also included snippets of my database tables, does this make the question a little clearer? Or do I need to include more samples from the other tables? — nopassport1, Mar 06 '20 at 11:29
@nopassport1 Error is correct since you are trying to retrieve data in a variable, you cannot get the select result. — Atk, Mar 06 '20 at 11:30
@Atk I know the error is correct, I didn't say it wasn't. I was just asking if there is any way to get the result of said select into a variable so I can use it within my swift statement. If it's not possible, what are my alternatives? — nopassport1, Mar 06 '20 at 11:31
You are counting after `group by`. According to your data and columns in the `group by` section (AFRU.ISDD, AFRU.ISDZ, AFRU.IEDD, AFRU.IEDZ) you have only 3 unique rows. — Serkan Arslan, Mar 06 '20 at 11:33
@nopassport1 adding a variable isn't going to fix an incorrect query. The query will still be incorrect. What you try to do though is simply impossible - assing *and* use the assigned variable in the same SELECT clause. *NOTHING* says the *first* clause will run before the second. Or that it will run separately. Queries are set-based operations, not loops — Panagiotis Kanavos, Mar 06 '20 at 11:35
@nopassport1 in fact, the CASE is essentially a constant as it would use the variable value when the query started. The rest is simply not allowed because it would make no sense - which value would this store and which would it retrieve from the variable? — Panagiotis Kanavos, Mar 06 '20 at 11:37
`the 'number of confirmations' is 3 but the table actually contains 6 records` why? The query is *grouping* and filtering stuff. The `count()` returns the *actual* number of records returned for those join, filter conditions *per group*. Without knowing what the tables contain, it's impossible to say why some rows were filtered out — Panagiotis Kanavos, Mar 06 '20 at 11:39
@MarcGuillot the OP is asking why the result is too small, not too large — Panagiotis Kanavos, Mar 06 '20 at 11:39
@MarcGuillot it probably *is* simple though - a wrong expectation because the other rows are filtered out by the JOINs — Panagiotis Kanavos, Mar 06 '20 at 11:41

score 2 · Accepted Answer · answered Mar 06 '20 at 11:44

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

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. — nopassport1, Mar 06 '20 at 11:54

Set a variable dynamically within a SELECT statement

1 Answers1