topic Re: inner join in Application Development and Automation Discussions

inner join

Former Member — Wed, 11 Apr 2007 10:24:50 GMT

SELECT AUFNR OBJNR BUKRS FROM AUFK

INTO CORRESPONDING FIELDS OF TABLE I_AUFK

WHERE BUKRS = P_BUKRS.

SELECT OBJNR STAT FROM JEST

INTO CORRESPONDING FIELDS OF TABLE I_JEST_IN

FOR ALL ENTRIES IN I_AUFK

WHERE OBJNR = I_AUFK-OBJNR

AND STAT EQ R_STAT1

AND STAT NE R_STAT2.

Hi ,

I want to join the above two table ...and then i will

SELECT AUFNR MATNR DWERK FROM AFPO

INTO CORRESPONDING FIELDS OF TABLE I_AFPO

WHERE OBJNP = I_JEST_IN-OBJNR.

I am getting error , Pls solve

Sabya

Re: inner join

anversha_s — Wed, 11 Apr 2007 10:28:08 GMT

hi,

try like this.

SELECT AUFNR OBJNR BUKRS FROM AUFK
INTO CORRESPONDING FIELDS OF TABLE I_AUFK
WHERE BUKRS = P_BUKRS.

if  I_AUFK[] is not initial. " use this.

SELECT OBJNR STAT FROM JEST
INTO CORRESPONDING FIELDS OF TABLE I_JEST_IN
FOR ALL ENTRIES IN I_AUFK
WHERE OBJNR = I_AUFK-OBJNR
AND STAT EQ R_STAT1
AND STAT NE R_STAT2.

endif.

Rgds

Anver

Re: inner join

Former Member — Wed, 11 Apr 2007 10:28:34 GMT

Hi,

SELECT a_{AUFNR a}OBJNR a_{BUKRS b}STAT

INTO CORRESPONDING FIELDS OF TABLE I_JOIN_ITAB

FROM AUFK as a join JEST as b

on a_{objnr = b}objnr

WHERE a~BUKRS = P_BUKRS

AND b~STAT EQ R_STAT1

AND b~STAT NE R_STAT2.

reward points if useful

regards,

ANJI

Re: inner join

Former Member — Wed, 11 Apr 2007 10:31:29 GMT

Try this way...

SELECT a~AUFNR a~OBJNR a~BUKRS b~STAT
INTO CORRESPONDING FIELDS OF TABLE I_JOIN_ITAB
FROM AUFK as a 
join JEST as b
on a~objnr = b~objnr
WHERE a~BUKRS = P_BUKRS
AND b~STAT EQ R_STAT1
AND b~STAT NE R_STAT2

Re: inner join

Former Member — Wed, 11 Apr 2007 10:35:02 GMT

SELECT AUFNR OBJNR STAT

INTO CORRESPONDING FIELDS OF TABLE ITAB

FROM AUFK as A

inner join JEST as B

ON A_{OBJNR = b}OBJNR

WHERE BUKRS = P_BUKRS AND

STAT EQ R_STAT1

AND STAT NE R_STAT2.

u can go for seperate select instead of Joins.

Regards,

madhu

Re: inner join

Former Member — Wed, 11 Apr 2007 10:38:46 GMT

try this

select aufk_{aufnr aufk}objnr aufk_{bukrs jest}objnr jest_{stat afpo}matnr afpo_{dwerk into table it_detail from aufk inner join jest on aufk}objnr = jest~objnr

inner join afpo on jest_{objnr = afpo}objnp

where aufk~bukrs = p_bukrs

and jest~stat = r_stat1

and jest~stat <> r_stat2.

create a table(it_detail) with all the above fields and use it.

Re: inner join

Former Member — Wed, 11 Apr 2007 10:45:18 GMT

hI..

SELECT AUFNR OBJNR BUKRS STAT

FROM AUFK INNER JOIN JEST

INTO TABLE ITAB

ON AUFK_{OBJNR = JEST}OBJNR WHERE

OBJNR = I_AUFK-OBJNR AND

STAT = R_STAT1 AND

STAT <> R_STAT2 AND

BUKRS = P_BUKRS.

***iNNER JOIN ALWAYS BETTER FOR 2 TABLES.

fOR 3 RD TABLE GO WITH FOR ALL ENTRIES.

IF SY-SUBRC EQ 0.

SELECT AUFNR MATNR DWERK FROM AFPO

INTO CORRESPONDING FIELDS OF TABLE I_AFPO

FOR ALL ENTRIES IN ITAB

WHERE OBJNP = ITAB-OBJNR.

ENDIF.