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

[OO]

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!

[Jarosław]

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