topic Re: select join in Application Development and Automation Discussions

select join

sap_cohort — Mon, 23 Oct 2006 23:35:49 GMT

I want to create a select join on 2 tables and I want to select ALL of the fields from the 1st table(without having to list them) and just a few (I'll list them) from the 2nd table. Is this possible?

Thanks!

Re: select join

Former Member — Mon, 23 Oct 2006 23:45:27 GMT

Hi,

Yes...It is possible..You can use dynamic fields selection..

Check this example..

PARAMETERS: P_MATNR TYPE MATNR.

DATA: BEGIN OF ITAB OCCURS 0.

INCLUDE STRUCTURE MARA.

DATA: WERKS LIKE MARC-WERKS,

END OF ITAB.

DATA: BEGIN OF DY_FIELDS OCCURS 0,

FIELD(61),

END OF DY_FIELDS.

DATA: FIELDS LIKE DFIES OCCURS 0 WITH HEADER LINE.

CALL FUNCTION 'DDIF_NAMETAB_GET'

EXPORTING

tabname = 'MARA'

TABLES

DFIES_TAB = FIELDS

EXCEPTIONS

NOT_FOUND = 1

OTHERS = 2

IF sy-subrc <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

LOOP AT FIELDS.

CLEAR: DY_FIELDS.

CONCATENATE 'A~' FIELDS-FIELDNAME INTO DY_FIELDS-FIELD.

APPEND DY_FIELDS.

ENDLOOP.

DY_FIELDS = 'B~WERKS'.

APPEND DY_FIELDS.

SELECT (DY_FIELDS)

INTO TABLE ITAB

FROM MARA AS A INNER JOIN MARC AS B

ON A_{MATNR = B}MATNR

WHERE A~MATNR = P_MATNR.

CHECK SY-SUBRC = 0.

Thanks,

Naren

Re: select join

Former Member — Tue, 24 Oct 2006 02:21:02 GMT

select ... left out join ?

Re: select join

Former Member — Tue, 24 Oct 2006 04:40:33 GMT

good

using JOIN statement you can do that.

go through this example and use accordingly

select aufk_{mandt aufk}aufnr aufk_{objnr aufk}autyp

aufk_{werks aufk}auart aufk~ktext

aufk_{flg_mltps aufk}bukrs aufk_{zschl aufk}procnr "I_40C1

aufk_{prctr makt}maktx "I_46A

afpo_{posnr afpo}objnp "I_40C1

afpo_{matnr afpo}pwerk

afpo_{wemng afpo}psmng afpo~amein

into corresponding fields of table t_auftrag

from ( aufk inner join afpo

on aufk_{aufnr = afpo}aufnr

) "I_40C1

and afpo~posnr = '0001' ) "D_40C1

left outer join makt on"I_46A

afpo_{matnr = makt}matnr and "I_46A

makt~spras = sy-langu "I_46A

where aufk~autyp in abgrauty

and aufk~werks in abgrwerk

and aufk~auart in abgraufa

and aufk~aufnr in abgraufn

and aufk~kokrs eq kokrs

and aufk~loekz eq space

and aufk~pkosa eq space "I40C_PK

and aufk~abgsl ne space.

thanks

mrutyun^

Re: select join

sap_cohort — Tue, 24 Oct 2006 12:25:52 GMT

Is it not possible to have something like the following or do you just have to specify all of the individual fields when your using the join?

select A~* B~Field1 B~Field2 B~field3.

Re: select join

Former Member — Tue, 24 Oct 2006 12:30:18 GMT

hi,

use the same one it will work for example

SELECT R~MATNR "R = MARA

R~VPSTA

R~MTART

R~MATKL

R~MBRSH

R~MEINS

R~GEWEI

R~RAUBE

R~SPART

R~PRDHA

R~MAGRV

C~MATNR "C = MARC

C~WERKS

C~PSTAT

C~PLIFZ

C~SSQSS

C~LADGR

C~SERNP

C~PERIV

C~STAWN

C~HERKL

C~EKGRP

C~DISMM

C~DISPO

C~DISLS

C~LGPRO

C~FHORI

C~DZEIT

C~BWSCL

C~AWSLS

C~LGFSB

INTO CORRESPONDING FIELDS OF TABLE IT_TOTAL

FROM ( MARA AS R

INNER JOIN MARC AS C ON C_{MATNR = R}MATNR )

WHERE R~MATNR IN R_MATNR AND

R~MTART IN S_MTART AND

C~WERKS IN R_WERKS .

Re: select join

sap_cohort — Tue, 24 Oct 2006 12:40:26 GMT

Sorry, I'm not following you. I want to select all of the fields of the 1st table without specifying them individually like you have. Can't we somehow say select all fields from the 1st table and then specify individually the fields I need from the 2nd table?

Thanks!

Re: select join

RichHeilman — Tue, 24 Oct 2006 12:59:09 GMT

Kenneth, that is not support by ABAP, as mentioned earily you can use a dynamic field list, or you can simply say....

select <b>* into corresponding fields</b> of table itab.

If you want all of the fields of the first table, then just use *,

Regards,

Rich Heilman

Re: select join

Former Member — Tue, 24 Oct 2006 13:01:34 GMT

Hi Kenneth,

I think you cannot do that.

YOu have to go for two different selects using For all entries.

select *

from <table1>

into itab1

where <where cond>.

if not itab1[] is initial.

select field1

field2

field3

from <table2>

into table itab2

for all entries in itab1

where field1 = itab1-field1

field2 = itab1-field2

endif.

Loop at itab2.

read table itab1 with key field1 = itab2-field1

field2 = itab2-field2.

if sy-subrc = 0.

move-correspoding itab2 to it_final.

move-correspoding itab1 to it_final.

append it_final.

clear it_final.

endif.

endloop.

Regards,

ravi

Re: select join

Former Member — Tue, 24 Oct 2006 14:55:27 GMT

Hi,

You cannot do the A~*, as sap will not recognize it..You can use the dynamic field selection as I mentioned above..

Thanks,

Naren

Re: select join

sap_cohort — Thu, 14 Dec 2006 20:00:56 GMT

Question Answered.. No supported.