join

[Former Member]

Hi all,

how to combine the following two select queries using joins.

• fetch data from a304 table

• SELECT kschl vkorg vtweg matnr datbi datab knumh

• FROM a304

• INTO TABLE t_a304

• WHERE kschl = p_kschl

• AND vkorg = p_vkorg

• AND vtweg = p_vtweg

• AND datbi IN s_datbi

• AND datab IN s_datab.

• fetch data from KONP table

• IF NOT t_a304[] IS INITIAL.

• SELECT knumh kopos kschl konwa kpein kmein

• FROM konp

• INTO TABLE t_konp

• FOR ALL ENTRIES IN t_a304

• WHERE knumh = t_a304-knumh

• AND kschl = t_a304-kschl.

• ENDIF.

[Former Member]

Hi all,

how to combine the following two select queries using joins.

• fetch data from a304 table

• SELECT kschl vkorg vtweg matnr datbi datab knumh

• FROM a304

• INTO TABLE t_a304

• WHERE kschl = p_kschl

• AND vkorg = p_vkorg

• AND vtweg = p_vtweg

• AND datbi IN s_datbi

• AND datab IN s_datab.

• fetch data from KONP table

• IF NOT t_a304[] IS INITIAL.

• SELECT knumh kopos kschl konwa kpein kmein

• FROM konp

• INTO TABLE t_konp

• FOR ALL ENTRIES IN t_a304

• WHERE knumh = t_a304-knumh

• AND kschl = t_a304-kschl.

• ENDIF.

[Former Member]

u cannt join these 2 tables.

i think KONP is a cluster table.

Regards

Prabhu

[RaymondGiuseppi]

SELECT a~kschl a~vkorg a~vtweg a~matnr a~datbi a~datab a~knumh
        b~knumh b~kopos b~kschl b~konwa b~kpein b~kmein
  FROM a304 as a 
  JOIN konp as b
    ON b~knumh = a~knumh
    AND  b~kschl = a~kschl.
  INTO TABLE <new table>
   WHERE a~kschl = p_kschl
     AND a~vkorg = p_vkorg
     AND a~vtweg = p_vtweg
     AND a~datbi IN s_datbi
     AND a~datab IN s_datab.

[Former Member]

I'm very new to ABAP dev so sorry if this a lame question. I am trying to use the A304 as suggested above however not all of my materials are in this table. How can this be? Is there some other way I should be getting the info record number so I can look up pricing?

Thanks in advance

[Former Member]

hi

SELECT a~kschl a~vkorg a~vtweg a~matnr a~datbi a~datab a~knumh
             b~knumh b~kopos b~kschl b~konwa b~kpein b~kmein
INTO TABLE t_a304
FROM a304 as a
inner join konp as b
on a~knumh = b~knumh
AND a~kschl = b~kschl.
 WHERE a~kschl = p_kschl
 AND a~vkorg = p_vkorg
 AND a~vtweg = p_vtweg
 AND a~datbi IN s_datbi
 AND a~datab IN s_datab.

pavan

*pls mark for helpful answers

[Former Member]

Hi Shweta

Try this

Select a_{knumh a}kschl a_{vkorg a}vtweg a_{matnr a}datbi a_{datab b}kopos

b_{konwa b}kpein b~kmein from A304 as a inner join konp as b on

a_{KNUMH = b}KNUMH into internal table

where WHERE kschl = p_kschl

AND vkorg = p_vkorg

AND vtweg = p_vtweg

AND datbi IN s_datbi

AND datab IN s_datab.

Regards

Davinder Singh

[Former Member]

Hi,

Yes you can join them.

Only thing is that you can't join KONV, since it is a cluster table:

SELECT a_{kschl a}vkorg a_{vtweg a}matnr a_{datbi a}datab a_{knumh b}knumh b_{kopos b}kschl b_{konwa b}kpein b~kmein

INTO TABLE t_a304

FROM a304 as a

inner join konp as b

on a_{knumh = b}knumh

and a_{kappl = b}kappl

and a_{kschl = b}kschl

WHERE a~kschl = p_kschl

AND a~vkorg = p_vkorg

AND a~vtweg = p_vtweg

AND a~datbi IN s_datbi

AND a~datab IN s_datab.

reward if useful

regards,

ANJI

Message was edited by:

Anji Reddy Vangala

[Former Member]

Hi,

SELECT a_{kschl a}vkorg a_{vtweg a}matnr a_{datbi a}datab a_{knumh b}kopos b_{konwa b}kpein b_{kmein FROM a304 as a inner join konp as b INTO( t_a304-kschl,t_a304-vkorg,t_a304-vtweg,t_a304-matnr,t_a304-datbi,t_a304-datab,t_a304-knumh,t_konp-kopos,t_konp-konwa,t_konp-kpein,t_konp-kmein ) WHERE a}kschl = p_kschl AND a_{vkorg = p_vkorg AND a}vtweg = p_vtweg AND a_{datbi IN s_datbi AND a}datab IN s_datab.

regards,

bharat.

[Former Member]

hi

use the following query.

SELECT kschl vkorg vtweg matnr datbi datab knumh kopos konwa kpein kmein

into corresponding fields of table itab

from a304 inner join konp on konp_{kschl = a304}kschl

and konp_{knumh = a304}knumh

where konp~kschl = p_kschl

AND vkorg = p_vkorg

AND vtweg = p_vtweg

AND datbi IN s_datbi

AND datab IN s_datab.

hope it will work

regards

ravish

<b>plz reward if helpful</b>

[Former Member]

Hi,

First change the structure of the internal table t_a304 to include all the fields in the select clause.

then try out the following code:

select a_{kschl a}vkorg a_{vtweg a}matnr a_{datbi a}datab a~knumh

b_{kopos b}konwa b_{kpein b}kmein

into table t_a304

from a304 as a inner join konp as b

on a_{knumh = b}knumh

and a_{kschl = b}kschl

where kschl = p_kschl

and vkorg = p_vkorg

and vtweg = p_vtweg

and datbi in s_datbi

and datab in s_datab.