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.

DATA: it_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.

2. Via DESCCRIBE TABLE statement, just you cannot specify any conditions:

DESCRIBE TABLE it_tab LINES lv_count.

3. Via LINES function, again it is not possible to specify any conditions:

lv_count = LINES( it_tab ).

4. 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.

5. 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 = LINES( it_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.

ABAP on HANA: How to check what tables are type of columnar?

This is a re-post of my SCN contribution related to ABAP on HANA topic.

ABAP on HANA: How to check what tables are type of columnar?

Useful class for coding of BW routines in Transformations

Recently I came across a coding in ABAP rule in transformation where simple method from class CL_RSAR_FUNCTION was used. It was method fiscper_calmonth. This simple method derives calendar month (0CALMONTH) as per given fiscal period (0FISCPER).

I had a look into other methods in same class and I found it very interesting. Method contains 55 methods in total in release BW 7.3 SP08. Some of them are very useful. Basic calendar and string function are there. For overview I’m providing full list of methods below. I’m sure I’ll use this class in my next BW developments.

ABORT_PACKAGE Function: Abort Package

ADD_TO_DATE Function: Add Days to Date

CALMONTH_FISCPER Function: Calendar Month ->Fiscal Period

CONDENSE Function: Condense

CONDENSE_NO_GAPS Function: Condense No Gaps

DATECONV Function: Date Conversion

DATE_DIFF Function: Date Difference

DATE_FISCPER Function: Date -> Fiscal Period

DATE_FISCPER3 Function: Date -> Fiscal Period 3

DATE_FISCYEAR Function: Date -> Fiscal Year

DATE_HALFYEAR Function: Date -> Half-Year

DATE_MONTH Function: Date -> Month

DATE_MONTH2 Function: Date -> Month 2

DATE_QUARTER Function: Date -> Quarter

DATE_QUARTER1 Function: Date -> Quarter1

DATE_WEEK Date Conversion in Calendar Week

DATE_WEEKDAY Function: Date -> Weekday (Textual)

DATE_WEEKDAY1 Function: Date -> Weekday (Technical)

DATE_YEAR Function: Date -> Calendar Year

FIRST_WORKINGDAY_MONTH Calculation of First Working Day in Month

FIRST_WORKINGDAY_YEAR Calculation of First Working Day in Month

FISCPER_CALMONTH Function: Fiscal Period -> Calendar Month

FISCPER_FISCYEAR Function: Fiscal Period -> Fiscal Year

IF Function: IF Statement

IS_INITIAL Function: Is Initial

LAST_WORKINGDAY_MONTH Calculation of Last Working Day in Month

LAST_WORKINGDAY_YEAR Calculation of Last Working Day in Year

LEFT Function: Left

L_TRIM Function: Left Trim

MAPPING Function: Field Mapping Transfer Structure

MONTH2_HALFYEAR Function: Month 2 -> Half-Year

MONTH2_QUARTER1 Function: Month 2-> Quarter1

MONTH_HALFYEAR Function: Month -> Half-Year

MONTH_QUARTER Function: Month -> Quarter

MONTH_QUARTER1 Function: Month -> Quarter1

MONTH_YEAR Function: Month -> Calendar Year

NEGATIVE Function: Reverse +/- Sign

QUARTER1_HALFYEAR Function: Quarter 1-> Half-Year

QUARTER_HALFYEAR Function: Quarter -> Half-Year

QUARTER_YEAR Function: Quarter -> Calendar Year

REPLACE_ALL Function: Replace All

REPLACE_FIRST Function: Replace First

RIGHT Function: Right

R_TRIM Function: Right Trim

SHIFT_LEFT Function: Shift Left

SHIFT_RIGHT Function: Shift Right

SKIP_RECORD Function: Skip Record

SKIP_RECORD_AS_ERROR Function: Skip Record (With Error Message in Monitor)

STR_LEN Function: strlen

UNIT_CONVERSION Function: Unit Conversion

WEEK_FIRST_DAY Calendar Week Conversion in First Day of Week Date

WORKINGDAY_MONTH Function: Date -> Working Day of Corresponding Month

WORKINGDAY_YEAR Function: Date -> Working Day of Corresponding Year

AMOUNT_DAYS_OF_MONTH Number of Days in Month

GREATER_OF_TWO_MONTH Which of 2 Months Has More Days Within Period