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.

Answer 1

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!

Answer 2

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.