Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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
10000065
10000113
100002105
1000073
10000857

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!

Tags:
replied

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

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question