Skip to Content
author's profile photo
Former Member

Grouping records by part of field in OpenSQL

Hi,

Is the subject possible at all in abap? For example in MS-SQL syntax I can use functions like:

select max(<i><field1></i>)

from <i><table></i>

group by <b>substring</b>(<i><field1></i>)

or

select max(<i><datefield1></i>)

from <i><table></i>

group by <b>year</b>(<i><datefield1></i>)

Is there a simple way to do this in abap or do I just need to create loop and group manually?

regards,

erki

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • author's profile photo
    Former Member
    Posted on May 18, 2004 at 08:47 AM

    Hi,

    If i understood ur question correctly, then u can do the following in order to achieve your requirement.

    There is a concept of dynamic text in open sql using which u can pass any thing dynamically for a open sql query...

    below iam sending a document copied from a SAP standard help, it may be useful for you(but some of the options works only in 4.7 only)

    Effect

    In all Open SQL statements you can specify the table name dynamically at runtime in an ABAP variable. You can also specify the <b>SELECTclause, the FROMclause, the WHEREclause, the GROUP BYclause, the HAVINGclause</b>, <b>the ORDER BYclause and the SETclause (partially)</b> dynamically in an ABAP variable. The name of the variable that contains the source code must be put in parentheses. There must be no blanks between the variable name and the parentheses.

    <b>Variant 1

    ... (field)</b>

    Effect

    The source code is specified in a field field of the type C.

    Example

    Deleting all lines in a table:

    PARAMETERS tabname(80) TYPE C.

    DELETE FROM (tabname).

    <b>Variant 2

    ... (string)</b>

    Effect

    The source code is specified in a string string.

    Example

    Displaying the flight connections after entering airports of departure and arrival:

    PARAMETERS: p_from TYPE SPFLI-CITYFROM, p_to TYPE SPFLI-CITYTO.

    DATA: where_clause TYPE STRING,

    carr TYPE spfli-carrid,

    conn TYPE spfli-connid.

    CONCATENATE 'CITYFROM = ''' p_from ''' AND CITYTO = ''' p_to ''''

    INTO where_clause.

    SELECT carrid connid FROM spfli

    INTO (carr, conn)

    WHERE (where_clause).

    WRITE: / carr, conn.

    ENDSELECT.

    <b>Variant 3

    ... (itab).</b>

    Effect

    The source code is specified in an internal table itab with line type C.

    Example

    Displaying the number of flight connections after entering airline carrier, flight number, and flight date:

    PARAMETERS: p_carrid TYPE sbook-carrid,

    p_connid TYPE sbook-connid,

    p_fldate TYPE sbook-fldate.

    TYPES: t_src(80) TYPE C.

    DATA: where_tab TYPE TABLE OF t_src, line TYPE t_src.

    CONCATENATE 'CARRID = ''' p_carrid '''' INTO line.

    APPEND line TO where_tab.

    APPEND 'AND' TO where_tab.

    CONCATENATE 'CONNID = ''' p_connid '''' INTO line.

    APPEND line TO where_tab.

    APPEND 'AND' TO where_tab.

    CONCATENATE 'FLDATE = ''' p_fldate '''' INTO line.

    APPEND line TO where_tab.

    SELECT count(*) FROM sbook WHERE (where_tab).

    WRITE: / sy-dbcnt.

    <b>Variant 4

    ... (stringtab)</b>

    Effect

    The source code is specified in an internal table stringtab with line type STRING.

    Example

    Displaying the flight data for all flights with available seats (after entering the airline carrier and flight number):

    PARAMETERS: p_carrid TYPE sflight-carrid,

    p_connid TYPE sflight-connid.

    DATA: s TYPE STRING,

    stringtab TYPE TABLE OF STRING,

    date TYPE sflight-fldate.

    CONCATENATE ' CARRID = ''' p_carrid ''' AND' INTO s.

    APPEND s TO stringtab.

    CONCATENATE ' CONNID = ''' p_connid '''' INTO s.

    APPEND s TO stringtab.

    SELECT fldate FROM sflight INTO date

    WHERE (stringtab) AND seatsocc < sflight~seatsmax.

    WRITE : / date.

    ENDSELECT.

    Note

    If the source code is specified in an internal table with a header, the source code is normally taken from the table body. This is only not the case if the table name is specified dynamically and the dynamic FROMclause is used. The table name or source code of the clause is then taken from the table header.

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Erki,

      it's possible in abap.

      here's a very simple example:

      REPORT ZTEST_sum.

      tables bsid.

      select-options: bukrs for bsid-bukrs,

      kunnr for bsid-kunnr.

      data : begin of tab occurs 0,

      kunnr type bsid-kunnr,

      gjahr type bsid-gjahr,

      amount type bsid-wrbtr,

      end of tab.

      start-of-selection.

      SELECT kunnr gjahr sum( dmbtr ) FROM BSID into table tab

      WHERE BUKRS in bukrs

      AND KUNNR in kunnr

      AND SHKZG = 'S'

      group by kunnr gjahr.

      loop at tab.

      write: / tab-kunnr, tab-gjahr, tab-amount.

      endloop.

      grx Andreas

  • author's profile photo
    Former Member
    Posted on May 20, 2004 at 08:00 AM

    I know how group by works in opensql but my problem is that my table does not have year field to group by but only date field. Is it possible to group by a year using date field?

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      I know doesn't exist an OpenSQL statement for your request.

      You need to do it manualy, and I think you already know how...

      Dany 😊