Skip to Content

How to extract cost centers and their number of appendant assets to internal table?

Hello,

my aim is to get an internal table with all my selected cost centers and the number of their appendant assets to pass this to a SmartForm.

The result would be something like:

cost center
number of assets 100000 65 100001 13 100002 105 100007 3 100008 57

What I did so far: I made up an SQL statement, which I tested in the transaction DBACOCKPIT (Performance -> Additional Functions -> SQL Command Editor), which works fine and gives me exactly what I need.

The SQL statement is (slightly simplified, and exemplarily only for costcenters 100000 and 100001):

select costcenter, count( assetnr ) as numberofassets from
     ( select anlz.kostlv as costcenter, anla.anln1 as assetnr, anla.deakt, anla.aktiv from anla
          join anlz on anla.mandt = anlz.mandt and anla.bukrs = anlz.bukrs and anla.anln1 = anlz.anln1
          where anlz.kostlv = '0000100000' or anlz.kostlv = '00000100001' )
group by costcenter


Now, I want this SQL statement to work in my ABAP code. To match the requirements of the ABAP Editor (SE80), I

- replaced all the point delimiters (.) by a tilde (~),

- removed all commas,

- added "into costcenter_assetcount" to store my result in an internal table.

So in my ABAP code the SQL looks like this (alias assetnr inb the first line is printed in red):

select costcenter count( assetnr ) as numberofassets from
( select anlz~kostlv as costcenter anla~anln1 as assetnr anla~deakt anla~aktiv from anla
join anlz
on anla~mandt = anlz~mandt
and anla~bukrs = anlz~bukrs
and anla~anln1 = anlz~anln1
where anlz~kostlv = '0000100000' or anlz~kostlv = '00000100001' )
into costcenter_assetcount
group by costcenter

Checking my code, ABAP Editor gives me a syntax error pointing to the SELECT of the subquery (it's in German, because this is my default language setting). After my first FROM, ABAP seems to expect a table name instead of a subquery.

Programm Z_FIAA_ASSETCOUNT @0A\QFehler@

"SELECT" ist im ABAP-Dictionary nicht als Tabelle, Projektions- oder

Datenbank-View deklariert.

Isn't it possible to use a SELECT subquery after my FROM in the ABAP Editor?

What would be the best practice to put the data in an internal table?

Looking forward to your comments!

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Best Answer
    Sep 05, 2014 at 09:58 AM

    Hi,

    I think, You make it to complicated, try this:

    select anlz~kostlv as costcenter count( distinct anla~anln1 ) as numberofassets

    from anla

    join anlz

    on anla~mandt = anlz~mandt

    and anla~bukrs = anlz~bukrs

    and anla~anln1 = anlz~anln1

    where anlz~kostlv = '0000100000' or anlz~kostlv = '00000100001'

    group by anlz~kostlv.


    Jarek

    Add comment
    10|10000 characters needed characters exceeded