topic join in Application Development and Automation Discussions

join

Former Member — Mon, 09 Apr 2007 10:26:37 GMT

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.

Re: join

Former Member — Mon, 09 Apr 2007 10:32:38 GMT

u cannt join these 2 tables.

i think KONP is a cluster table.

Regards

Prabhu

Re: join

RaymondGiuseppi — Mon, 09 Apr 2007 10:32:53 GMT

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.

Re: join

Former Member — Mon, 09 Apr 2007 10:33:10 GMT



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

Re: join

Former Member — Mon, 09 Apr 2007 10:34:26 GMT

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

Re: join

Former Member — Mon, 09 Apr 2007 10:36:51 GMT

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

Re: join

Former Member — Mon, 09 Apr 2007 10:37:19 GMT

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.

Re: join

Former Member — Mon, 09 Apr 2007 10:39:10 GMT

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>

Re: join

Former Member — Mon, 09 Apr 2007 10:43:53 GMT

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.

Re: join

Former Member — Fri, 10 Aug 2007 13:25:04 GMT

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