2006 Sep 28 7:09 PM
Hi friends,
Im developing a report and have almost done. But my inner join query is not fetching records correctly.ive a selection screen with following inputs.
1. docno select-options (like bkpf-belnr)
2.doctyp select-options (like bkpf-blart)
3.date select-options (like bkpf-cpudt)
4.user parameter (like bkpf-usnam)
5. refdocno select-options (like bkpf-xblnr)
I need following output in my report.
1.Document Number BKPF- BELNR
2.Reference Document Number BKPF- XBLNR
3.Entered By BKPF- CPUDT
4.Account Type BSEG- KOART
5.Cost Center BSEG- KOSTL
6.WBS Element BSEG- PROJK
7.Vendor Number BSEG- LIFNR
8.Debit / Credit Indicator BSEG- SHKZG
9.Amount BSEG- WRBTR
10.Vendor Name BSEC- NAME1
11.Name 2 BSEC- NAME2
12.Name 3 BSEC- NAME3
13.Vendor Address BSEC- STRAS
14.Vendor City BSEC- ORT01
15.Vendor State / Province BSEC- REGIO
16.Vendor Zip Code BSEC- PSTLZ
Please help me as it urgent. Our go-live is next week. I promise I will award points.
-Ashley
2006 Sep 28 7:13 PM
HI Ashley,
My adivce if u stuck in inner join statement and it is so urgent then go for normal select , for all entries and loop statements until u get the correct combination of joins..
but i am not sure that u can join those tables..
regards
Follow the below logic..as it is not possible to do inner join with BSEG.
SELECT fields from BKPF into table itab
where filter conditions.
if itab[] is not initial.
select fields for bseg into table ibseg
for all enteries in itab
from bseg where filter = itab-fields.
similarly do for bsec
endif.
loop at itab
read ibseg
read ibsec
endloop.
2006 Sep 28 7:13 PM
HI Ashley,
My adivce if u stuck in inner join statement and it is so urgent then go for normal select , for all entries and loop statements until u get the correct combination of joins..
but i am not sure that u can join those tables..
regards
2006 Sep 28 7:37 PM
You cannot join with BSEG and BSEG as they are cluster tables. Select from BKPF into a table. Loop at that table selecting from BSEG and BSEC independently to build your report table.
2006 Sep 28 7:57 PM
Try something like this:
TABLES: BKPF.
SELECT-OPTIONS: DOCNO FOR BKPF-BELNR,
DOCTYP FOR BKPF-BLART,
DATE FOR BKPF-CPUDT,
USER FOR BKPF-USNAM,
REFDOCNO FOR BKPF-XBLNR.
DATA: BEGIN OF T_BKPF OCCURS 0,
BUKRS LIKE BKPF-BUKRS,
BELNR LIKE BKPF-BELNR,
GJAHR LIKE BKPF-GJAHR,
XBLNR LIKE BKPF-XBLNR,
CPUDT LIKE BKPF-CPUDT,
END OF T_BKPF.
DATA: BEGIN OF T_BSEG OCCURS 0,
BUKRS LIKE BSEG-BUKRS,
BELNR LIKE BSEG-BELNR,
GJAHR LIKE BSEG-GJAHR,
KOART LIKE BSEG-KOART,
KOSTL LIKE BSEG-KOSTL,
PROJK LIKE BSEG-PROJK,
LIFNR LIKE BSEG-LIFNR,
SHKZG LIKE BSEG-SHKZG,
WRBTR LIKE BSEG-WRBTR,
END OF T_BSEG.
DATA: BEGIN OF T_BSEC OCCURS 0,
BUKRS LIKE BSEC-BUKRS,
BELNR LIKE BSEC-BELNR,
GJAHR LIKE BSEC-GJAHR,
NAME1 LIKE BSEC-NAME1,
NAME2 LIKE BSEC-NAME2,
NAME3 LIKE BSEC-NAME3,
STRAS LIKE BSEC-STRAS,
ORT01 LIKE BSEC-ORT01,
REGIO LIKE BSEC-REGIO,
PSTLZ LIKE BSEC-PSTLZ,
END OF T_BSEC.
SELECT BUKRS BELNR GJAHR XBLNR CPUDT
INTO TABLE T_BKPF
FROM BKPF
WHERE BELNR IN DOCNO
AND BLART IN DOCTYP
AND CPUDT IN DATE
AND USNAM IN USER
AND XBLNR IN REFDOCNO.
CHECK NOT T_BKPF[] IS INITIAL.
SELECT BUKRS BELNR GJAHR KOART KOSTL PROJK LIFNR SHKZG WRBTR
INTO TABLE T_BSEG
FROM BSEG
FOR ALL ENTRIES IN T_BKPF
WHERE BUKRS = T_BKPF-BUKRS
AND BELNR = T_BKPF-BELNR
AND GJAHR = T_BKPF-GJAHR.
SELECT BUKRS BELNR GJAHR NAME1 NAME2 NAME3 STRAS ORT01 REGIO PSTLZ
INTO TABLE T_BSEC
FROM BSEC
FOR ALL ENTRIES IN T_BKPF
WHERE BUKRS = T_BKPF-BUKRS
AND BELNR = T_BKPF-BELNR
AND GJAHR = T_BKPF-GJAHR.
SORT: T_BKPF, T_BSEG, T_BSEC.
LOOP AT T_BKPF.
WRITE: / T_BKPF-BELNR,
T_BKPF-XBLNR,
T_BKPF-CPUDT.
LOOP AT T_BSEG WHERE BUKRS = T_BKPF-BUKRS
AND BELNR = T_BKPF-BELNR
AND GJAHR = T_BKPF-GJAHR.
WRITE: /10 T_BSEG-KOART,
T_BSEG-KOSTL,
T_BSEG-PROJK,
T_BSEG-LIFNR,
T_BSEG-SHKZG,
T_BSEG-WRBTR.
ENDLOOP.
LOOP AT T_BSEC WHERE BUKRS = T_BKPF-BUKRS
AND BELNR = T_BKPF-BELNR
AND GJAHR = T_BKPF-GJAHR.
WRITE: /10 T_BSEC-NAME1,
T_BSEC-NAME2,
T_BSEC-NAME3,
T_BSEC-STRAS,
T_BSEC-ORT01,
T_BSEC-REGIO,
T_BSEC-PSTLZ.
ENDLOOP.
ENDLOOP.
2006 Sep 28 9:29 PM
Follow the below logic..as it is not possible to do inner join with BSEG.
SELECT fields from BKPF into table itab
where filter conditions.
if itab[] is not initial.
select fields for bseg into table ibseg
for all enteries in itab
from bseg where filter = itab-fields.
similarly do for bsec
endif.
loop at itab
read ibseg
read ibsec
endloop.
2006 Oct 02 9:19 PM
hi friends,
Thank you for that quick reply. It was especially helpful.Points are awarded.
thanks
Ashli
| User | Count |
|---|---|
| 6 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 | |
| 1 |