topic group by please help in Application Development and Automation Discussions

group by please help

Former Member — Sat, 29 Oct 2005 06:02:30 GMT

Dear All,

I am tryinf group by in my select statement which is not working . please correct the code.

thanks

Vinayak

tables : ekko,ekpo,lfa1.

data : begin of itab occurs 0,

ebeln like ekko-ebeln,

bedat like ekko-bedat,

lifnr like ekko-lifnr,

netwr like ekpo-netwr,

name1 like lfa1-name1,

count type i,

end of itab.

select-options: s_bedat for ekko-bedat.

select a_{ebeln a}lifnr a_{bedat sum( b}netwr )

into corresponding fields of itab

from ekko as a inner join ekpo as b on b_{ebeln = a}ebeln

where a_{bedat in s_bedat group by a}ebeln a_{lifnr a}bedat.

select single name1 from lfa1 into itab-name1

where lifnr = itab-lifnr.

append itab.

endselect.

loop at itab.

on change of itab-lifnr.

skip.

write 😕 itab-lifnr, itab-name1.

endon.

write 😕 itab-ebeln, itab-bedat,itab-netwr.

endloop.

Re: group by please help

ChristianFi — Sat, 29 Oct 2005 07:26:05 GMT

See my comments below.

> Dear All,

> I am tryinf group by in my select statement which is

> not working . please correct the code.

> thanks

> Vinayak

> tables : ekko,ekpo,lfa1.

You do not need any tables statement. Just leave it away

> data : begin of itab occurs 0,

> ebeln like ekko-ebeln,

> bedat like ekko-bedat,

> lifnr like ekko-lifnr,

> netwr like ekpo-netwr,

> name1 like lfa1-name1,

> count type i,

> end of itab.

personally I do not like the declaration of table with

header lines and occurs and I did not figure out what you want to do with the count column in your internal table

> select-options: s_bedat for ekko-bedat.

> select a_{ebeln a}lifnr a_{bedat sum( b}netwr )

> into corresponding fields of itab

> from ekko as a inner join ekpo as b on b~ebeln =

> ln = a~ebeln

> where a_{bedat in s_bedat group by a}ebeln

> ebeln a_{lifnr a}bedat.

> select single name1 from lfa1 into itab-name1

> where lifnr = itab-lifnr.

> append itab.

> endselect.

There are tons of threads here advising not to use nested selects - in this case you could read the name of lfa1 right before you write it.

select a_{ebeln a}lifnr a_{bedat sum( b}netwr )

into table itab

from ekko as a inner join ekpo as b

on b_{ebeln = a}ebeln

where a_{bedat in s_bedat group by a}ebeln

ebeln a_{lifnr a}bedat.

should work

(corresponding is not necessary but would not work, unless you do not use " sum( b~netwr ) as netwr ".

make sure to sort the table before loop over it

> loop at itab.

> on change of itab-lifnr.

> skip.

here you could read your name if you must.

select single name1 from lfa1 into itab-name1

where lifnr = itab-lifnr.

> write 😕 itab-lifnr, itab-name1.

> endon.

> write 😕 itab-ebeln, itab-bedat,itab-netwr.

> endloop.

HTH Christian

Re: group by please help

Former Member — Sat, 29 Oct 2005 17:31:07 GMT

It doesn't like you selecting sum( b_{netwr ) into corresponding fields of itab because there is no field name <b>sum( b}netwr ).</b> Try:


TABLES: ekko,ekpo,lfa1.

DATA : BEGIN OF itab OCCURS 0,
  ebeln LIKE ekko-ebeln,
  bedat LIKE ekko-bedat,
  lifnr LIKE ekko-lifnr,
  netwr LIKE ekpo-netwr,
  name1 LIKE lfa1-name1,
  count TYPE i,
END OF itab.

SELECT-OPTIONS: s_bedat FOR ekko-bedat.

SELECT a~ebeln a~lifnr a~bedat SUM( b~netwr )
  INTO (itab-ebeln, itab-lifnr, itab-bedat, itab-netwr)
  FROM ekko AS a INNER JOIN ekpo AS b ON b~ebeln = a~ebeln
  WHERE a~bedat IN s_bedat
  GROUP BY A~EBELN a~lifnr A~BEDAT.

  SELECT SINGLE name1 FROM lfa1 INTO itab-name1
    WHERE lifnr = itab-lifnr.
  APPEND itab.
ENDSELECT.

LOOP AT itab.
  ON CHANGE OF itab-lifnr.
    SKIP.
    WRITE :/ itab-lifnr, itab-name1.
  ENDON.
  WRITE :/ itab-ebeln, itab-bedat,itab-netwr.
ENDLOOP.

You can also use the other sugestions.

Rob

Message was edited by: Rob Burbank