topic Re: Fetching huge data from BUT000 with same 5 fields in Application Development and Automation Discussions

Fetching huge data from BUT000 with same 5 fields

Former Member — Fri, 21 Mar 2014 07:44:46 GMT

Re: Fetching huge data from BUT000 with same 5 fields

Former Member — Fri, 21 Mar 2014 07:53:20 GMT

Create ZTEST_TAB tabel with all 5 fields.

Then wrtie below program

REPRT ZTEST.

TEPES: BEGIN OF ty_test,

name type but000-name

last_name type so on..,

gender type ...,

birth_date type..,

birth_place...

TYPES: END OF ty_test.

DATA: wa_test TYPE ty_test.

DATA: it_test_tab TYPE TABLE OF ty_test.

SELECT name last_name gender, birth_date, birth_place FROM BUT000

INTO CORRESPONDING FIELDS OF TABLE it_test_tab.

INSERT ZTEST TABLE it_test_tab.

Re: Fetching huge data from BUT000 with same 5 fields

Former Member — Fri, 21 Mar 2014 08:03:57 GMT

Sorry that was half of my question,it got posted by mistake while typing.

Here comes the complete question.

Hi, there is a requirement to create a z table which will have all the customers in table BUT000. If the set of fields (name, last name, gender, birth date, birth place) are same for few partners (different partners), then a same physical reference number is assigned to all the records with same values for above 5 fields.e.g P000000001 will be assigned to 'reference' field of new z table for all such records.If next record contains different values for these 5 fields then new physical reference will be assigned to that record. We are facing difficulty in comparing each record with all the records in BUT000 , as there are almost 15000000 records in BUT000. It's causing performance issues too.Can we use group by in the select query on BUT000? Please suggest a performance effective solution.

Re: Fetching huge data from BUT000 with same 5 fields

Former Member — Fri, 21 Mar 2014 08:16:42 GMT

You can create index on table BUT000 for these five fields. That is the option to optimize its performance. Else you can run the program in background job.

As tbale BUT000 has huge data and u want company nonprimary key fields, u can onlu go for index else background job.

Re: Fetching huge data from BUT000 with same 5 fields

Former Member — Fri, 21 Mar 2014 08:44:02 GMT

Hi Manali,

What about this sample code:




TYPES: BEGIN OF ty_text,
   name_first TYPE but000-name_first,
   name_last TYPE but000-name_last,
   birthdt TYPE but000-birthdt,
   birthpl TYPE but000-birthpl.
TYPES: END OF ty_text.
TYPES: BEGIN OF ty_ztable,
   ref TYPE int4.
         INCLUDE TYPE ty_text.
TYPES: END OF ty_ztable.
DATA: lt_text TYPE TABLE OF ty_text,
       lt_ztable TYPE HASHED TABLE OF ty_ztable WITH UNIQUE KEY name_first name_last birthdt birthpl,
       ls_ztable TYPE ty_ztable,
       lv_ref TYPE int4,
       l_cur TYPE cursor,
       lv_buf_package type int4.
FIELD-SYMBOLS: <lfs> TYPE ANY,
                <lfs_text> TYPE ty_text,
                <lfs_ztable> type ty_ztable.
lv_ref = 1.
lv_buf_package = 5000.
OPEN CURSOR WITH HOLD l_cur FOR
   SELECT name_first name_last birthdt birthpl FROM but000.
DO.
   FETCH NEXT CURSOR l_cur
     INTO TABLE lt_text PACKAGE SIZE lv_buf_package.
   IF sy-subrc <> 0. EXIT. ENDIF.
   LOOP AT lt_text ASSIGNING <lfs_text>.
     READ TABLE lt_ztable ASSIGNING <lfs_ztable>
       WITH KEY name_first = <lfs_text>-name_first
                name_last = <lfs_text>-name_last
                birthdt = <lfs_text>-birthdt
                birthpl = <lfs_text>-birthpl.
     IF sy-subrc = 0.
       " wa-reference = <lfs_ztable>-ref                    " uncomment this
     ELSE.
       lv_ref = lv_ref + 1.
       CLEAR: ls_ztable.
       " MOVE-CORRESPONDING <lfs_text> TO ls_ztable.        " uncomment this
       " ls_ztable-name_first = <lfs_text>-name_first ....  " uncomment this
       ls_ztable-ref = lv_ref.
       INSERT ls_ztable INTO TABLE lt_ztable.
       " wa-reference = lv_ref    " uncomment this
     ENDIF.
     " move-correspoding to workking area (wa) type zdatabase " uncomment this
     " insert into databse                                    " uncomment this
   ENDLOOP.
   CALL FUNCTION 'DB_COMMIT'.
ENDDO.
CLOSE CURSOR l_cur.

Another way? (index):

Get the 5 fields (only 1 time for each pack of 5 fields)

open cursor...

where field1 = ...

do.

....

endoo.

Enddo.

Regards,

Luis