Wildcards in GROUP BY clause in OpenSQL?

user_1385123 0
Viewed 377

I have a select similar to the one below:

SELECT DISTINCT
  SCARR~CARRID,
  SCARR~CARRNAME,
  MIN( SPFLI~DISTANCE ) AS MIN_DISTANCE
FROM SCARR JOIN SPFLI ON SPFLI~CARRid = SCARR~CARRid
GROUP BY 
  SCARR~CARRID, 
  SCARR~CARRNAME
INTO TABLE @DATA(result).

In the real case, these are other tables and I have many more fields in both SELECT and GROUP BY.

Can I simplify the GROUP BY to not have to write again all the fields that are in the SELECT clause from the table SCARR?

I know other options are to use MIN for all the fields of table SCARR except its ID, or not GROUP BY and instead remove duplicates after the select, but I was trying to do something like GROUP BY scarr~*

opensql abap
3 Answers
user_16386260

Here is one possible workaround using dynamic approach: 

DATA: lcl_struc  TYPE REF TO cl_abap_structdescr,
      lt_grouped TYPE TABLE OF sflight.

lcl_struc ?= cl_abap_typedescr=>describe_by_name( 'SFLIGHT' ).

DATA(group_by) = REDUCE string( INIT line TYPE char1024 FOR <field> IN lcl_struc->get_components( ) NEXT line = COND #( WHEN line <> space THEN line && `, ` &&  `SFLIGHT~` && <field>-name  ELSE line && `SFLIGHT~` && <field>-name ) ).

 SELECT (group_by)
   FROM scarr  LEFT OUTER JOIN spfli
     ON spfli~carrid = scarr~carrid
   LEFT OUTER JOIN sflight
     ON sflight~carrid = spfli~carrid
    AND sflight~connid = spfli~connid
   LEFT OUTER JOIN sairport
     ON sairport~id = spfli~airpfrom
  WHERE scarr~carrid = 'AA'
  GROUP BY (group_by)
   INTO CORRESPONDING FIELDS OF TABLE @lt_grouped.

Pay attention to several limitations here:

  1. In the above sample I group only by SFLIGHT fields, because when specifying wildcarded fields together with single (SELECT spfli~*, sflight~carrid) the inline result table will have not single fields but sub-structures, so it's more sophisticated for processing or requires explicit declaration. Keep this in mind if you need group by joined tables.
  2. As it stated in help chapter provided by Florian, dynamic group clause requires all-or-nothing:

The columns after SELECT must then be specified either solely as arguments of aggregate functions or only directly. If not, this would raise a handleable exception CX_SY_OPEN_SQL_DB. Invalid syntax raises a handleable exception from the class CX_SY_DYNAMIC_OSQL_ERROR.

So it may be useless in your particular case if you need one-field aggregation, but just in case I put it here.

user_9114190

No is not possible in plain OpenSQL.

You can create a new CDS view for aggregate SPFLI, the view return for each CARRID the minimum distance:

define view Z_CDS_SPFLI_MIN as select from SPFLI
{
  carrid, 
  min( distance ) as min_distance
}
group by carrid

And then you can modify your query like this, without use group-by:

select SCARR~*, Z_CDS_SPFLI_MIN~min_distance
  from SCARR
     inner join Z_CDS_SPFLI_MIN
       on SCARR~carrid = Z_CDS_SPFLI_MIN~carrid.
user_46894760
Related