Wildcards in GROUP BY clause in OpenSQL?

Question

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~*

Whippoorwill

I converted your theoretical query into minimal, reproducible example, so that people can reproduce and adapt if a solution is possible. – Lauralauraceous

Answer 1

No, the OpenSQL syntax doesn't support this.

Gospodin

Thanks. Well, I see I should be able to create a dynamic select where the SELECT clause and GROUP_BY are generated with the same hardcoded string, but I also don't like this solution too much. – Whippoorwill

@Whippoorwill Every ABAPer does a copy/paste of SELECT columns into the GROUP BY section. Why do you need to do it differently? Remember that your code will be maintained by others, so don't invent "solutions" that people may not be happy with. It's just counter-productive. – Lauralauraceous

Dynamic SQL in ABAP is often just another term for "SQL injection as a feature". So better stick with Sandras recommendation and keep it simple and readable. – Stalker

I'm just exploring options before having a definitive opinion. Thanks for your help and recommendations! – Whippoorwill

Answer 2

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.

Gazette

Answer 3

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.

Squib