topic Re: Problem with the following SQL statement in Application Development and Automation Discussions

Problem with the following SQL statement

Former Member — Mon, 01 Aug 2005 10:36:52 GMT

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

Re: Problem with the following SQL statement

Former Member — Mon, 01 Aug 2005 10:39:38 GMT

Hello,

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

Regards,

Shekhar Kulkarni

Re: Problem with the following SQL statement

Former Member — Mon, 01 Aug 2005 10:44:47 GMT

Hi,

Define total_shipping as

DATA: total_shipping LIKE KONV-KWERT.

<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.

Re: Problem with the following SQL statement

manuel_bassani — Mon, 01 Aug 2005 10:45:45 GMT

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.

Re: Problem with the following SQL statement

Former Member — Mon, 01 Aug 2005 10:47:27 GMT

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.

Re: Problem with the following SQL statement

Former Member — Mon, 01 Aug 2005 10:58:10 GMT

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.

Re: Problem with the following SQL statement

jayanthi_jayaraman — Mon, 01 Aug 2005 10:58:37 GMT

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.