topic Re: how to write select to get data from diff tables in Application Development and Automation Discussions

how to write select to get data from diff tables

Former Member — Tue, 06 Jun 2006 04:38:54 GMT

Hi folks,

i need to develope a report for sales tax, for that i need to get

the data from the tables bsis(fiedls budat, hkont bukrs), and

custemer number from bsid(for open items), bsad(for cleared items)

and custemer name from kna1 corresponding to bsis and from table bset(

belnr , gjahr,hwbas, txjcd) corresponding to bsis table and

i need to disply these all details using alv , how to write select for

this and how to get alv display in effient way,

pls help me to do this.

thanks in advance.

Re: how to write select to get data from diff tables

Former Member — Tue, 06 Jun 2006 04:42:38 GMT

Hai Vishal

Check the following Code

for retrive data from more than one table as follows

REPORT ZSSSS1 LINE-SIZE 120

LINE-COUNT 25(3)

MESSAGE-ID ZSAN.

********************************************************************

*Program Desc: INTERACTIVE REPORT FOR PURCHASE ORDER DETAILS

BASIC LIST CONTAINS HEADER DETAILS
SECONDARY LIST CONTAINS ITEM DETAILS
AND CONDETION RECORD DETAILS

********************************************************************

T A B L E S U S E D *

********************************************************************

TABLES: EKKO,EKPO,KONV,LFA1,T001.

********************************************************************

S E L E C T I O N S C R E E N *

********************************************************************

SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-000.

SELECT-OPTIONS:

S_LIFNR FOR EKKO-LIFNR,

S_BUKRS FOR EKKO-BUKRS.

SELECTION-SCREEN END OF BLOCK B1.

********************************************************************

D A T A D E C L A R A T I O N S *

********************************************************************

DATA: BEGIN OF IT_EKKO OCCURS 0,

BUKRS LIKE EKKO-BUKRS,

LIFNR LIKE EKKO-LIFNR,

EBELN LIKE EKKO-EBELN,

KNUMV LIKE EKKO-KNUMV,

END OF IT_EKKO.

DATA: BEGIN OF IT_EKPO OCCURS 0,

EBELN LIKE EKPO-EBELN,

EBELP LIKE EKPO-EBELP,

MATNR LIKE EKPO-MATNR,

INFNR LIKE EKPO-INFNR,

MENGE LIKE EKPO-MENGE,

MEINS LIKE EKPO-MEINS,

NETPR LIKE EKPO-NETPR,

END OF IT_EKPO.

DATA: BEGIN OF IT_KONV OCCURS 0,

KNUMV LIKE KONV-KNUMV,

KSCHL LIKE KONV-KSCHL,

KAWRT LIKE KONV-KAWRT,

KBETR LIKE KONV-KBETR,

KPOSN LIKE KONV-KPOSN,

END OF IT_KONV.

DATA: BEGIN OF IT_LFA1 OCCURS 0,

LIFNR LIKE LFA1-LIFNR,

NAME1 LIKE LFA1-NAME1,

STRAS LIKE LFA1-STRAS,

ORT01 LIKE LFA1-ORT01,

PSTLZ LIKE LFA1-PSTLZ,

LAND1 LIKE LFA1-LAND1,

END OF IT_LFA1.

DATA: V_BUTXT LIKE T001-BUTXT,

FNAM(20) TYPE C,

FVAL(20) TYPE C.

********************************************************************

A T S E L E C T I O N - S C R E E N O N <FIELD> *

********************************************************************

AT SELECTION-SCREEN ON S_BUKRS.

PERFORM VALIDATE.

********************************************************************

S T A R T - O F - S E L E C T I O N *

********************************************************************

START-OF-SELECTION.

PERFORM SELECT_DATA. " SELECTION OF ALL DATA

PERFORM DISPLAY_DATA. " DISPLAY OF BASIC LIST

&----

*& Form SELECT_DATA

&----

THIS FORM CONTAINS ALL SELECTION OF DATA STATEMENTS

----

form SELECT_DATA.

SELECT BUKRS LIFNR EBELN KNUMV

FROM EKKO

INTO TABLE IT_EKKO

WHERE LIFNR IN S_LIFNR

AND BUKRS IN S_BUKRS.

IF NOT IT_EKKO[] IS INITIAL.

SELECT EBELN EBELP MATNR INFNR MENGE MEINS NETPR

FROM EKPO

INTO TABLE IT_EKPO

FOR ALL ENTRIES IN IT_EKKO

WHERE EBELN = IT_EKKO-EBELN.

IF SY-SUBRC = 0.

LOOP AT IT_EKKO.

LOOP AT IT_EKPO.

SELECT KNUMV KSCHL KAWRT KBETR KPOSN

FROM KONV

INTO IT_KONV

WHERE KNUMV = IT_EKKO-KNUMV

AND KPOSN = IT_EKPO-EBELP.

APPEND IT_KONV.

ENDSELECT.

ENDLOOP.

ENDIF." END OF IF FOR CHECK OF SY-SUBRC

SELECT LIFNR NAME1 STRAS ORT01 PSTLZ LAND1

INTO TABLE IT_LFA1

FROM LFA1

FOR ALL ENTRIES IN IT_EKKO

WHERE LIFNR = IT_EKKO-LIFNR.

ENDIF." END OF IF FOR IT_EKKO IS NOT INITIAL

endform. " SELECT_DATA

&----

*& Form DISPLAY_DATA

&----

DISPLAY THE BASIC DATA

----

form DISPLAY_DATA.

DATA: LINNO TYPE I.

SORT IT_EKKO BY BUKRS LIFNR EBELN.

LOOP AT IT_EKKO.

READ TABLE IT_LFA1 WITH KEY LIFNR = IT_EKKO-LIFNR.

SELECT SINGLE BUTXT

FROM T001

INTO (V_BUTXT)

WHERE BUKRS = IT_EKKO-BUKRS.

WRITE:/1 SY-VLINE,

(10) IT_EKKO-EBELN HOTSPOT ON,

SY-VLINE,

(10) IT_EKKO-LIFNR HOTSPOT ON,

SY-VLINE,

(15) IT_LFA1-NAME1 HOTSPOT ON,

SY-VLINE,

(10) IT_EKKO-BUKRS HOTSPOT ON,

SY-VLINE,

(15) V_BUTXT,

75 SY-VLINE.

HIDE: IT_EKKO-EBELN,

IT_EKKO-LIFNR.

WRITE:/1(75) SY-ULINE.

AT END OF LIFNR.

LINNO = SY-LINCT - SY-LINNO - 1.

SKIP LINNO.

ENDAT.

ENDLOOP.

endform. " DISPLAY_DATA

&----

*& Form VALIDATE

&----

VALIDATE BUKRS

----

form VALIDATE.

SELECT SINGLE *

FROM T001

WHERE BUKRS IN S_BUKRS.

IF SY-SUBRC <> 0.

MESSAGE E001.

ENDIF.

endform. " VALIDATE

Thanks & reagrds

Sreenivasulu P

Re: how to write select to get data from diff tables

Former Member — Tue, 06 Jun 2006 04:46:53 GMT

Hi Vishal,

There are multiple ways of doing it..

You can write one long query with many Inner Joins amongest the different tables..

Or else

You can get the contents of the 2 tables (inner joined) in internal table and then again use this internal table in select query for all entries..

Once u get the required contents in the internal table, you can display the contents in the ALV grid..

For ALV help, have a look at any standard program in package SLIS..

Regards,

Tanveer.

Please mark helpful answers

Re: how to write select to get data from diff tables

Former Member — Tue, 06 Jun 2006 05:02:08 GMT

select bukrs hkont budat belnr

into coressponding fields of itab

from BSIS

where bukrs in s_bukrs.

select single bukrs kunnr

into corresponding fields of itab

from BSID

where bukrs = itab-bukrs.

select single bukrs kunnr

into corresponding fields of itab

from BSAD

where bukrs = itab-bukrs.

select single kunnr into v_kunnr

from KNA1 where kunnr = itab-kunnr1.

select single bukrs belnr gjahr hwbas txcjd

into corresponding fields of itab

from BSET

where bukrs = itab-bukrs and belnr = itab-belnr.

append itab.

clear itab.

endselect.

if sy-subrc <> 0.

message E035. "Record not Found

endif.

use REUSE_ALV_GRID_DISPLAY func. module.

Regards,

Sangeeta.

Re: how to write select to get data from diff tables

Former Member — Tue, 06 Jun 2006 05:56:30 GMT

thanks for all your replies,

but i am trying to get the bsadkunnr & bsid-kunnr into same field itab-kunnr, when i try to select its going to shot dump, how to achieve this.

below is my code

data : begin of it_bsis occurs 0,

bukrs like bsis-bukrs,

budat like bsis-budat,

hkont like bsis-hkont,

belnr like bsis-belnr,

end of it_bsis.

data : begin of it_kna1 occurs 0,

bukrs like bsid-bukrs,

kunnr like bsid-kunnr,

name1 like kna1-name1,
budat like bsid-budat,

end of it_kna1.

data : begin of it_bset occurs 0,

belnr like bset-belnr,

gjahr like bset-gjahr,

hwbas like bset-hwbas,

txjcd like bset-txjcd,

end of it_bset.

SELECTION-SCREEN BEGIN OF BLOCK B1 WITH FRAME TITLE TEXT-001.

select-options : s_bukrs for bseg-bukrs no intervals no-extension,

s_budat for bsis-budat ,

s_mwskz for bset-mwskz.

SELECTION-SCREEN END OF BLOCK B1.

start-of-selection.

select budat hkont bukrs belnr from bsis

into table it_bsis where budat in s_budat and bukrs in s_bukrs and mwskz

in s_mwskz.

select bukrs kunnr from bsid

into corresponding fields of it_kna1

where bukrs = it_bsis-bukrs.

select bukrs kunnr from bsad

into corresponding fields of it_kna1

where bukrs = it_bsis-bukrs.

select name1 from kna1

into corresponding fields of it_kna1

where kunnr = it_kna1-kunnr.

append it_kna1.

clear it_kna1.

endselect.

Re: how to write select to get data from diff tables

Former Member — Tue, 06 Jun 2006 06:03:49 GMT

Hi,

select budat hkont bukrs belnr from bsis

into table it_bsis where budat in s_budat and bukrs in s_bukrs and mwskz

in s_mwskz.

In this statement, you are getting the values it the table. Try getting them in work area..

select budat hkont bukrs belnr from bsis

into it_bsis where budat in s_budat and bukrs in s_bukrs and mwskz

in s_mwskz.

Regards,

Tanveer.

Please mark helpful answers

Re: how to write select to get data from diff tables

Former Member — Tue, 06 Jun 2006 06:05:10 GMT

select name1 from kna1

into corresponding fields of it_kna1

for all entries in it_bsis.

modify it_kna1.

clear it_kna1.

ur logic seems to be worng y u r not using BSEG table.

Re: how to write select to get data from diff tables

Former Member — Tue, 06 Jun 2006 06:22:42 GMT

Hi friend,

I didnt understood your req correctly but I have ur code done.

DATA : BEGIN OF it_bsis OCCURS 0,

bukrs LIKE bsis-bukrs,

budat LIKE bsis-budat,

hkont LIKE bsis-hkont,

belnr LIKE bsis-belnr,

END OF it_bsis.

DATA : BEGIN OF it_bsid OCCURS 0,

bukrs LIKE bsid-bukrs,

kunnr LIKE bsid-kunnr,

END OF it_bsid.

DATA : BEGIN OF it_bsad OCCURS 0,

bukrs LIKE bsad-bukrs,

kunnr LIKE bsad-kunnr,

END OF it_bsad.

DATA : BEGIN OF it_kna1 OCCURS 0,

kunnr LIKE bsad-kunnr,

name1 LIKE kna1-name1,

END OF it_kna1.

DATA : BEGIN OF it_bset OCCURS 0,

belnr LIKE bset-belnr,

gjahr LIKE bset-gjahr,

hwbas LIKE bset-hwbas,

txjcd LIKE bset-txjcd,

END OF it_bset.

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

SELECT-OPTIONS : s_bukrs FOR bseg-bukrs NO INTERVALS NO-EXTENSION,

s_budat FOR bsis-budat,

s_mwskz FOR bset-mwskz.

SELECTION-SCREEN END OF BLOCK b1.

START-OF-SELECTION.

SELECT budat hkont bukrs belnr FROM bsis

INTO TABLE it_bsis

WHERE budat IN s_budat

AND bukrs IN s_bukrs

AND mwskz IN s_mwskz.

IF NOT it_bsis[] IS INITIAL.

SELECT bukrs kunnr FROM bsid

INTO TABLE it_bsid

FOR ALL ENTRIES IN it_bsis

WHERE bukrs = it_bsis-bukrs

AND mwskz IN s_mwskz

AND augbl NE space.

SELECT bukrs kunnr FROM bsad

INTO TABLE it_bsad

FOR ALL ENTRIES IN it_bsis

WHERE bukrs = it_bsis-bukrs

AND mwskz IN s_mwskz

AND augbl NE space.

IF NOT it_bsid[] IS INITIAL.

SELECT kunnr name1 FROM kna1

INTO TABLE it_kna1

FOR ALL ENTRIES IN it_bsid

WHERE kunnr = it_bsid-kunnr.

ENDIF.

IF NOT it_bsad[] IS INITIAL.

SELECT kunnr name1 FROM kna1

APPENDING TABLE it_kna1

FOR ALL ENTRIES IN it_bsad

WHERE kunnr = it_bsad-kunnr.

ENDIF.

Hope this solves ur purpose.

1 advice dont mind ! ! NEVER NEVER use select endselect atleast on accounting tables ! ! ok

If it helps some points would be helpful

Regards,

Yakub Shah

Re: how to write select to get data from diff tables

Former Member — Tue, 06 Jun 2006 07:40:10 GMT

hi shah,

many thanks for ur reply.

my requirement is i need to get kunnr values from bsad & bsid into single internal table since both tables contains different kunnr values. Atlast i need to display the data which we fetched from bsis , bsad ,bsid &kna1-name using alv list . Pls help me how to acheieve that.

i need to fetch kunnr data from bsad & bsid into single itab based on the data feched data from bsis into it_bsis.

pls let me know. ponts assured.

thanks

vishal

Re: how to write select to get data from diff tables

Former Member — Tue, 06 Jun 2006 07:53:57 GMT

SELECT bukrs kunnr FROM bsid

INTO TABLE it_bsid

FOR ALL ENTRIES IN it_bsis

WHERE bukrs = it_bsis-bukrs

AND mwskz IN s_mwskz

AND augbl NE space.

SELECT bukrs kunnr FROM bsad

INTO TABLE it_bsad

FOR ALL ENTRIES IN it_bsis

WHERE bukrs = it_bsis-bukrs

AND mwskz IN s_mwskz

AND augbl NE space.

loop at it_bsad.

it_bsid-bukrs = it_bsad-bukrs.

it_bsid-kunnr = it_bsad-kunnr.

append it_bsid.

endloop.

IF NOT it_bsid[] IS INITIAL.

SELECT kunnr name1 FROM kna1

INTO TABLE it_kna1

FOR ALL ENTRIES IN it_bsid

WHERE kunnr = it_bsid-kunnr.

ENDIF.

IF NOT it_bsad[] IS INITIAL.

SELECT kunnr name1 FROM kna1

APPENDING TABLE it_kna1

FOR ALL ENTRIES IN it_bsad

WHERE kunnr = it_bsad-kunnr.

ENDIF.

Re: how to write select to get data from diff tables

dani_mn — Tue, 06 Jun 2006 07:58:48 GMT

first pick your data from bsis using bkpf as header table into internal table.

and then use

Select all entries with BSID & BSAD

on belnr, gjahr and bukrs KEY

Regards,

Wasim Ahmed