topic Re: using inner join in Application Development and Automation Discussions

using inner join

Former Member — Thu, 08 Feb 2007 05:14:19 GMT

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.

Re: using inner join

Former Member — Thu, 08 Feb 2007 05:18:52 GMT

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

Re: using inner join

Former Member — Thu, 08 Feb 2007 05:22:17 GMT

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

Re: using inner join

Former Member — Thu, 08 Feb 2007 05:23:09 GMT

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

Re: using inner join

Former Member — Thu, 08 Feb 2007 05:32:25 GMT

This code gives me error

Re: using inner join

Former Member — Thu, 08 Feb 2007 05:34:23 GMT

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

Re: using inner join

Former Member — Thu, 08 Feb 2007 05:39:36 GMT

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.

Re: using inner join

Former Member — Thu, 08 Feb 2007 05:40:15 GMT

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^

Re: using inner join

Former Member — Thu, 08 Feb 2007 05:43:01 GMT

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,

Re: using inner join

Former Member — Thu, 08 Feb 2007 06:03:30 GMT

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

Re: using inner join

Former Member — Thu, 08 Feb 2007 06:06:15 GMT

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.

Re: using inner join

Former Member — Thu, 08 Feb 2007 06:11:40 GMT

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

Re: using inner join

Former Member — Thu, 08 Feb 2007 06:41:48 GMT

yes exactly

Re: using inner join

Former Member — Thu, 08 Feb 2007 06:43:01 GMT

Hi,

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

Thanks,

Naren

Re: using inner join

Former Member — Thu, 08 Feb 2007 07:02:08 GMT

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.