topic Re: joins in Application Development and Automation Discussions

joins

Former Member — Sat, 22 Dec 2007 07:54:28 GMT

Hi all,

In a report program i have used a inner join. I am getting error in the where condition. Here I have furnished part of my code for ur reference.

Tables : mara, makt, mard.

data : begin of itab occurs 0,

matnr like mara-matnr,

maktx like makt-maktx,

lgort like mard-lgort,

labst like mard-labst,

end of itab.

parameters : matnr like mara-matnr.

start-of-selection.

select mara_{matnr makt}maktx mard_{lgort mard}labst into corresponding fields of iab from mara inner join maktx on mara_{matnr = makt}matnr inner join mard on makt_{matnr = mard}matnr where matnr eq matnr.

in the where condition I am getting the following error message.

matnr has two meanings in data dictionary.

Please help me in sort out this problem and also tell me more abt inner joins.

Re: joins

Former Member — Sat, 22 Dec 2007 08:48:21 GMT

Hi,

Try this

Tables : mara, makt, mard.

data : begin of itab occurs 0,

matnr like mara-matnr,

maktx like makt-maktx,

lgort like mard-lgort,

labst like mard-labst,

end of itab.

parameters : s_matnr like mara-matnr.

start-of-selection.

select mara_{matnr makt}maktx mard_{lgort mard}labst into corresponding fields of itab from ((mara inner join makt on mara_{matnr = makt}matnr) inner join mard on makt_{matnr = mard}matnr )where matnr eq s_matnr.

Re: joins

Former Member — Sat, 22 Dec 2007 09:12:50 GMT

HI Sashi,

Select A_{MATNR B}MAKTX C_{LGORT C}LABST INTO CORRESPONDING FIELDS OF TABLE ITAB FROM (MARA AS A INNER JOIN MAKT AS B ON A_{MATNR = B}MATNR) INNER JOIN MARD AS C ON A_{MATNR = C}MATNR WHERE MATNR = P_MATNR.

Reward if useful

Regards

ANUPAM

Re: joins

paruchuri_nagesh — Sat, 22 Dec 2007 09:21:21 GMT

here iam giving example program

REPORT ZINNERJOINS no standard page heading line-count 60(4).

TABLES : LFA1,EKKO,EKPO.

SELECT-OPTIONS : S_LIFNR FOR LFA1-LIFNR.

DATA : BEGIN OF ITAB OCCURS 0,

LIFNR LIKE LFA1-LIFNR,

NAME1 LIKE LFA1-NAME1,

EBELN LIKE EKKO-EBELN,

AEDAT LIKE EKKO-AEDAT,

EBELP LIKE EKPO-EBELP,

MENGE LIKE EKPO-MENGE,

END OF ITAB.

SELECT LFA1_{LIFNR LFA1}NAME1 EKKO_{EBELN EKKO}AEDAT EKPO_{EBELP EKPO}MENGE INTO TABLE ITAB FROM LFA1 INNER JOIN EKKO ON LFA1_{LIFNR = EKKO}LIFNR

INNER JOIN EKPO ON EKKO_{EBELN = EKPO}EBELN WHERE LFA1~LIFNR IN S_LIFNR.

LOOP AT ITAB.

WRITE:/ ITAB-LIFNR,ITAB-NAME1,ITAB-EBELN,ITAB-AEDAT,ITAB-EBELP,ITAB-MENGE.

ENDLOOP.

top-of-page.

write : / 'this is vendors report'.

i think u have to write where mara~matnr eq matnr

Reward if u find useful

Regards

Ngesh.Paruchuri

Re: joins

Former Member — Sat, 22 Dec 2007 13:11:38 GMT

Hi,

No expert on inner joins, but this is how I do it...

select a_{matnr b}maktx c_{lgort c}labst into corresponding fields of iab from

( ( mara as a

inner join makt as b on a_{matnr = b}matnr )

inner join mard as c on a_{matnr = c}matnr )

where a~matnr = {where condition}.

The where condition is usually something outside the select statement, i.e. a passed variable, etc.

Regards,

Satish.