Select only one row from a DB Table depending on a column value in ABAP Open SQL

Question

I am looking for a solution where I want to select only one row from a DB Table depending on a column which like a flag.

A sample DB table looks like this:

C1 | C2 | C3
-----------------
A1 | N1 | 

A1 | N2 | X

A1 | N3 |

A2 | N21 | X

A2 | N22 |

where C1 and C2 are key fields. In this example, A1 has 3 entries and one of which has a flag true ('X').

I want to select either the entry with the flag = 'X' or the minimum of C2 value.

Is this possible in ABAP Open SQL? I tried using case statement but does not give me the required result.

EDIT 1:

In the above example: result will be

A1 | N2

A2 | N21

and when the flag is false or empty then:

A1 | N1

A2 | N21

Jagger Jagger · Accepted Answer · 2017-10-04T10:41:03

Of course it is possible. In fact it should not differ too much from the standard SQL.

SELECT *
  FROM <your_table>
  WHERE
    c3 = 'X'
    OR
    NOT EXISTS ( SELECT * FROM <your_table> WHERE c3 = 'X' ) 
      AND ( c2 = ( SELECT MIN( c2 ) FROM <your_table> ) )
  INTO TABLE @DATA(lt_your_table).

Here is a sample report done with table T000.

REPORT yyy.

SELECT *
  FROM t000
  WHERE
    mandt = '101'
    OR
    mandt = ( SELECT MIN( mandt ) FROM t000 )
      AND NOT EXISTS ( SELECT * FROM t000 WHERE mandt = '101' )
  INTO TABLE @DATA(lt_your_table).

LOOP AT lt_your_table ASSIGNING FIELD-SYMBOL(<fs_your_table>).
  WRITE <fs_your_table>-mandt.
ENDLOOP.

EDIT: After your comments the query could look like this.

SELECT mandt, cccoractiv
  FROM t000
  WHERE
    cccopylock = 'X'
UNION
SELECT mandt, MIN( cccoractiv ) AS cccoractiv
  FROM t000
  WHERE
    cccopylock <> 'X'
      AND NOT EXISTS ( SELECT * FROM t000 WHERE cccopylock = 'X' )
    GROUP BY mandt
INTO TABLE @DATA(lt_your_table).

Select only one row from a DB Table depending on a column value in ABAP Open SQL

1 Answers