Saturday, July 20, 2013

ABAP: select count on an internal table?

I recently came across interesting fragment of ABAP code. Actually it was causing a problem means ABAP dump.

DATAit_tab    TYPE TABLE OF usr02,
      lt_tab2   
TYPE TABLE OF usr02,
      lv_count  
TYPE i,
      lv_tabnm  
TYPE string.

SELECT *
  
FROM usr02
  
INTO  TABLE it_tab.

lv_tabnm 
'it_tab'.

IF sy-dbcnt NE 0.
  
SELECT COUNT(*FROM (lv_tabnm)
    
INTO lv_count
    
WHERE bname 'DDIC'.
ENDIF.

Apparently developer never tested this piece of code. He or she just simply assumed that it is possible to supply internal table name into SELECT statement. In this case SELECT COUNT which was supposed to return no of lines in that internal table.

However SELECT COUNT on internal tables doesn't work. There are other possibilities how to count no of rows in internal table. You can only supply name of DDIC table into the text variable and SELECT COUNT will count no of rows in the database table.

If you do need to count no of rows in itab as whole w/o any WHERE condition there are following possibilities as far as I know:
  1. Very old fashioned way via LOOP statement. It is not very efficient since iterating over every row of table:

    LOOP AT it_tab TRANSPORTING NO FIELDS WHERE bname 'DDIC'.
      lv_count 
    lv_count + 1.
    ENDLOOP.

  1. Via DESCCRIBE TABLE statement, just you cannot specify any conditions:
DESCRIBE TABLE it_tab LINES lv_count.

  1. Via LINES function, again it is not possible to specify any conditions:
lv_count LINESit_tab ).

  1. Usage of system variable SY-TFILL which with ABAP statements DESCRIBE TABLE, LOOP AT, and READ TABLE is filled with the row number of the internal table.
DESCRIBE TABLE result_package.
IF sy-tfill > 0.

  1. One option to build efficient way how to count the rows with respect to some condition is following. By using DELETE statement we delete records that do not fulfill condition and then we use LINES function to count rows of table.
it_tab2 it_tab.
DELETE it_tab2 WHERE NOT bname 'DDIC'.
lv_count 
LINESit_tab2 ).

Conclusion is that you cannot use SELECT COUNT on internal tables. The statement like:

SELECT COUNT(*FROM (lv_tabnm)

Would only work has if lv_tabnm is text variable and contains the name of a database (DDIC) table. By this way SELECT COUNT is executed on this database table.

- update 09/11/2013 –
Usage no.4 was added. I just want to stress out the reason why to know no of the internal tables is important. To determine number of rows of internal tables is very often used in ABAP programs. Due to the ABAP serves to process business data of high volumes there is always counting of rows in internal tables.

4 comments:

Matus said...

Vdaka :)

Martin Maruskin said...

Nie je zac :-)

Anonymous said...

"Here's a more efficient way. (DELETE is not a good solution)
SORT lt_tab BY cond_field.

lv_idx = sy-tabix.

LOOP AT lt_tab FROM lv_idx.

IF lt_tab-field = cond_field.
lv_count = lv_count+1.
ELSE.
EXIT.
ENDIF.

ENDLOOP.

Unknown said...

Syntax:
data(lv_count) = REDUCE type( INIT n = 0 FOR ls_tab IN lt_tab WHERE ( condition ) NEXT n = n + 1 ).

Example:
lv_count = REDUCE i( INIT n = 0
FOR ls_items
IN gt_tc_items
WHERE ( regnr <> space AND clamnt <> 0 )
NEXT n = n + 1 ).