topic Re: select statement using 2 tables in Application Development and Automation Discussions

select statement using 2 tables

Former Member — Wed, 08 Mar 2006 20:28:02 GMT

Hi Experts,

In the following code, i have to select "v_likp-charg" into the "concatenate" statement.

could you please let me know, how can i do that.

help is appreciated,

code:

data: i_kna1 type t_kna1 occurs 0,

v_kna1 type t_kna1,

i_likp type t_likp occurs 0,

v_likp type t_likp.

data: begin of t_kna1,

kunnr like kna1-kunnr,

name1 like kna1-name1,

end of t_kna1.

data: begin of t_likp,

matnr like likp-matnr,

charg like likp-charg,

kunnr like likp-kunnr,

end of t_likp.

data: begin of v_output,

record(500),

end of v_ouput.

loop at i_kna1 into v_kna1.

concatenate: v_kna1-kunnr

v_kna1-name1

i want "v_likp-charg" to be here..******

into v_output seperated by c_tab.

append v_ouput.

thanks,

Re: select statement using 2 tables

Former Member — Wed, 08 Mar 2006 20:32:52 GMT

Hi,

How did u declear c_tab in ur code.

Thanks

Khimavath Vikranth

Re: select statement using 2 tables

Former Member — Wed, 08 Mar 2006 20:35:24 GMT

Maybe you can do a read table to v_likp and get the code...something like this...


loop at i_kna1 into v_kna1.
w_index = sy-tabix.
read table v_likp index w_tabix.
concatenate: v_kna1-kunnr
v_kna1-name1
v_likp-charg
into v_output seperated by c_tab.

append v_ouput.
endloop.

But it depends on the data...you must have the same number of records on both i_kna1 and v_likp otherwise...do a SELECT SINGLE on likp using a condition...Always inside the loop.

Greetings,

Blag.

Re: select statement using 2 tables

RichHeilman — Wed, 08 Mar 2006 20:35:50 GMT

You need to make a connection to the customer master file. In this case, you will have a 1 to many relationship with KNA1 and LIKP. Do you want to output only one record for each customer number?

Assuming that you already have the v_likp internal table, you can just read it with key.



loop at i_kna1 into v_kna1.

clear v_likp.
read table i_likp into v_likp with key kunnr = v_kna1-kunnr.

concatenate: v_kna1-kunnr
v_kna1-name1
v_likp-charg
    into v_output seperated by c_tab.

append v_ouput.

Regards,

Rich Heilman

Re: select statement using 2 tables

Former Member — Wed, 08 Mar 2006 20:36:04 GMT

Hi,

Check the below code

Use an Inner join.

SELECT a~kunnr

a~name1

b~matnr

b~charg

into table itab from kna1 as a inner join likp as b

on a_{kunnr = b}kunnr.

Now process ITAB.

LOOP AT ITAB into wa_itab.

concatenate: wa_itab-kunnr

wa_itab-name1

wa_itab-charg

into v_output seperated by c_tab.

append v_ouput.

ENDLOOP.

Regards,

Vara

Re: select statement using 2 tables

Former Member — Wed, 08 Mar 2006 20:38:15 GMT

data: begin of c_tab ,

x(1) type x value '09',

end of c_tab.

Re: select statement using 2 tables

Former Member — Wed, 08 Mar 2006 20:38:17 GMT

if your v_likp-kunnr and v_kna1-kunnr are linked then your code should be


LOOP AT i_likp INTO v_likp.
  CLEAR v_kna1.
  READ TABLE i_kna1 WITH KEY kunnr = v_likp-kunnr.
  CHECK SY-SUBRC = 0.
.....your concatenate statement can follow here...
ENDLOOP.

Remember, I had the LOOP AT i_likp first because, you can have several LIKP records for the same KUNNR.

Re: select statement using 2 tables

Former Member — Wed, 08 Mar 2006 20:47:58 GMT

Hi,

loop at i_kna1 into v_kna1.

read table i_likp where kunnr = v_kna1-kunnr. concatenate: v_kna1-kunnr

v_kna1-name1 i_likp-charge

i want "v_likp-charg" to be here..******

into v_output seperated by c_tab.

append v_ouput.

Re: select statement using 2 tables

Former Member — Wed, 08 Mar 2006 21:58:23 GMT

thnx one and all,