The Different Types of SAP Tables

The Different Types of SAP Tables  

What is transparent, cluster and pool table?  Where and when we use these tables?

 

Transparent Table:  Exists with the same structure both in dictionary as well as in database exactly with the same data and fields.

Pooled Table: Pooled tables are logical tables that must be assigned to a table pool when they are defined. Pooled tables are used to store control data.  Several pooled tables can be combined in a table pool. The data of these pooled tables are then sorted in a common table in the database.

Cluster Table:  Cluster tables are logical tables that must be assigned to a table cluster when they are defined. Cluster tables can be used to store control data.  They can also be used to store temporary data or texts, such as documentation. 

Difference between Standard tables, Pooled tables and Cluster Tables?

 

A transparent table is a table that stores data directly. You can read these tables directly on the database from outside SAP with for instance an SQL statement. 

Transparent table is a one to one relation table i.e. when you create one transparent table then exactly same table will create in data base and if is basically used to store transaction data. 

A clustered and a pooled table cannot be read from outside SAP because certain data are clustered and pooled in one field. 

One of the possible reasons is for instance that their content can be variable in length and build up. Database manipulations in ABAP are limited as well. 

But pool and cluster table is a many to one relationship table. This means many pool table stores in a database table which is known as table pool.

All the pool table stored table in table pool does not need to have any foreign key relationship but in the case of cluster table it is must. And pool and cluster table is basically used to store application data.

Table pool can contain 10 to 1000 small pool table which has 10 to 100 records. But cluster table can contain very big but few (1 to 10) cluster table.

For pool and cluster table you can create secondary index and you can use select distinct, group for pool and cluster table. You can use native SQL statement for pool and cluster table. 

A structure is a table without data. It is only filled by program logic at the moment it is needed starting from tables. 

A view is a way of looking at the contents of tables. It only contains the combination of the tables at the basis and the way the data needs to be represented. You actually call directly upon the underlying tables. 

How many tables we will come across in ABAP?

Ans : 3 types : Pooled , clustered, Transparent 

How many kinds of internal table are there?

Ans:  5 Types. Standard Table,
                      Sorted Table,
                      Index Table,
                      Hashed Table,
                      Any Table (Generic type, rarely used)

ABAP ALV LIST VIEWER Example

ABAP ALV LIST VIEWER Example

 

This ALV program has all the basic report requirements such as page heading, page no, sub-total and a grand total.

* This is a basic ALV with the followings: -
* - Page Heading
* - Page No
* - Sub-Total
* - Grand Total

REPORT ZALV.

TYPE-POOLS: SLIS.

DATA:  G_REPID LIKE SY-REPID,
GS_PRINT                               TYPE   SLIS_PRINT_ALV,
GT_LIST_TOP_OF_PAGE      TYPE   SLIS_T_LISTHEADER,
GT_EVENTS                           TYPE   SLIS_T_EVENT,
GT_SORT                                TYPE   SLIS_T_SORTINFO_ALV,
GS_LAYOUT                          TYPE   SLIS_LAYOUT_ALV,
GT_FIELDCAT                       TYPE   SLIS_T_FIELDCAT_ALV,
FIELDCAT_LN                       LIKE    LINE OF GT_FIELDCAT,
COL_POS TYPE I.

DATA:             BEGIN OF ITAB,
  FIELD1(5) TYPE C,
  FIELD2(5) TYPE C,
  FIELD3(5) TYPE P DECIMALS 2,
END OF ITAB.

DATA:             BEGIN OF ITAB1 OCCURS 0.
  INCLUDE STRUCTURE ITAB.
DATA: END OF ITAB1.

DATA:             BEGIN OF ITAB_FIELDCAT OCCURS 0.
  INCLUDE STRUCTURE ITAB.
DATA: END OF ITAB_FIELDCAT.

* Print Parameters
PARAMETERS:
            P_PRINT         AS       CHECKBOX DEFAULT ' ',     "PRINT IMMEDIATE
            P_NOSINF       AS       CHECKBOX DEFAULT 'X',   "NO SELECTION INFO
            P_NOCOVE    AS       CHECKBOX DEFAULT ' ',     "NO COVER PAGE
            P_NONEWP    AS       CHECKBOX DEFAULT ' ',     "NO NEW PAGE
            P_NOLINF      AS       CHECKBOX DEFAULT 'X',   "NO PRINT LIST INFO
            P_RESERV TYPE I.                                                                "NO OF FOOTER LINE

INITIALIZATION.
G_REPID = SY-REPID.
PERFORM PRINT_BUILD    USING GS_PRINT.      "Print PARAMETERS

START-OF-SELECTION.

* TEST DATA
MOVE 'TEST1' TO ITAB1-FIELD1.
MOVE 'TEST1' TO ITAB1-FIELD2.
MOVE '10.00' TO ITAB1-FIELD3.
APPEND ITAB1.

MOVE 'TEST2' TO ITAB1-FIELD1.
MOVE 'TEST2' TO ITAB1-FIELD2.
MOVE '20.00' TO ITAB1-FIELD3.
APPEND ITAB1.

DO 50 TIMES.
  APPEND ITAB1.
ENDDO.

END-OF-SELECTION.

PERFORM       BUILD.
PERFORM       EVENTTAB_BUILD CHANGING GT_EVENTS.
PERFORM       COMMENT_BUILD  CHANGING GT_LIST_TOP_OF_PAGE.
PERFORM       CALL_ALV.

FORM BUILD.
* DATA FIELD CATALOG
* Explain Field Description to ALV
 
DATA:  FIELDCAT_IN TYPE SLIS_FIELDCAT_ALV.

CLEAR FIELDCAT_IN.
FIELDCAT_LN-FIELDNAME = 'FIELD1'.
FIELDCAT_LN-TABNAME   = 'ITAB1'.
*FIELDCAT_LN-NO_OUT    = 'X'.  "FIELD NOT DISPLAY, CHOOSE FROM LAYOUT
FIELDCAT_LN-KEY       = ' '.   "SUBTOTAL KEY
FIELDCAT_LN-NO_OUT    = ' '.
FIELDCAT_LN-SELTEXT_L = 'HEAD1'.
APPEND FIELDCAT_LN TO GT_FIELDCAT.

CLEAR FIELDCAT_IN.
FIELDCAT_LN-FIELDNAME = 'FIELD2'.
FIELDCAT_LN-TABNAME   = 'ITAB1'.
FIELDCAT_LN-NO_OUT    = 'X'.
FIELDCAT_LN-SELTEXT_L = 'HEAD2'.
APPEND FIELDCAT_LN TO GT_FIELDCAT.

CLEAR FIELDCAT_IN.
FIELDCAT_LN-FIELDNAME     = 'FIELD3'.
FIELDCAT_LN-TABNAME       = 'ITAB1'.
FIELDCAT_LN-REF_FIELDNAME = 'MENGE'. "<- REF FIELD IN THE DICTIONNARY
FIELDCAT_LN-REF_TABNAME   = 'MSEG'.  "<- REF TABLE IN THE DICTIONNARY
FIELDCAT_LN-NO_OUT        = ' '.
FIELDCAT_LN-DO_SUM        = 'X'.   "SUM UPON DISPLAY
APPEND FIELDCAT_LN TO GT_FIELDCAT.

* DATA SORTING AND SUBTOTAL
 
DATA: GS_SORT TYPE SLIS_SORTINFO_ALV.

CLEAR GS_SORT.
GS_SORT-FIELDNAME = 'FIELD1'.
GS_SORT-SPOS      = 1.
GS_SORT-UP        = 'X'.
GS_SORT-SUBTOT    = 'X'.
APPEND GS_SORT TO GT_SORT.

CLEAR GS_SORT.
GS_SORT-FIELDNAME = 'FIELD2'.
GS_SORT-SPOS      = 2.
GS_SORT-UP        = 'X'.
*GS_SORT-SUBTOT    = 'X'.
APPEND GS_SORT TO GT_SORT.

ENDFORM.

FORM CALL_ALV.
 
* ABAP List Viewer
 
CALL FUNCTION 'REUSE_ALV_LIST_DISPLAY'
EXPORTING
* I_INTERFACE_CHECK = ' '
* I_BYPASSING_BUFFER =
* I_BUFFER_ACTIVE = ' '
I_CALLBACK_PROGRAM = G_REPID
* I_CALLBACK_PF_STATUS_SET = ' '
* I_CALLBACK_USER_COMMAND = ' '
I_STRUCTURE_NAME = 'ITAB1'
IS_LAYOUT =  GS_LAYOUT
IT_FIELDCAT = GT_FIELDCAT[]
* IT_EXCLUDING =
* IT_SPECIAL_GROUPS =
  IT_SORT = GT_SORT[]
* IT_FILTER =
* IS_SEL_HIDE =
* I_DEFAULT = 'X'
* I_SAVE = ' '
* IS_VARIANT =
  IT_EVENTS = GT_EVENTS[]
* IT_EVENT_EXIT =
  IS_PRINT = GS_PRINT
* IS_REPREP_ID =
* I_SCREEN_START_COLUMN = 0
* I_SCREEN_START_LINE = 0
* I_SCREEN_END_COLUMN = 0
* I_SCREEN_END_LINE = 0
* IMPORTING
* E_EXIT_CAUSED_BY_CALLER =
* ES_EXIT_CAUSED_BY_USER =
TABLES
T_OUTTAB = ITAB1
EXCEPTIONS
PROGRAM_ERROR = 1
OTHERS = 2.
 
ENDFORM.

* HEADER FORM
 
FORM EVENTTAB_BUILD CHANGING LT_EVENTS TYPE SLIS_T_EVENT.
CONSTANTS:
GC_FORMNAME_TOP_OF_PAGE TYPE SLIS_FORMNAME VALUE 'TOP_OF_PAGE'.
*GC_FORMNAME_END_OF_PAGE TYPE SLIS_FORMNAME VALUE 'END_OF_PAGE'.

  DATA: LS_EVENT TYPE SLIS_ALV_EVENT.

  CALL FUNCTION 'REUSE_ALV_EVENTS_GET'
       EXPORTING
            I_LIST_TYPE = 0
       IMPORTING
            ET_EVENTS   = LT_EVENTS.

  READ TABLE LT_EVENTS WITH KEY NAME =  SLIS_EV_TOP_OF_PAGE
                           INTO LS_EVENT.
  IF SY-SUBRC = 0.
    MOVE GC_FORMNAME_TOP_OF_PAGE TO LS_EVENT-FORM.
    APPEND LS_EVENT TO LT_EVENTS.
  ENDIF.

* define END_OF_PAGE event
* READ TABLE LT_EVENTS WITH KEY NAME =  SLIS_EV_END_OF_PAGE
*                          INTO LS_EVENT.
* IF SY-SUBRC = 0.
*   MOVE GC_FORMNAME_END_OF_PAGE TO LS_EVENT-FORM.
*   APPEND LS_EVENT TO LT_EVENTS.
* ENDIF.
 
ENDFORM.

FORM COMMENT_BUILD CHANGING GT_TOP_OF_PAGE TYPE SLIS_T_LISTHEADER.
  DATA: GS_LINE TYPE SLIS_LISTHEADER.

  CLEAR GS_LINE.
  GS_LINE-TYP  = 'H'.
  GS_LINE-INFO = 'HEADER 1'.
  APPEND GS_LINE TO GT_TOP_OF_PAGE.

  CLEAR GS_LINE.
  GS_LINE-TYP  = 'S'.
  GS_LINE-KEY  = 'STATUS 1'.
  GS_LINE-INFO = 'INFO 1'.
  APPEND GS_LINE TO GT_TOP_OF_PAGE.
  GS_LINE-KEY  = 'STATUS 2'.
  GS_LINE-INFO = 'INFO 2'.
  APPEND GS_LINE TO GT_TOP_OF_PAGE.

* CLEAR GS_LINE.
* GS_LINE-TYP  = 'A'.
*
* GS_LINE-INFO = 'ACTION'.
* APPEND GS_LINE TO  GT_TOP_OF_PAGE.

ENDFORM.

FORM TOP_OF_PAGE.
  CALL FUNCTION 'REUSE_ALV_COMMENTARY_WRITE'
       EXPORTING
            IT_LIST_COMMENTARY = GT_LIST_TOP_OF_PAGE.
  WRITE: SY-DATUM, 'Page No', SY-PAGNO LEFT-JUSTIFIED.
 
ENDFORM.

FORM END_OF_PAGE.
  WRITE at (sy-linsz) sy-pagno CENTERED.
 
ENDFORM.

* PRINT SETTINGS
 
FORM PRINT_BUILD USING LS_PRINT      TYPE   SLIS_PRINT_ALV.
  LS_PRINT-PRINT              = P_PRINT.        "PRINT IMMEDIATE
  LS_PRINT-NO_PRINT_SELINFOS  = P_NOSINF.   "NO SELECTION INFO
  LS_PRINT-NO_COVERPAGE       = P_NOCOVE.   "NO COVER PAGE
  LS_PRINT-NO_NEW_PAGE        = P_NONEWP.
  LS_PRINT-NO_PRINT_LISTINFOS = P_NOLINF. "NO PRINT LIST INFO
  LS_PRINT-RESERVE_LINES      = P_RESERV.

ENDFORM.

*END OF ZALV PROGRAM

LIST of SAP SD TABLES

LIST of SAP SD TABLES

What are SY-TABIX, SY-INDEX, SY-DBCNT, SY-SUBRC?

What are SY-TABIX, SY-INDEX, SY-DBCNT, SY-SUBRC?

SY-TABIX : used for record count of internal tables.
SY-INDEX : used for loop counter.
SY-DBCNT : it gives the number of rows read from the database.
SY-SUBRC : it is used for error checking

What is ALV Reports?

What  is ALV Reports?

ALV report is used for various functionalities. Such as sorting, filtering and retrieving data. Also used to perform functions on the data of the report. It can display up to 90 columns or more with various display options. U needs to create an ALV report when u needs to include the columns that store more than 255 characters in their field value in a report.

    Features of ALV report

       User friendliness

      Ability to handle events

      Use of moderate code

Various module of ALV report

REUSE_ALV_GRID_DISPLAY	Display and ALV grid as per the parameters defined in the function call
REUSE_ALV_LIST_DISPLAY	Display and ALV list as per the parameters defined in the function call
REUSE_ALV_COMMENTRY_WRITE	Display the list header information
REUSE_ALV_HIERSEQ_LIST_DISPLAY	Display two internal tables that are formatted in the hierarchical-sequential list format
REUSE_ALV_GRID_VARIANT	Display a variant selection dialog box
REUSE_ALV_GRID_VARIANT_EXISTENSE	Checks the existence of the variant display

How to create ALV report

ALV Reports

ALV report is used for various functionalities. Such as sorting, filtering and retrieving data. Also used to perform functions on the data of the report. It can display up to 90 columns or more with various display options. U needs to create an ALV report when u needs to include the columns that store more than 255 characters in their field value in a report.

Features of ALV report

1.       User friendliness

2.       Ability to handle events

3.       Use of moderate code

Various module of ALV report

REUSE_ALV_GRID_DISPLAY	Display and ALV grid as per the parameters defined in the function call
REUSE_ALV_LIST_DISPLAY	Display and ALV list as per the parameters defined in the function call
REUSE_ALV_COMMENTRY_WRITE	Display the list header information
REUSE_ALV_HIERSEQ_LIST_DISPLAY	Display two internal tables that are formatted in the hierarchical-sequential list format
REUSE_ALV_GRID_VARIANT	Display a variant selection dialog box
REUSE_ALV_GRID_VARIANT_EXISTENSE	Checks the existence of the variant display

How to create ALV report

To create a ALV report follow the below steps—

1.       Open the initial screen of ABAP editor by either navigating to the SAP menu or executing the SE38 transaction code.

2.       In the initial screen of ABAP editor, enter ZALVREPORT (as example), select the source code radio button in the subobjects group box, and click the Create pushbutton to create a new program, as shown below figure

SQL Example COUNT , Select single and sql function(ABAP Programming)

SQL Example COUNT , Select single and sql function(ABAP Programming) 

*** counting the database rows

REPORT  ZCount_Test.

parameters: p1 LIKE VBAP-VBELN.

write:/ p1.

Tables VBAP.

SELECT COUNT(*)

FROM VBAP INTO total

WHERE VBELN <= p1 AND POSNR = '30'.

write:/ total.

*** selecting single Rows using sql function

REPORT  ZSelect_single-Test.

parameters: p1 LIKE VBAP-VBELN.

write:/ p1.

parameters: p1 LIKE VBAP-VBELN.

write:/ p1.

tables VBAP.

select single * from VBAP

where VBELN IN (p1).

write: / VBAP-VBELN,VBAP-POSNR,VBAP-MATNR,VBAP-MATWA, VBAP-ARKTX .