topic Re: select stmts in Application Development and Automation Discussions

select stmts

Former Member — Tue, 13 Mar 2007 04:57:57 GMT

Hi Guys,

can anyone tell me the precise difference between

1) SELECT SINGLE * ........... and SELECT UPTO 1 ROW

and when each of them is used??????

2) SELECT ....... FOR ALL ENTRIES.......

when it should be used and what advantages does it have over

nested select.

Re: select stmts

Former Member — Tue, 13 Mar 2007 05:02:32 GMT

Hi,

Select sinle is used to select a a particular single record that will match for the where condition field.

Upto 1 rows is used, when you wants atleast one suitable record that will match the where condtion. this is used when you can't pass a key field in where condition.

For all entries is used: to fetch the records from some other tables based on the records that were already fetched into an internal table.

It is advantage over nested selects, as it consumes less db time.

Regards,

Anji

Re: select stmts

Former Member — Tue, 13 Mar 2007 05:04:18 GMT

your first question is answered in some other thread.. pls refer that.

when come to 2nd question, for all entries can be used instead of Join and also instead of nested selects.

Regards,

Sujatha.

Re: select stmts

Former Member — Tue, 13 Mar 2007 05:04:23 GMT

hi,

select single is used only when u r using all the key fields in the where clause,

select single hits database only once , where as select up to 1 rows hits database two times.

so, when u r using all key fields in where clause it is better to use select single.

Re: select stmts

Former Member — Tue, 13 Mar 2007 05:05:19 GMT

1. SELECT SINGLE - This form is to be used when the WHERE clause contains all the KEY fields of the table checked for conditions

2. SELECT UP TO 1 ROWS to be used when the WHERE clause does not contain all the KEY fields of the table checked for conditions

3. FOR ALL ENTRIES should be used when there is atleast one field in common between the tables. This is a replacement of JOINS and NESTED SELECTS. NESTED SELECTS hinder the performance of the report, hence to improve performance use FOR ALL ENTRIES.

Naveen

Re: select stmts

former_member404244 — Tue, 13 Mar 2007 05:06:57 GMT

Hi,

Select Single - This form of select is used when the WHERE clause of the SELECT contains all the KEY fields for comparision buring the selection of a single record. This returns the exact record.

Select UP TO 1 rows - This form of select is used when the WHERE clause of the SELECT does not contain all the KEY fields for comparision buring the selection of a single record. This returns the first record for condition.

For all entries is used: to fetch the records from some other tables based on the records that were already fetched into an internal table.u don't need to use nested select statements..

Regards,

nagaraj

Re: select stmts

Former Member — Tue, 13 Mar 2007 05:07:28 GMT

select single *

from spfli

into corresponding fields

of fs_spfli.

this statement should be used when we want to select some data into work area.

select *

up to 1 rows

from spfli

into corresponding fields

of table t_spfli.

this statement should be used when we want to select some data into table.

... FOR ALL ENTRIES IN itab WHERE ... col operator itab-comp ...

this helps us this way,

If the addition FOR ALL ENTRIES is specified before the language element WHERE, then the components comp of the internal table itab can be used as operands when comparing with relational operators.

Re: select stmts

Former Member — Tue, 13 Mar 2007 05:13:38 GMT

hi,

in performance wise both select single and select upto 1 row are same but select single is advisable when all key fields are mentioned in where condition.

For all entries is used to avoid nested select statements. If we want to check some conditions on some records(randomly) that are presented in internel table then for all entries is used.

Coditions to use for all entries.

1. the internal table is not initial.

2. in where condition we have to mention the field that is present in internal table.

3. to eleminate duplicate entries mention all primary key fields in where condition.