topic Re: package size in select query in Application Development and Automation Discussions

package size in select query

Former Member — Tue, 28 Aug 2007 06:25:56 GMT

Hi all,

Can any body help for how to use package size in select statemtent.

I want to seletc record in lot of 300 form 10k records.

And i am using 4.6c version and wnat to know this statement (package size) is there in 4.6c.

I know it is in 6.0.

Re: package size in select query

Former Member — Tue, 28 Aug 2007 06:42:00 GMT

When you read several lines of a database table, you can place them in an internal table. To do this, use the following in the INTO clause:

SELECT ... INTO|APPENDING [CORRESPONDING FIELDS OF] TABLE <itab>

[PACKAGE SIZE <n>] ...

The same applies to the line type of <itab>, the way in which the data for a line of the database table are assigned to a table line, and the CORRESPONDING FIELDS addition as for flat work areas (see above).

The internal table is filled with all of the lines of the selection. When you use INTO, all existing lines in the table are deleted. When you use APPENDING; the new lines are added to the existing internal table <itab>. With APPENDING, the system adds the lines to the internal table appropriately for the table type. Fields in the internal table not affected by the selection are filled with initial values.

If you use the PACKAGE SIZE addition, the lines of the selection are not written into the internal table at once, but in packets. You can define packets of <n> lines that are written one after the other into the internal table. If you use INTO, each packet replaces the preceding one. If you use APPENDING, the packets are inserted one after the other. This is only possible in a loop that ends with ENDSELECT. Outside the SELECT loop, the contents of the internal table are undetermined. You must process the selected lines within the loop.

yes it is there in 4.6.....

reward points if helpful...

Re: package size in select query

Former Member — Tue, 28 Aug 2007 13:04:54 GMT

300 seems small. I usually try for package sizes in th 5,000 to 10,000 range:


  select * from adrc
         into table lt_adrc
         package size 5000

Rob

Re: package size in select query

Former Member — Tue, 28 Aug 2007 13:07:01 GMT

hi,

Package size can be used if you for example only want to finish processing a limited amount of data at a time due to lack of memory.

The example below read 50 records at a time from VBAK into an internal table, and selects the corresponding entries from vbap into an internal table. Then the two internal tables can be processed, and the next 50 records from VBAk can be read. Remember to reinitialize tha tables before the next read.

REPORT  z_test. 

TYPES: 
  BEGIN OF t_vbak, 
    vbeln LIKE vbak-vbeln, 
    erdat LIKE vbak-erdat, 
  END OF t_vbak, 

  BEGIN OF t_vbap, 
    posnr  LIKE vbap-posnr, 
    matnr  LIKE vbap-matnr, 
    meins  LIKE vbap-meins, 
  END OF t_vbap, 

  BEGIN OF t_report, 
    vbeln LIKE vbak-vbeln, 
    erdat LIKE vbak-erdat, 
    posnr  LIKE vbap-posnr, 
    matnr  LIKE vbap-matnr, 
    meins  LIKE vbap-meins, 
  END OF t_report. 

DATA: 
  li_vbak   TYPE t_vbak OCCURS 0, 
  l_vbak    TYPE  t_vbak, 
  li_vbap   TYPE t_vbap OCCURS 0, 
  l_vbap    TYPE t_vbap, 
  li_report TYPE t_report OCCURS 0, 
  l_report  TYPE t_report. 

START-OF-SELECTION. 
  SELECT vbeln erdat 
    FROM vbak 
    INTO TABLE li_vbak PACKAGE SIZE 50. 
    SELECT posnr matnr meins 
      FROM vbap 
      INTO TABLE li_vbap 
      FOR ALL ENTRIES IN li_vbak 
      WHERE vbeln = li_vbak-vbeln. 
    IF sy-subrc = 0. 
*   Now you have the two internal tables li_vbak and li_vbap filled 
*   with data. 
*   Do something with the data - remember to reinitialize internal 
*   tables 
    ENDIF. 
  ENDSELECT. 

*-- End of Program

Regards

Sudheer

Re: package size in select query

Former Member — Wed, 29 Aug 2007 04:32:07 GMT

Thanks a lot Rob,

But i want to select record in lot of 200.

Currently the code is taking 200 records but next 200 record are overwritten.

I want first time it reterive record 200 then second time another 200 and append it in same interbal table.

I think we have to use APPEND also within the select statement.

I if you about the plz let me know.

Thanks

Amar

Re: package size in select query

Former Member — Wed, 29 Aug 2007 07:35:23 GMT

Hi,

Yes definitely you use the 'appending table' statement , then internal table contains all records instead of overwriting the exitsting records.

You can follow the below sample code is

DATA: BEGIN OF tbl_mara OCCURS 0,

matnr LIKE mara-matnr,

mbrsh LIKE mara-mbrsh,

END OF tbl_mara.

SELECT matnr mbrsh FROM mara

APPENDING TABLE tbl_mara PACKAGE SIZE 10.

ENDSELECT.

DESCRIBE TABLE tbl_mara.

WRITE 😕 sy-tfill.

reward with points if helpful.

Regards,

Vijay.

Re: package size in select query

Former Member — Wed, 29 Aug 2007 08:56:32 GMT

Hi,

package size always selects data in select endselect and in each pass it overwrites the data into that table so in that case you have to move the data to another internal table in between the select endselect.

select * from dbtabinto itab1 package size 200.

appned lines of table itab1 to itab2.

endselect.

*here you have all the entries in itab 2

loop at itab2.

*statements

endloop.

thanks

vivekanand

Re: package size in select query

Former Member — Wed, 29 Aug 2007 10:19:37 GMT

Package size in SELECT statements

Package size can be used to retreive a spcific number of records at a time. This can be used if you

for example only want tofinish processing a limited amount of data at a time due to lack of memory.

The exampel below read 50 records at a time from VBAK into an internal table, and selects the

corresponding entries from vbap into an internal table. Then the two internal tables can be

processed, and the next 50 records from VBAk can be read. remeber to reinitialize tha tables before

the next read.

Note the usage of SELECT - ENDSELECT !

REPORT z_test .

TYPES:

BEGIN OF t_vbak,

vbeln LIKE vbak-vbeln,

erdat LIKE vbak-erdat,

END OF t_vbak,

BEGIN OF t_vbap,

posnr LIKE vbap-posnr,

matnr LIKE vbap-matnr,

meins LIKE vbap-meins,

END OF t_vbap,

BEGIN OF t_report,

vbeln LIKE vbak-vbeln,

erdat LIKE vbak-erdat,

posnr LIKE vbap-posnr,

matnr LIKE vbap-matnr,

meins LIKE vbap-meins,

END OF t_report.

DATA:

li_vbak TYPE t_vbak OCCURS 0,

l_vbak TYPE t_vbak,

li_vbap TYPE t_vbap OCCURS 0,

l_vbap TYPE t_vbap,

li_report TYPE t_report OCCURS 0,

l_report TYPE t_report.

START-OF-SELECTION.

SELECT vbeln erdat

FROM vbak

INTO TABLE li_vbak PACKAGE SIZE 50.

SELECT posnr matnr meins

FROM vbap

INTO TABLE li_vbap

FOR ALL ENTRIES IN li_vbak

WHERE vbeln = li_vbak-vbeln.

IF sy-subrc = 0.

Now you have the two internal tables li_vbak and liÆ_vbap filled with data.
Do something with the data - remember to reinitialize internal tables

ENDIF.

ENDSELECT.

All of the product names here are trademarks of their respective companies. The site

www.allsaplinks.com no way affiliated with SAP AG. We have made every effort for the content

integrity. Information used on this site is at your own risk.

REWARD IF USEFULL

Re: package size in select query

Former Member — Wed, 29 Aug 2007 13:16:22 GMT

The PACKAGE SIZE addition is generally ony used when you will be selecting so much data that if you did it all in one go, there wouldn't be enough space in the internal table (system constraint) and it would dump.

So you read a number of records into an internal table using PACKAGE SIZE, process these records into a different internal table and then read another package into the first table (over writing the original contents).

It doesn't matter that the table is being over written, because you are only interested in the second table that contains the processed entries.

Rob

Re: package size in select query

Former Member — Thu, 30 Aug 2007 09:32:20 GMT

Since actual data package size is greater than MAXLINES, data is transferred in packages of size MAXLINES or 40000 bytes.