topic Re: Regarding Select statement in Application Development and Automation Discussions

Regarding Select statement

Former Member — Thu, 12 Jun 2008 10:52:31 GMT

Hi below are given 2 queries:

select bukrs

gjahr

belnr

from bkpf

into (t_bkpf-bukrs, t_bkpf-gjahr, t_bkpf-belnr)

where awkey = t_vbrk-vbeln3

and bukrs = t_vbrk-bukrs

and blart = 'RV'.

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

SELECT SINGLE

from bkpf

bukrs

gjahr

belnr

FROM bkpf

INTO (t_bkpf-bukrs, t_bkpf-gjahr, t_bkpf-belnr)

WHERE awkey = t_vbrk-vbeln3

AND bukrs = t_vbrk-bukrs

AND blart = 'RV'.

the difference is only SINGLE keyword.

my question is which query is wrong and why.

Please answer with detail description.

<REMOVED BY MODERATOR>

thanks

Edited by: Alvaro Tejada Galindo on Jun 12, 2008 5:04 PM

Re: Regarding Select statement

Former Member — Thu, 12 Jun 2008 10:56:28 GMT

Hi,

In BKPF we have BUKRS, BELNR as primary keys, so there is a chance of getting the muliple records (by hitting the table with non primary keys), so by just getting the data to the work area is not recommended.

We should use select single when we are using full bunch of primary keys.

So I suggest to use select -- endselect up to 1 rows to archive your requirement.

<REMOVED BY MODERATOR>

Edited by: Alvaro Tejada Galindo on Jun 12, 2008 5:04 PM

Re: Regarding Select statement

Former Member — Thu, 12 Jun 2008 10:57:23 GMT

1st query wil show u error , u hve to put endselect in 1st one , in 2nd one it wil execute without error

both the methods are correct , it depend upon ur requirement whether u want to fetch single value on condition basis or multiple values

Re: Regarding Select statement

Former Member — Thu, 12 Jun 2008 10:57:24 GMT

hii

First query is right

in second query little mistake is there..From should not be there after SINGLE.

SELECT SINGLE kunnr
      FROM kna1
      INTO w_kna1
      WHERE kunnr = w_kunnr.

difference between two queries is first query will fetch all the data which will match to your condition.it can be one raw or more then one raw.

But by using second query you will get single record that will match to your condition.if more then one record exist then it will give you a very first record as your o/p.

<REMOVED BY MODERATOR>

thx

twinkal.

Edited by: Alvaro Tejada Galindo on Jun 12, 2008 5:04 PM

Re: Regarding Select statement

Former Member — Thu, 12 Jun 2008 11:00:07 GMT

Hi,

First query is wrong, because there can be more then 1 record you select.

Should be


Select * into [table]

or:


Select * into [structure] "(or fields, as in your case) 
* works as a loop, inserting every row into structure/fields, one by one
Endselect.

or:


Select single * into [structure] 
* first row is taken

Chears

Lukas

Re: Regarding Select statement

Former Member — Thu, 12 Jun 2008 11:02:23 GMT

Hi,

The first select statement goes wrong if the query is retrieving more than one record. In order to avoid that u can use select ..append itab....endselect. or select query .. into table .. where condition.

The use of select single is used to retrieve single record and place it in corresponding variables. this can be used when ever u want to retrieve only one record by passing all the key values for conditions.

Rgds.,

subash

Re: Regarding Select statement

Former Member — Thu, 12 Jun 2008 11:06:05 GMT

First question..

when using t_vbrk in where clause, where is the for all entries option.

select single can be used if you have fully qualified primary keys.

select end-select will do a database hit twice. Performance wise select single is better. But if Primary keys are not available u can use select end select.

For both the options, use a work area in INTO clause