select join

[sap_cohort]

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!

[Former Member]

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

[Former Member]

select ... left out join ?

[Former Member]

hi

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^

[sap_cohort]

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.

[Former Member]

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 .

[sap_cohort]

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!

[RichHeilman]

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

[Former Member]

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

[Former Member]

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

[sap_cohort]

Question Answered.. No supported.