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: 

data extraction from 4 different dbtables into one internal table

Former Member
0 Kudos
244

hi people,

I am new to abap programming and have a question. If I have tables likp, lips, kna1, t001W and tspat and I have to extract different sets of data from each of these with likp and lips being the main tables with which these other tables are linked, can I store all this data into one single internal table and write it out to as an IDOC or not? This is with respect to the bill of lading. If anybody has done anything similar to this please can you help. I have written the queries but the problem is to write the data out in one table. If possible can somebody give me some example. I need help urgently.

Thanks & regards,

Minal

10 REPLIES 10

Former Member
0 Kudos
87

You should use a join or a view of LIKP & LIPS to get the data in one internal table say lt_delivery_data.

You can find a view in SE11 for LIKP & LIPS and choose a view which has both the tables LIKP & LIPS and is equivalant to a join.

I m sure you will find one but if not then you need to use join on both the tables.

I am only giving logic here ..code you need to write.


'Declare lt_final_tab as standard table.
Declare lt_delivery_data as sorted table with key <LIKP key fields >.

SELECT <required fields>
  INTO TABLE lt_delivery_data
  FROM <delivery_table_view>
 WHERE < your conditions >. 

For other two tables , you should declare two separate hashed internal tables with their respective primary keys as key of hashed table.

Get all the t001W data in one shot from database into  hashed itab say lt_t001W.

LOOP AT lt_delivery_data.

* Prpare your final lv_final_itab.

* if you want you can use MOVE one by one rather than 
* move-corresponding. performance wise MOVE is better.
  move-corresponding fields of lt_delivery_data 
                            to lt_final_tab.

* Also use READ TABLE to get the corresponding data from * lt_t001W and move it to final table .
* MOVE required fields of lt_kna1 to final table.
  
* For KNA1, You can chose to have the logic below 
* or simply use the way it is done for t001W.  
  READ TABLE lt_kna1 
  with key customer = lt_delivery_data-customer. 
*  I think customer is field kunnr - my main memory is 
*  a mess somehow working with RAM and CPU :-)
  if sy-subrc ne 0.
    select single * into lt_kna1 
      from kna1 
     where customer = lt_delivery_data-customer.
     APPEND lt_kna1 .
   endif.
   MOVE required fields of lt_kna1 to final table.
   APPEND lt_final_tab.
ENDLOOP.

* Now do whatever you want to with this final itab :-)
OK I missed tspat,but you can use similar logic.

Cheers,

Ram

Former Member
0 Kudos
87

Hi Minal,

The simplest way to do this is to create your own view in transaction SE11.

This view could join LIKP, LIPS, KNA1, T001W and TSPAT. All you need to do is specify the join conditions (for example LIKP-MANDT = KNA1-MANDT, LIKP-KUNNR = KNA1-KUNNR, etc. client is important in these joins!).

Then you choose only those fields that you need as the fields of the view.

Save and generate the view.

Now in your program the code is very simple. Lets say you called your view ZV_BILL_OF_LADING. The code to select the data would be:

* Data declarations.
DATA: x_bill_of_lading TYPE STANDARD TABLE OF zv_bill_of_lading.
* Select data.
SELECT *
  FROM zv_bill_of_lading
  INTO TABLE x_bill_of_lading
 WHERE LIKP = ...
   AND ...

* Now we have internal table x_bill_of_lading filled
* with the required data.
* Process as required for idoc.
  LOOP AT x_bill_of_lading.
    .
    .
    .
  ENDLOOP.

You have a seperate thread already for the idoc posting, so I will just restrict the answer to extracting the data.

Hope that helps.

Cheers,

Brad

0 Kudos
87

Hi Brad,

I agree, this might be the simplest way to it but ...

I don't think the transaction tables like LIKP and master tables like KNA1 & T001W should be mixed together in a join.

The reason being LIKP might have more than 50000 records while tables like T001W will hardly have 2000 records.

If you make a join, then together even T001W will be searched for at least 50000 times in the database.

So it's better to buffer the T001W and KNA1 and in an internal table ( Hashed / Sorted ) and then use READ statement using key ( binary search in case of sorted ).

That way you would be able to avoid a lot of db processing.

There will be huge difference between the performance of both the approachs.

Thanks,

Ram

0 Kudos
87

Hi Ram,

Two points:

1. Performance is not just about db optimisation. ABAP execution costs you runtime also. Given all the joins in these tables are direct primary keys the db accesses would not be expensive (in fact with db's like oracle using caching the performance would be the same as for your direct internal table lookup). So in fact it could be quicker using the view as you don't need to do further selects in addition to the joins on KNA1 and T001W.

2. Programs needs to be maintained once they have been written. In general I always prefer to take the simplest approach when writing a program unless there is a really strong reason for taking a more complex approach. The simpler a program is, the easier it is to maintain (by yourself and by others when you have moved on). This saves both time and money.

So in this case I feel that putting all the tables into the view is the best combination of performance and simplicity. Its efficient and simple.

Cheers,

Brad

0 Kudos
87

Hi Brad,

I am sorry but I think there is a strong reason here.

5 tables in a join, 2 being transaction rest master data, will be too much. Does not matter if all the primary keys are used.

I think the best way to prove is to write 2 programs and run with some real scale of records.

But I don't think anyone of us is going to do that.

So I'll leave it here..

Cheers,

Ram

Former Member
0 Kudos
87

Dear Hi

I am going to give an example with two table plz convert it into yours own.

let suppose i have two tables DEPT which has following

columns

-


deptno (primary key)

dname

location

another table is EMPLOYEE which has following columns

-


empno

ename

sal

deptno (foreign key)

DEPT is the master table and EMPLOYEE is detail table and key column is deptno

you have write in your question that you have made the query and join the tables, problem is how the insert the result set in an internal table so my dear try this

first of all create a structure

-


types: begin of temp

dname type dept-dname,

location type dept-location,

empno type emp-empno,

ename type emp-ename,

end of temp.

data itab_temp type standard table of temp.

select deptdname, deptlocation, employee~empno,

employee~ename

from dept inner join employee

on deptdeptno = employeedeptno

into table itab_temp.

plz try this this is the logic for two table you can add more tables.

Rai

Former Member
0 Kudos
87

Dear Hi

I am going to give an example with two table plz convert it into yours own.

let suppose i have two tables DEPT which has following

columns

-


deptno (primary key)

dname

location

another table is EMPLOYEE which has following columns

-


empno

ename

sal

deptno (foreign key)

DEPT is the master table and EMPLOYEE is detail table and key column is deptno

you have write in your question that you have made the query and join the tables, problem is how the insert the result set in an internal table so my dear try this

first of all create a structure

-


types: begin of temp

vdname type dept-dname,

vlocation type dept-location,

vempno type emp-empno,

vename type emp-ename,

vend of temp.

data itab_temp type standard table of temp.

data wa_temp type temp.

select deptdname deptlocation employee~empno

employee~ename

from dept inner join employee

on deptdeptno = employeedeptno

into table itab_temp.

loop at itab_temp into wa_temp.

write : / wa_temp,

end loop.

plz try this this is the logic for two table you can add more tables.

Rai

Former Member
0 Kudos
87

Dear Minal Agrawal

if u r not satisfied with my answer then tell me i can give u the example with four tables.

Regards

Rai

0 Kudos
87

Hey Rai,

Thanks for your help.

I have been able to join two and three tables but as of now my problem is with some field in the likp table. I cannot get the BTGEW field from the likp table into my typ_tab which is internal table with common entries from both likp and lips with the following fields: vbeln, lfdat, wadat, kunnr, werks, & spart. If i take in btgew too , then when I try to write the file to a PC it gives me an error (dump). Can you help me with this as soon as possible? Also I have to convert 22.000 to 22000. How do I do this? If you can help, please let me know.

Thanks,

0 Kudos
87

i think this field is amount field and reference a currency.

You should rather convert it to character field and just multiply the value with 1000.

declare another internal table say lt_final_tab which will have all the fields but BTGEW of type char and width say 17.

Then

data : lv_int_amount type i.

LOOP AT typ_tab.

move-corresponding fields of typ_tab to lt_final_tab.

lv_int_amount = typ_tab-BTGEW * 1000.

lt_final_tab-BTGEW = lv_int_amount.

append lt_final_tab.

ENDLOOP.