Grouping records by part of field in OpenSQL

[Former Member]

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

[Former Member]

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.

[Former Member]

I do not want to create dynamic criteria, I want to sum some amounts by part of field. To clarify, for example, I need to get sum of amount field by year. I do not have separate year field in the table. My problem is in making <group by> sentence with part of field (year from date field). So I need to somehow say:

...

select sum(amount) from table1 group by year(date)

...

only problem is that, the year() or similar functions are not possible in open sql. So is there any other way to achieve same result?

regards,

erki

[andreas_mann3]

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

[Former Member]

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?

[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