ABAP Open SQL - aggregate functions

[Former Member]

Hi,

Does ABAP Open SQL support aggregate functions? (e.g. SUM, AVG)

When you hit F1 on "select", the ABAP Keyword Documentation opens up. Under "Open SQL" in the documentation is information on "SELECT - aggregate". So the SAP Keyword Documentation states that aggregate functions are possible in Open SQL.

But this guy (who seems amply qualified) says that open SQL in ABAP does not support aggregate functions such as sum:

http://it.toolbox.com/blogs/sap-on-db2/abap-open-sql-limitations-31495

Who is right? If ABAP does support aggregate functions, can I have a complete select statement using SUM and GROUP BY as an example?

I have written a select statement using SUM and GROUP BY but the resulting internal table is empty.

Thanks.

[kesavadas_thekkillath]

Aggregate functions can be used

see link:[Reading Aggregate Data for Columns|http://help.sap.com/saphelp_nw04/helpdata/EN/fc/eb3990358411d1829f0000e829fbfe/content.htm]

Certain Things are not possible in ABAP open sql such as rollback create Etc. I think these are not AGGREGATE functions

But i dont why he mentioned

in open sql you canu2019t use aggregate functions like sum,avg. 

Edited by: Keshu Thekkillam on Sep 3, 2009 6:27 PM

[Former Member]

Hi

Check the program in se38

demo_select_group_by

Try with SUM or AVG instead of MIN or MAX in that code

it works

Edited by: Sonal Patel on Sep 3, 2009 6:34 PM

[Former Member]

Do you know if data type currency (CURR) is considered by the aggregate function SUM to be a numeric field? I'm just not getting any data in my results and I'm pretty confident my syntax is correct:

select zfmusfga_{rfistl zfmusfga}rfund zfmusfga_{refdocnr sum( zfmusfga}hsl ) zfmusfga~budat

into (itab-rfistl, itab-rfund, itab-refdocnr, itab-hsl, itab-budat)

from zfmusfga

where rfistl = fld_fc and

racct = '48020900' and

budat >= p_pdates and

budat <= p_pdatee

group by

zfmusfga_{rfistl zfmusfga}rfund zfmusfga_{refdocnr zfmusfga}budat.

endselect.

[kesavadas_thekkillath]

data:v_netpr type ekpo-netpr.
select sum( NETPR ) from ekpo into v_netpr where ebeln = '3000000023' .
write v_netpr.

This works for me

Might be some problems in your where clause

check leading zeros in your value 48020900 and whether the date conditions matches the entries with your table values

[ThomasZloch]

Not sure what the guy wants. Of course Open SQL cannot include all the bells and whistles of each specific native SQL dialects, because Open SQL is designed to serve all database platforms alike, not only DB2, so it can only include common features. Some common features might have been left out for reasons unknown to me though.

Aggregates are certainly included, here is another helpful link.

http://help.sap.com/abapdocu_70/en/ABAPSELECT_AGGREGATE.htm

Thomas

[Former Member]

Open SQL does support aggregate functions. The article in question was talking about open SQL in th econtext of DB2 for z/OS. I don't know if that makes any difference.

Rob

[SuhaSaha]

Hello Rob,

Just out of curiosity(& of course ignorance) does ABAP support column functions in Open SQL?

I think it doesn't.

BR,

Suhas

[Former Member]

Do you mean something like returning the sum of two columns of a row?

Rob

[SuhaSaha]

Yes,

Or concatinating two columns of a row.

BR,

Suhas

[Former Member]

I don't think you can dum two columns, but you should be able to get a concatenated result by the way you define your work area or internal table (haven't tried it).

Rob

[a142367]

this SQL compiles but gives a runtime error: Can anybody Help:

select
service
version
status
proc_date
avg( process_time ) as process_time
count(*) as call_count

into corresponding fields of table lt_srv_hdrlog_c
from zca_serv_hdrlog
where proc_date eq lv_date
group by service
version
status
proc_date.

The run time error is:

The current ABAP program "xxxxxxxxxxxxxxxxxxxxxxx" had to be

terminated because it found a

statement that could not be executed.

In include "Lxxxxxxxxxxxxxxxxxxxx", in line 114 of program

"SAPLxxxxxxxxxxxxx", the following syntax errors

have occurred:

Unknown column name "AVG". until runtime, you cannot specify a field l

ist.

[a142367]

i have eventually got this working using new open SQL syntax:

  select
       mandt
     , proc_date
     , service
     , version
     , status
     , count(*)            as call_count
     , avg( process_time ) as process_time

    from zca_serv_hdrlog
    where      proc_date   eq @lv_date
    group by   mandt
             , proc_date
             , service
             , version
             , status
    into table @lt_srv_hdrlog_c.