How to get row count of database table

Question

I am just new in abap language and I am trying to practice an inner join statement but I don't know how whether I will be able to get the number of rows of my select statement before output.

Here's what I want to achieved.

<--------------------------------------- >

< total number of rows > Record(s) found |

Column Header 1|Column Header 2 ..

< data 
 ....
 retrieved >


<--------------------------------------- >

Below is my select statement :

 SELECT spfli~carrid scarr~carrname sflight~planetype sflight~fldate sflight~price spfli~cityfrom spfli~cityto
   INTO (g_carrid ,g_carrname ,g_planetype,g_fldate ,g_price ,g_cityfrom ,g_cityto) FROM spfli
  INNER JOIN sflight
     ON spfli~carrid = sflight~carrid AND spfli~connid = sflight~connid
  INNER JOIN scarr
     ON scarr~carrid = spfli~carrid
  WHERE spfli~carrid = s_carrid-low.

  WRITE: / g_carrname ,g_planetype,g_fldate ,g_price ,g_cityfrom ,g_cityto.

 ENDSELECT.

And if you have any advice and idea on how to do this using internal table, please, show me a sample. I just really want to learn. Thank you and God Bless.

mydoghasworms · Accepted Answer · 2011-09-21T17:34:38.793

8

The system variable SY-DBCNT should give you the number of rows selected, but only after the select ends.

The alternative to SELECT-ENDSELECT is to select all the rows at once with SELECT INTO TABLE into an internal table (provided you are not selecting too much at once!).

For example:

data: lt_t000 type table of t000.

select * from t000 into table lt_t000.

This will select everything from that table in one go into the internal table. So what you could do is to declare an internal table with all the fields currently in your INTO clause and then specify INTO TABLE for your internal table.

After the SELECT executes, SY-DBCNT will contain the number of selected rows.

Here is a complete example, built around the SELECT statement in your question, which I have not checked for sanity, so I hope it works!

tables: spfli.

select-options: s_carrid for spfli-carrid.

* Definition of the line/structure
data: begin of ls_dat,
        carrid type s_carr_id,
        carrname type s_carrname,
        planetype type s_planetye,
        fldate type s_date,
        price type s_price,
        cityfrom type s_from_cit,
        cityto type s_to_city,
      end of ls_dat.
* Definition of the table:
data: lt_dat like table of ls_dat.

* Select data
select spfli~carrid scarr~carrname sflight~planetype sflight~fldate sflight~price spfli~cityfrom spfli~cityto
  into table lt_dat
  from spfli
  inner join sflight
  on spfli~carrid = sflight~carrid and spfli~connid = sflight~connid
  inner join scarr
  on scarr~carrid = spfli~carrid
  where spfli~carrid = s_carrid-low.

* Output data
write: 'Total records selected', sy-dbcnt.
loop at lt_dat into ls_dat.
  write: / ls_dat-carrid, ls_dat-carrname, ls_dat-planetype, ls_dat-fldate, ls_dat-price, ls_dat-cityfrom, ls_dat-cityto.
endloop.

Note: Report (type 1) programs still support the notion of declaring internal tables with header lines for backward compatibility, but this is not encouraged! Hope it works!

edited Sep 21 '11 at 17:34

answered Sep 21 '11 at 14:34

mydoghasworms

18,233
11
61
95

thank you Mister for your response but i just wanna ask if you could guide me or show me some samples based on my statement on how to execute an internal table with inner join select statement and loop it at a work_area..for reason that i couldn't get it right. please once again.. thanks – Kid Sep 21 '11 at 16:22
OK, after a lot of sweat, there is your example! – mydoghasworms Sep 21 '11 at 17:35
i really appreciate what you did Mister. Thank you very much. Have a blessed day always. I guess i really need to learn more. ^^, – Kid Sep 21 '11 at 18:21
4

Just complimenting the answer, if you don't have access to a given select statement to get `sy-dbcnt` and need to know the number of lines within an internal table, you can use `DESCRIBE TABLE lt_date LINES lv_integer`. – Paulo Sep 21 '11 at 18:45
1

You can also use lv_integer = lines(lt_date[]) – Esti Sep 22 '11 at 02:48
Just let me add one comment: while using of joins look like expert work, in ABAP it is general not recommended practice. It is usually better to load the data into memory and do all the "joins" using "read table" or "single select" statements. – Tom Burger Sep 22 '11 at 09:10
@TomBurger: Not sure where you get your "recommended practice" from. In cases where you are reading from DB tables with millions of records, offloading the processing to the database server can be a huge performance gain. – mydoghasworms Oct 03 '11 at 20:08
Not really... because based on the nature of R/3 architecture, you can have only 1 database server, but you can have many - and rather cheap - application servers. So, for scaling reasons, it is always preferred to offload the work from database server to application servers. And in case of need, you just add more application servers, because this is where you can scale horizontally (but you can't add more database servers). This is the first thing they tell you in SAP intro course. – Tom Burger Oct 04 '11 at 06:44
@TomBurger: To quote the online documentation in the examples on program RSHOWTIM (which you access from SE30 - Tips & Tricks): "To read data from several logically connected tables use a join instead of nested Select statements. Network load is considerably less." There is definitely more overhead associated with multiple SELECT statements, and this becomes apparent when the number of rows read go into the hundreds and thousands. – mydoghasworms Oct 04 '11 at 12:36
You are right of course with offloading work on to the application servers, but it is less expensive for the database to compile and execute a single statement with a JOIN than it is for both the app and DB servers to process thousands of SELECTS. – mydoghasworms Oct 04 '11 at 12:57

score 2 · Answer 2 · answered Jan 16 '16 at 17:37

If you only need row count without retrieving data itself the following syntax works as well

SELECT COUNT(*)
  FROM spfli
 INNER JOIN sflight
 ...

After execution of this query you will be able to get row count value from SY-DBCNT and DB load will be much less than during usual SELECT ... INTO itab. This is, however, true only if you don't need actual data. If you need both row count and data itself it is not sensible to split this into separate select statement.

How to get row count of database table

2 Answers2