Inner join on bkpf and bsid

[Former Member]

Hi Experts ,

I want to do an inner join on bsid and bkpf tables in the select query . I want to order the result on the basis of cputm field in bkpf , that is why inner join on bkpf is req , rest all the fields are from bsid . But the requirement is that we should not select the cputm field in the select , it is only to be used for sorting the result of the select query . we are also using select * from bsid where ...... endselect . and not specific fields from bsid currently . Kindly let me know how to use the inner join on bkpf and bsid in this case .

Would reward all helpful answers .

Thanks

[former_member402443]

Hi Ankit,

First make a structure of the required fields.

TABLES : bkpf,bsid.

TYPES : BEGIN OF x_tab,

belnr like bkpf-belnr,

kunnr like bsid-kunnr,

bldat like bsid-bldat,

budat like bsid-budat,

waers like bsid-waers,

END OF x_tab.

DATA : itab type STANDARD TABLE OF x_tab,

wa_itab TYPE x_tab.

start-of-SELECTION.

SELECT bkpf_{belnr bsid}kunnr bsid_{bldat bsid}budat bsid~waers into table itab

FROM bkpf INNER JOIN bsid on bkpf_{belnr = bsid}belnr and

bkpf_{bukrs = bsid}bukrs and

bkpf_{gjahr = bsid}gjahr.

Rewards Points, if useful.

Regards

Manoj Kumar

Edited by: Manoj Kumar on Dec 26, 2007 2:36 PM

[Former Member]

Hello Ankit,

I think you know how to have a inner join

use any of the these BUKRS

BELNR

GJAHR for condition.

after you get the result

sort the internal table by the field.

[Former Member]

Hi,

You need to select the specified fields from BSID.

You can not use Select * clause in case of Inner Join. See the same query :

data : gt_bsid like bsid occurs 0 with header line.

select a_{bukrs a}gjahr

................

......... b~CPUTM

into table gt_bsid

from BSID as a

inner join BKPF as b

on a_{bukrs eq b}BUKRS

and .....

....

where a~BUKRS eq '0001'

and ......

You can put join based upon the common fields in both the tables BSID and BKPF.

Once you get the data sort the internal table based on CPUTM field of BKPF and proceed with further processing.

Hope this would help you.

Regards,

lalit

[Former Member]

In the inner join after where class can you try with Order by Class?

Eg:order by a~cputm.

Regards,

Sathishkumar.V

[Former Member]

hi

good

check this

good rule is to join from more to less record, i.e.

codeselect vkan_{vbeln vbap}posnr vbap~menge

into corresponding fields of table ...

from vbap

join vbak

on vbap~vbeln

= vbak~vbeln

where ...

[/code]

thanks

mrutyun^