topic Re: report -without inner join in Application Development and Automation Discussions

report -without inner join

Former Member — Thu, 12 Mar 2009 12:41:45 GMT

hi,

this is report i created in this i used two tables and used inner join to make relation b/t these tables...

so my doubt is ..if we r using three diff.. tables without inner join we can do this report ......if u can.. plz can u show me wit example...

TABLES: VBAP,VBAK.

DATA : BEGIN OF ITAB OCCURS 0,

vbeln like VBAK-VBELN, "Sales Document No.

kunnr like VBAK-KUNNR,"customerno

VKORG like VBAK-VKORG,"salesorg

audat like VBAK-AUDAT,"documentdate

matnr like VBAP-MATNR,"materialno

netwr like VBAK-NETWR,"netamount

end of itab.

selection-screen : begin of block a1. "SCREEN 500 AS WINDOW..

select-options : KUNNR for VBAK-kunnr.

select-options : VKORG FOR VBAK-vkorg.

select-options : AUDAT for VBAK-audat.

select-options : MATNR for VBAP-matnr.

select-options : NETWR FOR VBAK-netwr.

SELECTION-SCREEN :end of block a1.

SELECT vbak_{vbeln vbak}kunnr vbak_{vkorg vbak}audat vbap_{matnr vbak}netwr into table itab from vbak

INNER JOIN vbap on vbak_{vbeln = vbap}vbeln

where

VBAK~kunnr in kunnr

AND

VBAK~VKORG in VKORG

and vbak~audat in audat

AND vbap~matnr in matnr.

write sy-dbcnt.

write:/ 'CUSTOMERNO' ,20 'SALESORG',40 'DOCUMENTDATE',60 'MATERIAL',80 'NETAMOUNT'.

ULINE.

loop at itab.

write 😕 itab-kunnr,20 itab-vkorg, 40 itab-audat, 60 itab-matnr, 80 itab-netwr.

endloop.

thanx

bhanu

Re: report -without inner join

Former Member — Thu, 12 Mar 2009 12:45:01 GMT

yes, you can have 3 tables in inner join, otherwise you can go for For all entires clause and go for indiviudal table selects.

combine them at the end.

Re: report -without inner join

Former Member — Thu, 12 Mar 2009 12:46:25 GMT

Hi Bhanu,

Use for all entries in this way.

DATA:

t_mara LIKE STANDARD TABLE OF fs_material,

t_marc LIKE STANDARD TABLE OF fs_material1.

SELECT matnr

mtart

INTO CORRESPONDING FIELDS OF TABLE t_mara

FROM mara

WHERE mtart = p_mtart .

SELECT matnr

werks

FROM marc

INTO TABLE t_marc FOR ALL ENTRIES

IN t_mara

WHERE matnr = t_mara-matnr.

This is for 2 table.Use the same logic for 3 table.Take another internal table and do it in the same way.

Much Regards,

Amuktha.

Re: report -without inner join

viquar_iqbal — Thu, 12 Mar 2009 12:50:37 GMT

we can use FOR ALL ENTRIES IN

first we get the data into a table itab .

Re: report -without inner join

former_member156446 — Thu, 12 Mar 2009 12:51:04 GMT

Refer to this psuedo code

SELECT vbak~vbeln vbak~kunnr vbak~vkorg vbak~audat vbap~matnr vbak~netwr into table itab from vbak
INNER JOIN vbap on vbak~vbeln = vbap~vbeln
where
VBAK~kunnr in kunnr
AND
VBAK~VKORG in VKORG
and vbak~audat in audat
AND vbap~matnr in matnr.

SELECT vbak~vbeln vbak~kunnr vbak~vkorg vbak~audat vbak~netwr vbak~matnr
             into table itab
from vbak
where kunnr in kunnr....


select vbeln matnr 
      into table itab2
from vbap 
for all entries in itab
where vbap~matnr = itab~matnr....

loop at itab assigning <fs_itab>.
 read table itab2 into wa_itab2 with key vbeln = <fs_itab>-vbeln
                                                         matnr = <fs_itab>-matnr.

" your move's to final table
endloop.

Re: report -without inner join

Former Member — Thu, 12 Mar 2009 12:57:22 GMT

Hi Bhanu,

Lets assume itab1, itab2 and itab3 are int tables with x,y and z fields. ANd z1, z2 and z3 are databse tables.

Here is the code:


select x y z from z1 into table itab1

IF not itab1 not initial.
select x y z from z2 into table itab2
 for all entries in itab1
  where x = itab1-x.
ENDIF.

IF not itab1 not initial.
select x y z from z3 into table itab3
 for all entries in itab2
  where x = itab2-x.
ENDIF.

Regards,

Sachin