using inner join

[Former Member]

How to use inner join instead of select stat. so that I get all the required fields form both my tables link (lgnum ivnum lgtyp and uname) and linp (lgnum ivnum ivpos lgpla and idatu).

Thanks.

[Former Member]

select link_{lgnum link}ivnum link_{lgtyp link}uname linp_{ivpos linp}lgpla linp_{idatu FROM link inner join linp on link}lgnum = linp~lgnum

and link_{ivnum = link}ivnum

into TABLE it_tab1

********Changes in where condition

where link~lgnum = pa_lgnum AND

link~ivnum IN s_ivnum.

[Former Member]

Hi,

Check this example..

DATA: BEGIN OF ITAB OCCURS 0,

LGNUM TYPE LINK-LGNUM,

IVNUM TYPE LINK-IVNUM,

IVPOS TYPE LINP-IVPOS,

END OF ITAB.

SELECT A_{LGNUM A}IVNUM B~IVPOS

INTO TABLE ITAB

FROM LINK AS A INNER JOIN LINP AS B

ON A_{LGNUM = B}LGNUM

AND A_{IVNUM = B}IVNUM

WHERE A~LGNUM = '11221'.

THanks,

Naren

[Former Member]

Use below code:

select link_{lgnum link}ivnum link_{lgtyp link}uname linp_{ivpos linp}lgpla linp_{idatu FROM link inner join linp on link}lgnum = linp~lgnum

and link_{ivnum = link}ivnum

into TABLE it_tab1 where lgnum = pa_lgnum AND ivnum IN s_ivnum.

Thanks

[Former Member]

This code gives me error

[Former Member]

Shaheen,

In the given code I am assuming you have used below lines in your code from your previous threads:

PARAMETERS: pa_lgnum LIKE LINK-LGNUM.

SELECT-OPTIONS: s_ivnum FOR LINK-IVNUM.

If this used and still you are getting any error, please tell me the error description.

Thanks.

[Former Member]

yes it's used. this is the error colunm name has two meanings

[Former Member]

select link_{lgnum link}ivnum link_{lgtyp link}uname linp_{ivpos linp}lgpla linp_{idatu FROM link inner join linp on link}lgnum = linp~lgnum

and link_{ivnum = link}ivnum

into TABLE it_tab1

********Changes in where condition

where link~lgnum = pa_lgnum AND

link~ivnum IN s_ivnum.

[Former Member]

Shaheen,

Please check if the internal table have below fields:

DATA: begin of it_tab1 occurs 0,

lgnum like link-lgnum,

ivnum like link-ivnum ,

lgtyp like link-lgtyp,

uname like link-uname ,

ivpos like linp-ivpos ,

lgpla like linp-lgpla ,

idatu like linp-idatu ,

end of it_tab1.

Please confirm.

Thanks

[Former Member]

yes exactly

[Former Member]

Hi

Both ur tables are header and item.

it is better to use JOIN instead of for all entries

performance will be very good in this case

Regards

Shiva

[Former Member]

Hi,

Did you check my code..It is not giving any error..

DATA: BEGIN OF ITAB OCCURS 0,

LGNUM TYPE LINK-LGNUM,

IVNUM TYPE LINK-IVNUM,

IVPOS TYPE LINP-IVPOS,

END OF ITAB.

SELECT A_{LGNUM A}IVNUM B~IVPOS

INTO TABLE ITAB

FROM LINK AS A INNER JOIN LINP AS B

ON A_{LGNUM = B}LGNUM

AND A_{IVNUM = B}IVNUM

WHERE A~LGNUM = '11221'.

Thanks,

Naren

[Former Member]

hi

good

check this code and use accordingly

SELECT c~matnr "Material no.

• c~werks "Plant

• c~lvorm "Flag Mat.-Del. at Plant level

• c~bwtty "Valuation category

• c~kzkri "Indicator: Gritical part

• c~dispr "Material: MRP profile

• ADD Mike Krepcik TD 3363 02/01/05 D10K913640

• c~dismm "MRP type

• END TD 3363 02/01/05 D10K913640

• c~plifz "Planned delivery time in days

• c~perkz "Period indicator

• c~minbe "Reorder point

• c~mabst "Maximum stock level

• c~umlmc "Stock in Tran. (plant to plant)

• a~mtart "Material type

• a~meins "Base unit of measure

• k~spras "Language key

• k~maktx "Material description

• w~bwkey "Valuation area

• w~bwtar "Valuation type

• w~lbkum "Total value stock

• w~salk3 "Value of total valuated stock

• w~verpr "Moving ave. price/periodic unit

• w~peinh "Price unit ADD D10K913194

• FROM marc AS c LEFT OUTER JOIN mara AS a

• ON c_{matnr = a}matnr

• LEFT OUTER JOIN makt AS k

• ON c_{matnr = k}matnr

• LEFT OUTER JOIN mbew AS w

• ON c_{matnr = w}matnr AND

• c_{werks = w}bwkey

• INTO TABLE i_splus

• WHERE c~matnr IN s_matnr "Material no.

• AND c~werks IN s_werks. "Plant

thanks

mrutyun^

[Former Member]

Hi shaheen

create a <b>database view of these 2 tables.</b>

and write a select statement on this view.

it meets your requirement and also improves performance.

Regards,

MB

[Former Member]

Hi,

Please post your select statement and your internal table declaration..So that it will be easier for us identify the error..

Thanks,

Naren

[Former Member]

hi

plz chk this code

tables : link,linp.

DATA: begin of it_tab1 occurs 0,

lgnum like link-lgnum,

ivnum like link-ivnum ,

lgtyp like link-lgtyp,

uname like link-uname ,

ivpos like linp-ivpos ,

lgpla like linp-lgpla ,

idatu like linp-idatu ,

end of it_tab1.

*select link_{lgnum link}ivnum link_{lgtyp link}uname linp_{ivpos linp}lgpla linp_{idatu FROM link inner join linp on link}lgnum = linp~lgnum

*and link_{ivnum = link}ivnum

*into TABLE it_tab1 where lgnum = pa_lgnum AND ivnum IN s_ivnum.

select a_{lgnum a}ivnum a_{lgtyp a}uname b_{ivpos b}lgpla b~idatu into table it_tab1

FROM link as a inner join linp as b

on a_{lgnum = b}lgnum and a_{ivnum = b}ivnum.

regards.