WHERE variable = ( subquery ) in OpenSQL

Question

I'm trying to retrieve rows from a table where a subquery matches an variable. However, it seems as if the WHERE clause only lets me compare fields of the selected tables against a constant, variable or subquery.

I would expect to write something like this:

DATA(lv_expected_lines) = 5.
SELECT partner contract_account
INTO TABLE lt_bp_ca
FROM table1 AS tab1
WHERE lv_expected_lines = (
    SELECT COUNT(*)
    FROM table2
    WHERE partner          = tab1~partner
    AND   contract_account = tab1~contract_account ).

But obviously this select treats my local variable as a field name and it gives me the error "Unknown column name "lv_expected_lines" until runtime, you cannot specify a field list."

But in standard SQL this is perfectly possible:

SELECT PARTNER, CONTRACT_ACCOUNT
FROM TABLE1 AS TAB1
WHERE 5 = (
    SELECT COUNT(*)
    FROM TABLE2
    WHERE PARTNER          = TAB1.PARTNER
    AND   CONTRACT_ACCOUNT = TAB1.CONTRACT_ACCOUNT );

So how can I replicate this logic in RSQL / Open SQL?

If there's no way I'll probably just write native SQL and be done with it.

Nice question. Frankly said I have never seen a query in which the left hand side in WHERE condition would be a constant either in OpenSQL or in standard SQL. I think it is not possible in OpenSQL, however it might be possible to write the query differently and achieve the same result. Using native SQL in ABAP is never a good option. Let me give it some thought... — Jagger, Feb 20 '15 at 08:01

score 2 · Answer 1 · answered Feb 23 '15 at 15:00

The program below might lead you to an Open SQL solution. It uses the SAP demo tables to determines the plane types that are used on a specific number of flights.

REPORT zgertest_sub_query.

DATA: lt_planetypes TYPE STANDARD TABLE OF s_planetpp.

PARAMETERS: p_numf TYPE i DEFAULT 62.

START-OF-SELECTION.

  SELECT planetype
         INTO TABLE lt_planetypes
         FROM  sflight
       GROUP BY planetype
       HAVING COUNT( * ) EQ p_numf.

  LOOP AT lt_planetypes INTO DATA(planetype).
    WRITE: / planetype.
  ENDLOOP.

It only works if you don't need to read fields from TAB1. If you do you will have to gather these with other selects while looping at your results.

This can work in most cases but not all, +1. I'd considered a solution with `GROUP BY` but the problem is that that doesn't work very well when the scenario includes objects from table A with 0 entries in table B. For instance getting all planetypes with less than 3 planes in SFLIGHT becomes more difficult. In my case I think it would have led to subpar select performance where coding the logic in ABAP would have been quicker. — Lilienthal, Feb 24 '15 at 09:34

score 1 · Answer 2 · answered Apr 10 '20 at 14:48

For those dudes who found this question in 2020 I report that this construction is supported since ABAP 7.50. No workarounds are needed:

SELECT kunnr, vkorg
FROM vbak AS v
WHERE 5 = ( SELECT COUNT(*)
              FROM vbap
             WHERE kunnr = v~kunnr
               AND vkorg = v~vkorg )
 INTO TABLE @DATA(customers).

This select all customers who made 5 sales orders within some sales organization.

score -1 · Answer 3 · answered Feb 20 '15 at 23:13

In ABAP there is no way to do the query as in NATIVE SQL. I would advice not to use NATIVE SQL, instead give a try to SELECT/ENDSELECT statement.

DATA: ls_table1 type table1,
      lt_table1 type table of table1,
      lv_count  type i.

SELECT PARTNER, CONTRACT_ACCOUNT
INTO ls_table1
FROM TABLE1.

      SELECT COUNT(*)
        INTO lv_count
        FROM TABLE2
       WHERE PARTNER          = TAB1.PARTNER
        AND  CONTRACT_ACCOUNT = TAB1.CONTRACT_ACCOUNT.

  CHECK lv_count EQ 5.
  APPEND ls_table1 TO lt_table1.

ENDSELECT

Here you append to ls_table1 only those rows where count is equals to 5 in selection of table2.

Hope it helps.

WHERE variable = ( subquery ) in OpenSQL

3 Answers3