Problem with the following SQL statement

[Former Member]

Hi,

When I goto Activate the ABAP program the following error pops up for the SQL statement below;

DATA: total_shipping TYPE p DECIMALS 2.

SELECT SUM( KWERT )

INTO total_shipping

FROM KONV

WHERE KNUMV = '0000001104' and KSCHL = 'ZKF0'.

Error - "Aggregate functions and the addition DISTINCT are not supported in field lists for pooled and cluster tables."

What could be the solution be?

Thanks,

Kishan

[Former Member]

Hello,

Get the records in an internal table Add them or use the collect statement.

Regards,

Shekhar Kulkarni

[Former Member]

Hi,

Define total_shipping as

DATA: total_shipping LIKE KONV-KWERT.

OR

<b>DATA: total_shipping LIKE KONV-KAWRT</b>. as sometimes while doing SUM it will over flow so inorder to avoid that use this data declaration.

SELECT SUM( KWERT )

INTO total_shipping

FROM KONV

WHERE KNUMV = '0000001104' and KSCHL = 'ZKF0'.

Try this one.

Hope this helps.

[manuel_bassani]

Hi,

pooled and cluster tables (like BSEG, KONV, ...) have some restrictions (no aggregate functions, no inner join...)

you can use the following statements

DATA: begin of total_tab occurs 0,
      KWERT type KONV-KWERT,
      end of total_tab,

      wa like line of total_tab.

DATA: total_shipping TYPE p DECIMALS 2.

SELECT KWERT
INTO table total_tab
FROM KONV
WHERE KNUMV = '0000001104' and KSCHL = 'ZKF0'.

loop at total_tab into wa.
  at first.
    sum.
    total_shipping = wa-kwert.
    exit.
  endat.
endloop.

[Former Member]

Hi,

Table KONV is a cluster table. SUM you can do only for transparent tables.

DATA: total_shipping TYPE p DECIMALS 2.

SELECT KWERT FROM KONV WHERE KNUMV = '0000001104' and KSCHL = 'ZKF0'.

total_shipping = total_shipping + KONV-KWERT.

ENDSELECT.

[jayanthi_jayaraman]

Hi,

data itab type standard table of konv.

data wa type konv.

DATA: total_shipping TYPE p DECIMALS 2 value 0.

select * from konv into table itab

WHERE KNUMV = '0000001104' and KSCHL = 'ZKF0'.

loop at itab into wa.

total_shipping = total_shipping + wa-kwert.

endloop.

write total_shipping.

[Former Member]

Hi,

Try this one

REPORT zzz_test .

TABLES: konv.

TYPES: begin of ty_ship,
  kwert LIKE konv-kwert,
 end of ty_ship.
 DATA: total_shipping LIKE konv-kawrt.

 DATA: i_ship type standard table of ty_ship,
       w_ship type ty_ship.

SELECT kwert
INTO table i_ship
FROM konv
WHERE knumv = '0000001104' AND kschl = 'ZKF0'.

IF sy-subrc = 0.
clear total_shipping.
Loop at i_ship into w_ship.
 total_shipping = w_ship-kwert + total_shipping.
clear w_ship.
endloop.
ENDIF.
WRITE: / total_shipping.

Manually insert some values in debugging mode and check it.