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 · Accepted Answer · 2017-10-05 10:19:53Z

5

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

edited Oct 5, 2017 at 10:19

answered Oct 4, 2017 at 10:41

Jagger

10.6k11 gold badges60 silver badges100 bronze badges

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

6 Comments

qwerty Over a year ago

Hi @Jagger, thanks. But in this case, it returns me two rows => one with flag= 'X' and one wiht min value since it is an OR condition. What I really want is one row at the end. Also, I can do this by ABAP code but I want to use the query for CDS views where it does not understand ABAP code.

Jagger Over a year ago

The first version of the answer did so but I changed it like an hour ago. Right now it returns either/or. I don't know much of CDS views but when I googled it, it came together with ABAP CDS, so I think it is possible to use ABAP there.

qwerty Over a year ago

@Jagger- yes it works well when you have the flag set to true. But when you have the flag set to false for all entries, then it selects only one entry with the min value. What I need in the case of flag being empty is all entries belonging to C1 (key field) having min(C2). I tried to add group by but does not help my cause. Any ideas?

Jagger Over a year ago

@qwerty Please update your question with an example of your expected query results, because it is not clear to me what you mean. When the C3 is empty then you receive all entries from the table that have C2 as minimum. I understand that you want minima for each value of C1, right? So many results grouped by C1 and not those having minimum of C2 for each and every record. This would be mixing two totally different queries together (one with group by and one without). One would be having the same difficulties in standard SQL. Maybe going with UNION would be an option?

qwerty Over a year ago

@Jagger- updated the example result. Yes, that is right. I want the min C2 value for a group of C1. Unfortunately, i only receive one single value which is min (C2) and it completely ignores the C1 group in this case.

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related