topic Fetch data from different database tables in Application Development and Automation Discussions

Fetch data from different database tables

Former Member — Tue, 02 Oct 2007 02:33:52 GMT

Hi...

How can i fetch data from different database tables and put it into a internal table and then display it??? Can provide simple short codes as i'm new to ABAP. Thanks.

Re: Fetch data from different database tables

Former Member — Tue, 02 Oct 2007 02:35:56 GMT

Hi,

Check this sample code..


TYPE-POOLS: slis.

DATA: BEGIN OF itab OCCURS 0,
        vbeln TYPE vbeln,
        expand,
      END OF itab.

DATA: BEGIN OF itab1 OCCURS 0,
        vbeln TYPE vbeln,
        posnr TYPE posnr,
        matnr TYPE matnr,
        netpr TYPE netpr,
      END OF itab1.

DATA: t_fieldcatalog TYPE slis_t_fieldcat_alv.
DATA: s_fieldcatalog TYPE slis_fieldcat_alv.

s_fieldcatalog-col_pos = '1'.
s_fieldcatalog-fieldname = 'VBELN'.
s_fieldcatalog-tabname   = 'ITAB'.
s_fieldcatalog-rollname  = 'VBELN'.
s_fieldcatalog-outputlen = '12'.
APPEND s_fieldcatalog TO t_fieldcatalog.
CLEAR: s_fieldcatalog.

s_fieldcatalog-col_pos = '1'.
s_fieldcatalog-fieldname = 'VBELN'.
s_fieldcatalog-tabname   = 'ITAB1'.
s_fieldcatalog-rollname  = 'VBELN'.
s_fieldcatalog-outputlen = '12'.
APPEND s_fieldcatalog TO t_fieldcatalog.
CLEAR: s_fieldcatalog.

s_fieldcatalog-col_pos = '2'.
s_fieldcatalog-fieldname = 'POSNR'.
s_fieldcatalog-tabname   = 'ITAB1'.
s_fieldcatalog-rollname  = 'POSNR'.
APPEND s_fieldcatalog TO t_fieldcatalog.
CLEAR: s_fieldcatalog.

s_fieldcatalog-col_pos = '3'.
s_fieldcatalog-fieldname = 'MATNR'.
s_fieldcatalog-tabname   = 'ITAB1'.
s_fieldcatalog-rollname  = 'MATNR'.
APPEND s_fieldcatalog TO t_fieldcatalog.
CLEAR: s_fieldcatalog.

s_fieldcatalog-col_pos = '4'.
s_fieldcatalog-fieldname = 'NETPR'.
s_fieldcatalog-tabname   = 'ITAB1'.
s_fieldcatalog-rollname  = 'NETPR'.
s_fieldcatalog-do_sum    = 'X'.
APPEND s_fieldcatalog TO t_fieldcatalog.
CLEAR: s_fieldcatalog.

DATA: s_layout TYPE slis_layout_alv.

s_layout-subtotals_text            = 'SUBTOTAL TEXT'.
s_layout-key_hotspot = 'X'.
s_layout-expand_fieldname = 'EXPAND'.


SELECT vbeln UP TO 100 ROWS
       FROM
       vbak
       INTO TABLE itab.


IF NOT itab[] IS INITIAL.

  SELECT vbeln posnr matnr netpr
         FROM vbap
         INTO TABLE itab1
         FOR ALL ENTRIES IN itab
         WHERE vbeln = itab-vbeln.

ENDIF.


DATA: v_repid TYPE syrepid.

v_repid = sy-repid.

DATA: s_keyinfo TYPE slis_keyinfo_alv.
s_keyinfo-header01 = 'VBELN'.
s_keyinfo-item01   = 'VBELN'.

CALL FUNCTION 'REUSE_ALV_HIERSEQ_LIST_DISPLAY'
     EXPORTING
          i_callback_program = v_repid
          is_layout          = s_layout
          it_fieldcat        = t_fieldcatalog
          i_tabname_header   = 'ITAB'
          i_tabname_item     = 'ITAB1'
          is_keyinfo         = s_keyinfo
     TABLES
          t_outtab_header    = itab
          t_outtab_item      = itab1
     EXCEPTIONS
          program_error      = 1
          OTHERS             = 2.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.

Thanks

Naren

Re: Fetch data from different database tables

Former Member — Tue, 02 Oct 2007 02:46:34 GMT

Some Examples:

DATA: gt_bdrgin TYPE STANDARD TABLE OF bdrgin INITIAL SIZE 0,

SELECT * FROM bdrgin INTO TABLE gt_bdrgin

WHERE obj_type IN lr_serial AND

chnum IN lr_channel.

SELECT SINGLE stext FROM tbdlst

INTO lv_facility_name

WHERE langu = sy-langu AND

logsys = gs_bdrgin-sndprn.

Or If you want data from diff tables at once, you can use JOIN. Something like

SELECT t685_{kschl t682z}kozgf t682i_{kotabnr t682z}kvewe

t682z_{kappl t682z}kolnr zaehk zifna

FROM t685

INNER JOIN t682i

ON t685_{kvewe eq t682i}kvewe and

t685_{kappl eq t682i}kappl and

t685_{kozgf eq t682i}kozgf

INNER JOIN t682z

ON t682i_{kvewe eq t682z}kvewe and

t682i_{kappl eq t682z}kappl and

t682i_{kozgf eq t682z}kozgf and

t682i_{kolnr eq t682z}kolnr

INTO TABLE gt_t682

WHERE t685~kvewe = 'A' AND " 'A' - Pricing

t685~kappl = 'V' AND " 'V' - Sales/Distribution

t685~kschl IN gr_kschl AND

t682z~qustr NE ''.

Sri