OpenSQL with comparison between columns

Question

Can I compare columns of a table in OpenSQL?

Currently the code looks like this:

  SELECT SINGLE menge wemng ebeln ebelp
    INTO (eket-menge,
          eket-wemng,
          eket-ebeln,
          eket-ebelp)
    FROM eket
    WHERE ebeln = ekpo-ebeln
      AND ebelp = ekpo-ebelp.

  IF eket-menge NE eket-wemng.

I want to avoid retreiving lines where menge = wemng, but this does not work:

  SELECT menge wemng ebeln ebelp
    INTO (eket-menge,
          eket-wemng,
          eket-ebeln,
          eket-ebelp)
    FROM eket
    WHERE ebeln = ekpo-ebeln
      AND ebelp = ekpo-ebelp
      AND menge <> wemng.

ABAP thinks wemng is supposed to be a variable.

How can I do it?

score 3 · Accepted Answer · answered Sep 10 '18 at 09:01

You should use column selector(~) when you compare two columns in WHERE condition. This is by design.

ABAPDOCU says:

Column selector

Character ~. A column (col) of a database table (dbtab) can be addressed in a SELECT statement by means of dbtab~col. This type of addressing is necessary when multiple database tables are accessed if the name of a column occurs in a number of different database tables or if two columns are compared with each other in a comparison in the WHERE condition.

So, in your case:

SELECT menge wemng ebeln ebelp
    INTO (eket-menge,
          eket-wemng,
          eket-ebeln,
          eket-ebelp)
    FROM eket
    WHERE ebeln = ekpo-ebeln
      AND ebelp = ekpo-ebelp
      AND menge <> eket~wemng.
...

Note that the modern alternative is to use the [**strict SQL**](https://help.sap.com/http.svc/rc/abapdocu_752_index_htm/7.52/en-US/index.htm?file=abenopensql_strict_modes.htm), whose one of its goals was to address this kind of naming issues, i.e. variables need to be prefixed with @, all other names are columns or tables : `SELECT menge FROM eket WHERE ebeln = @ekpo-ebeln AND menge <> wemng INTO @eket-menge` — Sandra Rossi, Sep 10 '18 at 15:50

score 2 · Answer 2 · edited Sep 09 '18 at 14:53

2

Please check below query:

  SELECT menge wemng ebeln ebelp
  INTO (eket-menge,
        eket-wemng,
        eket-ebeln,
        eket-ebelp)
  FROM eket
  WHERE ebeln = ekpo-ebeln
    AND ebelp = ekpo-ebelp
    AND menge <> eket~wemng.

But at this moment you get error:

Incorrect nesting: Before the statement "ENDIF", the control structure introduced by "SELECT" must be concluded with "ENDSELECT".

So you should create some internal table to handle this

  SELECT menge wemng ebeln ebelp
  INTO TABLE lt_eket
  FROM eket
  WHERE ebeln = ekpo-ebeln
    AND ebelp = ekpo-ebelp
    AND menge <> eket~wemng.

or use SELECT SINGLE

  SELECT SINGLE menge wemng ebeln ebelp
  INTO (eket-menge,
        eket-wemng,
        eket-ebeln,
        eket-ebelp)
  FROM eket
  WHERE ebeln = ekpo-ebeln
    AND ebelp = ekpo-ebelp
    AND menge <> eket~wemng.

edited Sep 09 '18 at 14:53

A. Suliman

12,923
5
24
37

answered Sep 09 '18 at 09:22

Gustawson

33
5

1

Actually your first example works too if you put an ENDSELECT. behind it – András Sep 09 '18 at 19:45
Yes that's right but SELECT ENDSELECT is obsolete and SAP recommends to not use this way. – Gustawson Sep 14 '18 at 09:57
I know, I work there. However, if you are memory constrained, it is still the best option. Also the error only happened because you forgot to close with ENDSELECT, not because it is obsolete. – András Sep 14 '18 at 15:00

OpenSQL with comparison between columns

2 Answers2