topic Re: Select with join from the same fields into two different fields in Application Development and Automation Discussions

Select with join from the same fields into two different fields

Former Member — Fri, 09 Feb 2007 16:09:45 GMT

Hi!

Is it possible to solve, in one SELECT command?

I would like join two tables (AUFK and VAFILOA), and both tables have the same field (GSBER). This field conatins two different values in the two table, because in AUFK it is the source, and in VAFILOA it is the receiver. The tables are connected by AUFNR.

I would like to know, can I read with ONE SELECT/JOIN into two different fields of an internal table (for example: GSBER_SOURCE and GSBER_RECEIVER).

Thanx in advance

Tamá

Re: Select with join from the same fields into two different fields

Former Member — Fri, 09 Feb 2007 16:14:38 GMT

Hi tamas,

It is very much possible:

I tried this example:

data: v_gsber1 type aufk-gsber,

v_gsber2 type VAFILOA-gsber,

v_aufnr type aufk-aufnr.

select single aufk~GSBER

VAFILOA~gsber

from aufk inner join VAFILOA

on aufk_{aufnr = VAFILOA}aufnr

into (v_gsber1 , v_gsber2)

where aufk~aufnr = v_aufnr.

Regards,

Ravi

Re: Select with join from the same fields into two different fields

Former Member — Fri, 09 Feb 2007 16:15:31 GMT

Yes, you can. Select GSBER from two tables with alias and put it into GSBER_SOURCE and GSBER_RECEIVER of internal table.

Thanks,

Santosh

Re: Select with join from the same fields into two different fields

Former Member — Fri, 09 Feb 2007 16:19:22 GMT

sure you can do this. First declare you itab, then do a join in your select

select aufk_{gsber vafiloa}gsber

from aufk

join vafiloa

on aufk_{AUFNR = vafiloa}AUFNR

into table itab.

Re: Select with join from the same fields into two different fields

Former Member — Fri, 09 Feb 2007 16:19:46 GMT

Hi!

Well, not only these fields are selected, so my select seems like this now...

As you might see it is using corresponding fields. Is it possible to solve this task this way?



    SELECT aufk~auart afko~aufnr aufk~ktext aufk~objnr afko~aufpl
           vafiloa~iloan iloa~tplnr vafiloa~equnr aufk~kostv
           vafiloa~anlnr vafiloa~anlun aufk~bukrs aufk~kostl
           aufk~zzkocsiszam aufk~werks iloa~gsber
             FROM aufk
             INNER JOIN afih ON afih~aufnr = aufk~aufnr
             INNER JOIN afko ON afko~aufnr = aufk~aufnr
             INNER JOIN vafiloa ON afko~aufnr = vafiloa~aufnr
             INNER JOIN iloa ON iloa~iloan = vafiloa~iloan
             INTO CORRESPONDING FIELDS OF TABLE gt_rendeles
           FOR ALL ENTRIES IN gt_rendeles_teny
           WHERE aufk~auart IN s_auart
             AND aufk~bukrs IN s_bukrs
             AND aufk~kostv IN s_kostv
             AND aufk~kostl IN s_kostl
             AND aufk~werks IN s_werks
             AND aufk~zzkocsiszam IN s_kocsi
             AND vafiloa~equnr IN s_equnr  "berendezés
             AND iloa~tplnr IN s_tplnr     "m&#369;szaki hely
             AND aufk~objnr = gt_rendeles_teny-objnr. "ténykönyvelés van

Thanx

Tamá

Re: Select with join from the same fields into two different fields

Former Member — Fri, 09 Feb 2007 16:23:40 GMT

Hi Tamás,

Unfortunately you cannot do that way. because, when you use the into corresponding fields of, addition the system, checks if the name of the database field and the name of the field in the internal table to be the same.

YOu cannot have GSBER as the field name for two different fields,

so you can have a temporary table with only those fields exactky is the same order in which they are selected and have the command:

INTO TABLE gt_rendeles_temp

instead of INTO CORRESPONDING FIELDS OF TABLE gt_rendeles.

(The catch is that you need to have the fields of gt_rendeles_temp as the same order as they appear in the select statement.)

Regards,

Ravi

Re: Select with join from the same fields into two different fields

Former Member — Fri, 09 Feb 2007 16:24:27 GMT

Add aufk_{GSBER and vafiloa}GSBER in your select, then in your definition of table gt_rendeles make the order of the fields the same as in your select statement, then use "INTO TABLE GT_RENDELES".

This will only work if the fields you select are celcard in the exact order as gt_rendeles.

Re: Select with join from the same fields into two different fields

Priti_PM — Fri, 09 Feb 2007 16:28:37 GMT

Yes, It should not be a problem to get it done in one select with JOIN.

Have a look here for example :


DATA: BEGIN OF lt_temp OCCURS 0,
          gsber_source TYPE aufk-gsber,
          gsber_dest TYPE aufk-gsber,
      END OF lt_temp.
SELECT a~gsber
       b~gsber
  FROM aufk AS a
       JOIN vafiloa AS b
       ON a~aufnr = b~aufnr
       INTO TABLE lt_temp.

Regards.

Re: Select with join from the same fields into two different fields

Former Member — Fri, 09 Feb 2007 16:31:44 GMT

Hi!

Well, I tried a few ways, and it is possible to solve this question. the keyword is "AS".

Thanx for the answers, let the points flow... I don't know why don't a lot of people like to give points...

The follwoing code is working fine, and finds the corresponding name in the table:


    SELECT aufk~auart afko~aufnr aufk~ktext aufk~objnr afko~aufpl
           vafiloa~iloan iloa~tplnr vafiloa~equnr aufk~kostv
           vafiloa~anlnr vafiloa~anlun aufk~bukrs aufk~kostl
           aufk~zzkocsiszam aufk~werks iloa~gsber AS gsber_receiver
           aufk~gsber AS gsber_sender
             FROM aufk
             INNER JOIN afih ON afih~aufnr = aufk~aufnr
             INNER JOIN afko ON afko~aufnr = aufk~aufnr
             INNER JOIN vafiloa ON afko~aufnr = vafiloa~aufnr
             INNER JOIN iloa ON iloa~iloan = vafiloa~iloan
             INTO CORRESPONDING FIELDS OF TABLE gt_rendeles
           FOR ALL ENTRIES IN gt_rendeles_teny
           WHERE aufk~auart IN s_auart
             AND aufk~bukrs IN s_bukrs
             AND aufk~kostv IN s_kostv
             AND aufk~kostl IN s_kostl
             AND aufk~werks IN s_werks
             AND aufk~zzkocsiszam IN s_kocsi
             AND vafiloa~equnr IN s_equnr  "berendezés
             AND iloa~tplnr IN s_tplnr     "m&#369;szaki hely
             AND aufk~objnr = gt_rendeles_teny-objnr. "ténykönyvelés van

Priti almost had it...

Thanx

Tamá

Re: Select with join from the same fields into two different fields

Former Member — Fri, 09 Feb 2007 16:32:04 GMT

Answered.

Re: Select with join from the same fields into two different fields

Former Member — Fri, 09 Feb 2007 16:32:21 GMT

Or may be you can modify the structure of gt_rendeles table creating two different fields and use into table gt_rendeles instead of corresponding fields.

Thanks,

Santosh

Re: Select with join from the same fields into two different fields

zeeshan_xt — Thu, 06 Jun 2019 19:11:27 GMT

I am using tables VBAK, VBAP, VBFA and they all have fields VBELN, POSNR etc. This exampled worked - Thank you. The mistake i was making was that i was using "INTO CORRESPONDING FIELDS OF TABLE". Here's my code based on this example and its working perfectly:

DATA: BEGIN OF lt_vbak OCCURS 0,
vbeln TYPE vbeln_va,
erdat TYPE erdat,
auart TYPE auart,
bstnk TYPE bstnk,
kunnr TYPE kunag,
posnr TYPE posnr_va,
matnr TYPE matnr,
arktx TYPE arktx,
vbeln_vbfa TYPE vbeln_nach,
posnn TYPE posnr_nach,
erdat_vbfa TYPE erdat,
END OF lt_vbak.

DATA: wa_vbak LIKE LINE OF lt_vbak.

REFRESH: lt_vbak.
CLEAR: lt_vbak, wa_vbak.

SELECT a~vbeln a~erdat a~auart a~bstnk a~kunnr
b~posnr b~matnr b~arktx
c~vbeln c~posnn c~erdat
INTO TABLE lt_vbak
FROM ( vbak AS a
INNER JOIN vbap AS b ON b~vbeln = a~vbeln
INNER JOIN vbfa AS c ON c~vbelv = b~vbeln AND c~posnv = b~posnr )
WHERE a~vbeln IN s_vbeln
AND a~erdat IN s_erdat
AND a~kunnr IN s_kunnr
AND a~auart = 'ZKA'
AND c~vbeln IN s_vbelnl
AND c~erdat IN s_bldat
AND c~vbtyp_n = 'T'.