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.

Tuesday, July 9, 2013

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