Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

How to use SELECT statement to extract KNA1-NAME1 and KNB1-ZSABE

Former Member
0 Kudos

In building table I_KNA1 in form f_extract_data, an additional select statement is needed to select and append datafrom (KNA1 and KNB1) to existing I_KNA1

In form f_extract_data table i_kna1 is populated with data from zd_kna101.(where ZD_KNA101 is a view of KNA1, KNB1 and KNBK. It's a joint of these three tables.) This will take care of all customers with KNBK-BANKL and KNBK-BANLN populated. But this does not include new customers created in SAP CRM (no KNBK records). Therefore a separate SELECT statement is needed to extract KNA1-NAME1 and KNB1-ZSABE for customers created in SAP CRM(the customers who do do not have BANKL and BANKN). These selected records are then need to be appended to I_KNA1. It's possible that new selection statement might extract records that are already in existing I_KNA1 table. IF this is the case, right after the append, sort I_KNA1 and remove duplicate records.

*******************************

form f_extract_data.

select kunnr " Customer Number

name1 " Name

zsabe " Business Unit

bankl " Legacy Bank Key

bankn " Legacy Bank Account

from zd_kna101 " View of KNA1, KNB1, and KNBK

into table i_kna1

for all entries in i_doc_by_ref

where kunnr = i_doc_by_ref-kunnr

and zsabe in s_zsabe.

if sy-subrc <> 0.

message a116 with 'customer'. " Customer Table is empty. HALT!!!

else.

sort i_kna1 by kunnr.

endif.

****************************************

will I have to use the join statement(for KNA1-NAME1 and KNB1-ZSABE using the key as KUNNR) here or give two seperate select statements for extracting data from KNA1 and KNB1 using the key as KUNNR.

THANKS FOR THE HELP.

1 ACCEPTED SOLUTION

Former Member
0 Kudos

Hi Andy,

<b>A join on KNA1 and KNB1 will do</b>. Join these two tables <b>on kunnr</b> and get your KNA1-name1 and KNB1-ZSABE.

Regards,

Raj

5 REPLIES 5

Former Member
0 Kudos

Hi Andy,

<b>A join on KNA1 and KNB1 will do</b>. Join these two tables <b>on kunnr</b> and get your KNA1-name1 and KNB1-ZSABE.

Regards,

Raj

RichHeilman
Developer Advocate
Developer Advocate
0 Kudos

I would suggest using the join on KUNNR. Maybe something like this.





data: begin of itab occurs 0,
      kunnr type kna1-kunnr,
      name1 type kna1-name1,
      ZSABE type knb1-ZSABE,
      end of itab.


    select kna1~kunnr kna1~name1 knb1~zsabe
           into table itab
                from kna1
                   inner join knb1
                        on kna1~kunnr = knb1~kunnr
                               where ........

Regards,

Rich Heilman

0 Kudos

Thanks Rich for reply I have to check whether it works or not.I also don't know how to use the syntax for JOIN statement

jayanthi_jayaraman
Active Contributor
0 Kudos

Hi,

data : itab type standard table of zd_kna101,

wa type zd_kna101.

select kna1name knb1zsabe knb1~kunnr

from kna1 inner join knb1

on kna1name = knb1name into corresponding fields of table itab.

loop at itab into wa.

read table i_kna1 into w_kna1 with key name = wa-name.

if sy-subrc eq 0.

delete itab where name = wa-name.

else.

append wa to i_kna1.

endif.

endloop.

Hope this will help.

0 Kudos

you can use a db-view too:

select name1 zsabe kunnr

from VF_DEBI

...

regards Andreas