topic Re: SELECT INTO TABLE in Application Development and Automation Discussions

SELECT INTO TABLE

Former Member — Wed, 02 Jul 2008 06:16:45 GMT

Hi,

Is there a possibility to convert something like this:

TYPES: BEGIN OF r_mystruct,
	field1(10),
	field2(20),
	field3(30),
	field4(40),
	field5,
	field6(5)
       END OF r_mystruct.

DATA:
	tab TYPE STANDARD TABLE OF r_mystruct WITH HEADER LINE.

MOVE sy-index TO tab-field5.
SELECT aufnr gsber ktext FROM aufk INTO CORRESPONDING FIELDS OF aufk.
	MOVE aufk-aufnr TO tab-myfield1.
	MOVE aufk-aufnr TO tab-myfield2.
	MOVE aufk-ktext TO tab-myfield3.
	MOVE aufk-gsber TO tab-myfield4.
	APPEND tab.
ENDSELECT.

to a statement involving a SELECT INTO TABLE ?

I've tried something like:

SELECT aufnr aufnr ktext gsber ( sy-index ) NULL FROM aufk INTO TABLE tab.

but i get numerous errors (and yes, i need to have aufnr twice)

Any hints on how to transfer selected fields to a completely custom structure, is it possible ?

Thanks in advance,

Bart

Re: SELECT INTO TABLE

Former Member — Wed, 02 Jul 2008 06:22:34 GMT

Bart,

Have you ever hear about table buffer?

just define in tables statement:

tables : aufk.

remove into corresponding field.

Amit.

Re: SELECT INTO TABLE

Former Member — Wed, 02 Jul 2008 06:32:29 GMT

Yes I have.

The problem is I don't want to select data into table with exactly the same fields as the source table(here AUFK).

I need to make a custom structure which is common for many selects from different tables.

In presented select AUFNR from AUFK table goes to field1.

I have more selects, for example from PRPS where PSPNR goes into field1.

So I'm interested in optimizing the select by converting the SELECT - MOVE - ENDSELECT to a SELECT INTO TABLE, but once again not a table of AUFK or PRPS, but my custom table consisting of a set of custom rows (type r_myrecord, with fields field1, field2 .. ).

Re: SELECT INTO TABLE

former_member195383 — Wed, 02 Jul 2008 06:37:39 GMT

Hi declare the type as....

TYPES: BEGIN OF r_mystruct,

field1(10),

aufnr type aufk-aufnr,

ktext type aufk-ktext ,

gsber type aufk-gsber,

field5,

field6(5)

END OF r_mystruct.

DATA: tab TYPE STANDARD TABLE OF r_mystruct WITH HEADER LINE.

SELECT * INTO CORRESPONDING FIELDS OF TABLE tab

FROM aufk.

the abv select query will fetch the aufnr,ktext and gsber fields...

for rest of the fields...usse the below loop..after the select statement...

loop at itab.

itab-field1 = itab-aufnr.

itab-field5 = sy-index.

itab-field6 = ''.

append itab.

endloop.

reward if useful...

Edited by: Rudra Prasanna Mohapatra on Jul 2, 2008 8:37 AM

Re: SELECT INTO TABLE

Former Member — Wed, 02 Jul 2008 06:44:29 GMT

To sum it up more picture wise:

SELECT aufnr aufnr ktext gsber ( sy-index ) NULL FROM aufk INTO TABLE tab.
or
SELECT pspnr posid post1 pgsbr ( sy-index ) 'blah' FROM prps INTO TABLE tab.

So at the end I expect to obtain, in the first case:

tab[] = 
FIELD1     FIELD2     FIELD3     FIELD4     FIELD5    FIELD6
aufnr	     aufnr      ktext      gsber      9         [empty]

In the second case:


tab[]=
FIELD1     FIELD2     FIELD3     FIELD4     FIELD5    FIELD6
pspnr	     pspnr      post1      pgsbr      7         blah

So in the first select, some fields of AUFK go into the tab\[\] and in second select fields of PRPS go into tab\[\].

And I'm asking is it possible to do it avoiding the LOOP with MOVE aufnr or syindex or 'blah' to fieldX, I'd like it to be handled by the SELECT INTO TABLE automatically.

Re: SELECT INTO TABLE

JozsefSzikszai — Wed, 02 Jul 2008 06:50:52 GMT

hi Bart,

selecting the same field twice in one SELECT ==> works

'selecting' a system variable (sy-index) ==> does not work

'selecting' NULL ==> does not work

'selecting' a literal ('blah') ==> does not work

hope this helps

Re: SELECT INTO TABLE

Former Member — Wed, 02 Jul 2008 06:51:59 GMT

Hello

Try:


TYPES: BEGIN OF r_mystruct,
	field1 like aufk-aufnr,
	field2 like aufk-aufnr,
	field3 like aufk-ktext,
	field4 like aufk-gsber,
       END OF r_mystruct.
 
DATA:
	tab TYPE STANDARD TABLE OF r_mystruct WITH HEADER LINE.
 

SELECT aufnr aufnr ktext gsber FROM aufk INTO table tab.

Re: SELECT INTO TABLE

former_member195383 — Wed, 02 Jul 2008 06:53:44 GMT

hi....

after using the select query why dont u use the below loop statement....That will solve ur purpose of selecting null value ans sy-index value...

loop at itab.

itab-field1 = itab-aufnr.

itab-field5 = sy-index.

itab-field6 = ''.

append itab.

endloop.

Re: SELECT INTO TABLE

Former Member — Wed, 02 Jul 2008 07:12:15 GMT

I have this:


TYPES: BEGIN OF r_mystruct,
	field1(10),
	field2(20),
	field3(30),
	field4(40),
	field5,
	field6(5)
       END OF r_mystruct.

You are all mostly circling around this:


TYPES: BEGIN OF r_mystruct,
field1(10),
aufnr type aufk-aufnr,
ktext type aufk-ktext ,
gsber type aufk-gsber,
field5,
field6(5)
END OF r_mystruct.

I've already said I want to feed the same structure once with AUFK data, once with PRPS data or data from some other tables.


SELECT * INTO CORRESPONDING FIELDS OF TABLE tab
FROM aufk.

This will work ok.

but this won't:


SELECT pspnr [here some other fields from PRPS] INTO CORRESPONDING FIELDS OF TABLE tab
FROM prps.

As Your structure is only suitable for AUFK.

Re: SELECT INTO TABLE

JozsefSzikszai — Wed, 02 Jul 2008 07:17:30 GMT

than you can do:

SELECT aufn AS field1 whatever AS field2 whatever AS field3 ...

INTO CORRESPONDING FIELDS OF TABLE tab.

Re: SELECT INTO TABLE

Former Member — Wed, 02 Jul 2008 07:33:24 GMT

Allright, everything obviously can't be solved by the SELECT only, but pointed alternatives of Rudra Prasanna and Eric Cartman \[ xxx AS field1, gosh how could I miss that ] were helpful. Points rewarded.

Thanks.

Re: SELECT INTO TABLE

Former Member — Wed, 02 Jul 2008 11:13:18 GMT

I don't understand why it should not be possible to do it in one SELECT.

Your original solution is probably the best solution already.

SELECT aufnr gsber ktext FROM aufk INTO CORRESPONDING FIELDS OF aufk.

MOVE aufk-aufnr TO tab-myfield1.

MOVE aufk-aufnr TO tab-myfield2.

MOVE aufk-ktext TO tab-myfield3.

MOVE aufk-gsber TO tab-myfield4.

MOVE (sy-index TO tab-myfield5.

MOVE 'blabla' TO tab-myfield6.

APPEND tab.

ENDSELECT.

There is only the misunderstanding, that SELECT ... INTO TABLE is supposed to be much better

that SELECT ... ENDSELECT, which is not true!

Get rid of the INTO COORESPONDING FIELDS, define a smaller structure where INTO works.

And what is SY-INDEX????

Field:SY-INDEX

Definition

In DO- and WHILEloops, the number of the previous loop passes including the current pass.

Is that what you want?

Or the counter of the internal table?

or just a counter?

Siegfried

Re: SELECT INTO TABLE

ThomasZloch — Wed, 02 Jul 2008 11:26:11 GMT


> There is only the misunderstanding, that SELECT ... INTO TABLE is supposed to be much better
> that SELECT ... ENDSELECT, which is not true!

Hi Siegfried,

how does this compare to the statement "It is always faster to use the Into Table version of a Select statement than to use Append statements. " of the performance examples within SE80? I suspect that some of these are quite outdated anyways.

Please shed some light on this.

Greetings

Thomas

Re: SELECT INTO TABLE

JozsefSzikszai — Wed, 02 Jul 2008 11:38:00 GMT

I created a small, fast and dirty test:

data : begin of gt_bkpf occurs 0,
       bukrs like bkpf-bukrs,
       belnr like bkpf-belnr,
       gjahr like bkpf-gjahr,
       end of gt_bkpf.

select bukrs belnr gjahr up to 100000 rows
       from bkpf
       into gt_bkpf.
append gt_bkpf.
endselect.

data : begin of gt_bkpf occurs 0,
       bukrs like bkpf-bukrs,
       belnr like bkpf-belnr,
       gjahr like bkpf-gjahr,
       end of gt_bkpf.

select bukrs belnr gjahr up to 100000 rows
       from bkpf
       into table gt_bkpf.

The first example takes about 60% more time, than the first.

Re: SELECT INTO TABLE

Former Member — Wed, 02 Jul 2008 13:05:30 GMT

I would be more convinced, if you measure the faster one first and slower later.

Even if you measure identical things you will see often that the first one is slower than the second.

60% is too much I would say.

I see 10%, but I am looking forward to other measurements.

The 10% overhead for the SELECT ... ENDSELECT is here o.k., because it save the LOOP, which is probably more expensive than 10%.

Siegfried

Re: SELECT INTO TABLE

ThomasZloch — Wed, 02 Jul 2008 13:36:13 GMT

based on Eric's example with 100K records:

Array read: 0.139 seconds

Append: 0.329 seconds

1000K records:

Array read: 1.406 seconds

Append: 3.286 seconds

very consistent from second measurement on. sandbox with 80 million BKPF entries.

Cheers

Thomas

Re: SELECT INTO TABLE

Former Member — Wed, 02 Jul 2008 14:38:30 GMT

I had another example. I can see the 60% in the above example.

But try this small modification of the example:


  data : gt_bkpf like bkpf occurs 0,
         gs_bkpf like bkpf.

    select *
           into gs_bkpf
           from bkpf
           up to 100000 rows.

I have no access to a system with such large BKPF tables, I see .... less than 10% difference, here.

What is the reality?

Siegfried

Re: SELECT INTO TABLE

ThomasZloch — Wed, 02 Jul 2008 14:58:50 GMT

BKPF/BSEG and COBK/COEP are our monsters here, very FI/CO heavy.

100K reading ALL fields from BKPF (select * into):

Array read: 3.340 seconds

Append: 3.646 seconds

There is your 10%...interesting result indeed.

And then: 100K selecting * into corresponding fields (structure as in Erics example)

Array read: 0.146 seconds

Append: ~~3.399 seconds~~ wrong, correct: 0.305 seconds

...any interpretations?

Edit: All my results in a little overview:


                                                                                100,000  BKPF records read.                                                                                
SELECT * FROM tab INTO CORRESPONDING FIELDS (3 fields in target structure):           
Array Select:            0.145  sec                                                   
Loop/Append :            0.307  sec                                                   
SELECT f1 f2 f3 FROM tab INTO (3 fields in target structure):                         
Array Select:            0.135  sec                                                   
Loop/Append :            0.304  sec                                                   
SELECT f1 f2 f3 FROM tab INTO CORRESPONDING FIELDS (all fields in target structure):  
Array Select:            0.400  sec                                                   
Loop/Append :            0.540  sec                                                   
SELECT * FROM tab INTO (all fields in target structure):                              
Array Select:            3.411  sec                                                   
Loop/Append :            3.692  sec

Re: SELECT INTO TABLE

JozsefSzikszai — Thu, 03 Jul 2008 07:02:56 GMT

I gave it a try as well and had the same result. The difference was less than 10% (rather surprising). However two remarks:

- From tables like BKPF/BSEG I always select with field list and never with *

- In the line-by-line select there should be some data manipulation between the SELECT and the APPEND, which also reduces the performance. Otherwise it does not make much sense to select this way. In fact we have to compare:

SELECT ....

data manipulation

APPEND ....

ENDSELECT.

with this:

SELECT ... INTO TABLE ...

LOOP AT itab.

data manipulation

ENDLOOP.

Re: SELECT INTO TABLE

ThomasZloch — Thu, 03 Jul 2008 07:17:08 GMT

Hi Eric,

> - From tables like BKPF/BSEG I always select with field list and never with *

Me too, but my results here show that as long as you define a target table with only the necessary fields, there is almost no difference between SELECT * INTO CORRESPONDING FIELDS and SELECT f1 f2 f3 ... INTO (with our without INTO CORRESPONDING FIELDS). The mistake that a lot of people make is to SELECT * INTO a target table that is simply defined by the database table, e.g. BKPF, but then only work with a small subset of all that data. Large overhead.

In fact, SELECT * INTO CORRESPONDING FIELDS into a target table with few colums allows for nice dynamic programming possibilities.

> - In the line-by-line select there should be some data manipulation between the SELECT and the APPEND, which also reduces the performance. Otherwise it does not make much sense to select this way.

True, but here I just wanted to compare the pure runtimes of these two constructs. I will add your additional test to my little program here.

Of course the results may vary on different hardware/OS/DB.

Cheers

Thomas

Edit: here it is:


 1,000,000  BKPF records read.                                                                                
SELECT f1 f2 f3 FROM tab INTO (4 fields in target structure) with manipulation:    
Array Select, LOOP INTO     :            2.511  sec                                
Array Select, LOOP ASSIGNING:            2.059  sec                                
Loop/Manipulate/Append      :            3.498  sec

Array select still in front...ASSIGNING better than INTO, but we knew that anyway, didn't we.

Manipulation is to concatenate f1 f2 f3 into an additional field f4.