hi

[Former Member]

pls tell me ten diffrence between

1. select single & select up to 1 rows

2. select options & parameters

[Former Member]

Hi,

What is the differences between select single and selct up to 1 row?

Select Single * will pickup only one matching record from the database into the buffer, and returns the same to the internal table.

Select upto 1 rows will pickup all the records matching the condition into the buffer, but return the top record to the internal table.

For this reason, performance wise select upto 1 row is better than select upto 1 row.

According to SAP Performance course the SELECT UP TO 1 ROWS is faster than SELECT SINGLE because you are not using all the primary key fields.

select single is a construct designed to read database records with primary key. In the absence of the primary key, it might end up doing a sequential search, whereas the select up to 1 rows may assume that there is no primary key supplied and will try to find most suitable index.

The best way to find out is through sql trace or runtime analysis.

Use "select up to 1 rows" only if you are sure that all the records returned will have the same value for the field(s) you are interested in. If not, you will be reading only the first record which matches the criteria, but may be the second or the third record has the value you are looking for.

The System test result showed that the variant Single * takes less time than Up to 1 rows as there is an additional level for COUNT STOP KEY for SELECT ENDSELECT UP TO 1 ROWS.

The 'SELECT SINGLE' statement selects the first row in the database that it finds that fulfils the 'WHERE' clause If this results in multiple records then only the first one will be returned and therefore may not be unique.

Mainly: to read data from

The 'SELECT .... UP TO 1 ROWS' statement is subtly different. The database selects all of the relevant records that are defined by the WHERE clause, applies any aggregate, ordering or grouping functions to them and then returns the first record of the result set.

Mainly: to check if entries exist.

DIFF BETWEEN SELECT-OPTIONS AND PARAMETERS

The main difference between select-options and parameters is that the select-option creates a selection table consisting of 4 fields.

Please go thru the following details to learn more bout it...

Description of the individual components:

SIGN

The data type of SIGN is C with length 1. The contents of SIGN determine for each row whether the result of the row condition is to be included in or excluded from the resulting set of all rows.

Possible values are I and E.

– I stands for "inclusive" (inclusion criterion - operators are not inverted)

– E stands for "exclusive" (exclusion criterion - operators are inverted)

OPTION

The data type of OPTION is C with length 2. OPTION contains the selection operator. The following operators are available:

– If HIGH is empty, you can use EQ, NE, GT, LE, LT,CP, and NP. These operators are the same as those that are used for logical expressions. Yet operators CP and NP do not have the full functional scope they have in normal logical expressions. They are only allowed if wildcards ( '*' or '+' ) are used in the input fields.

If wildcards are entered on the selection screen, the system automatically uses the operator CP. The escape character is defined as #.

– If HIGH is filled, you can use BT (BeTween) and NB (Not Between). These operators correspond to BETWEEN

and NOT BETWEEN that you use when you check if a field belongs to a range. You cannot use wildcard characters.

- LOW

The data type of LOW is the same as the column type of the database table, to which the selection criterion is linked.

– If HIGH is empty, the contents of LOW define a single field comparison. In combination with the operator in OPTION, it specifies a condition for the database selection.

– If HIGH is filled, the contents of LOW and HIGH specify the upper and lower limits for a range. In combination with the operator in OPTION, the range specifies a condition for the database selection.

- HIGH

The data type of HIGH is the same as the column type of the database table, to which the selection criterion is linked. The contents of HIGH specify the upper limit for a range selection.

The parameter statement does not create a selection table .

The second thing is select-option gives us a range for selection, whereas parameter doesn't .

Using the parameter you can define radio buttons and checkboxes where as select-options can't .

[Former Member]

Hi

A lot of people use the SELECT SINGLE statement to check for the existence of a value in a database prior to running a large report. Select singles are also used to look up values from a database where that value is going to be constant for the duration of the program run, or the value is being used to validate some user entry.

Other people prefer to use the 'UP TO 1 ROWS' variant of the SELECT statement.

So what's the difference between using 'SELECT SINGLE' statement as against a 'SELECT .... UP TO 1 ROWS' statement ?

If you're considering the statements

Code:

SELECT SINGLE field

INTO w_field

FROM table.

and

Code:

SELECT field

INTO w_field

FROM table

UP TO 1 ROWS.

ENDSELECT.

then looking at the result, not much apart from the extra ENDSELECT statement. Look at the run time and memeory usage and they may be worlds apart.

Why is this ?? The answer is simple.

The 'SELECT SINGLE' statement selects the first row in the database that it finds that fulfils the 'WHERE' clause If this results in multiple records then only the first one will be returned and therefore may not be unique.

The 'SELECT .... UP TO 1 ROWS' statement is subtly different. The database selects all of the relevant records that are defined by the WHERE clause or lack of, applies any aggregate, ordering or grouping functions to them and then returns the first record of the resultant result set.

Get the difference ??

If not, then create a Ztable called ZDifference with 2 fields in it, MANDT of type MANDT and POSNR of type POSNR. Make sure both of these are keys. Also create a table maintenance dialog for it (SE11->Utilities->Table Maintenance Generator). Fill the table with ten rows 000001-000010.

Then run the program shown below:

Code:

• Purpose: A program that demonstrates the difference

• between SELECT SINGLE and SELECT UP TO n ROWS.

• This program requires the data table Z_DIFFERENCE

• to have been created according to the structure

• outlined in the text above and populated with

• at least 10 records.

Report Z_Difference

Message-id 38

Line-Size 80

Line-Count 0

No Standard Page Heading.

Start-Of-Selection.

Data: w_Single type Posnr,

t_Rows type standard table of Posnr

initial size 0

with header line.

Select single Posnr

from zDifference

into w_Single.

Select Posnr

into table t_Rows

from zDifference

up to 1 rows

order by Posnr descending.

Write 😕 'Select single:', w_Single.

Skip 1.

Write 😕 'Up to 1 rows :'.

Loop at t_Rows.

Write t_Rows.

EndLoop.

You should see the output:

Code:

Select single: 000001

Up to 1 rows : 000010

The first 'SELECT' statement has selected the first record in the database according to any selection criteria in the 'WHERE' clause. This is what a 'SELECT SINGLE' does. The second 'SELECT' has asked the database to reverse the order of the records before returning the first row of the result.

In order to be able to do this the database has read the entire table, sort it and then return the first record. If there was no ORDER BY clause then the results would have been identical (ie both '000001') but the second select if given a big enough table to look at would be far slower.

Now. This causes a problem in the Extended Program Check in that if the full key is not specified in a 'SELECT SINGLE' you get a message like this:

Quote:

Program: Z_DIFFERENCE Line : 39

Syntax check warning

This warning is only displayed in SLIN.

Select single Posnr

^

Messages:

In "SELECT SINGLE ...", the WHERE condition for the key field "POSNR" does not test for equality. Therefore, the single record you are searching for may not be unique.

If you haven't specified a full key and your QA person is complaining that your Extended Check has warnings tell him

"Yes. I can get rid of the warning but the program will run slower and consume more memory."

You could always tell him to "Get Lost" but it's always better to have a valid reason before you do that!

[Former Member]

Hi,

1. Result for both statement is same ,only difference in process

select single : Here system will pick the first record didrectly

based on conditions.

Select upto 1 row : here system will pick all the records which

satisfies the condition and the it will take the

first record.So here performance wise

this staement is low.

2 .Parameters : It will take only on input value.

Select options : This will take range of values .

Ex: 1 From 1 To 100

[Former Member]

Hi

The statement SELECT-OPTIONS has the following effect:

The statement declares a selection table in the program with the name selcrit. A selection table is an internal standard table with header line and standard key. In selection tables, you can save multiple logical conditions . The content of selection screens can be analyzed in a logical expression and in the expression of a WHERE-condition in Open SQL. Selection tables have the following four columns:

sign of type c and length 1. The content of sign determines for every row whether the result of the condition formulated in the column is included or excluded in the entire resulting set for all rows. Evaluable values are "I" for include and "E" for exclude.

option of type c and length 2. option contains the selection option for the condition of the row in form of logical operators. Analyzable operators are "EQ", "NE", "GE", "GT", "LE", "LT", "CP" and "NP" if column high is initial, and "BT", "NB" if column high is not initial. With the options "CP" and "NP", the data type of the columns low and high must be of the data type c, and low must contain at least one wildcard character "" or "*", in which "" masks a single character and "*" a user-defined, even empty, character chain.

low of the data type defined after FOR. This column is designated for the comparison value or the lower interval limitation.

high of the data type defined after FOR. This column is designated for the upper interval limitation.

Two input fields with the name selscrit-low and selscrit-high are created on the current selection screen using a matching external data type in a new line at positions 35 and 60. The length of the input fields bases upon the length of the data type which is defined after FOR. The maximum length of the input fields is 45. The maximum visible length of the input fields is, depending on the nesting depth, in blocks with frames between 10 and 18. If the length is larger than the maximum visible length, then the content is scrollable.

Before the first input field, an automatically generated output field

is displayed as identifier in the first possible position, whose length is, depending on the nesting depth, in blocks with frames between 20 and 30. The output field either contains the name of the selection criterion selcrit, or the selection text which is assigned to the selection criterion in the text elements of the program. If the user requests a field- or input-help on the output field using the function key F1 resp. F4, the same output is displayed as if one of the input fields is chosen.

A pushbutton multiple selection is created after the second input field. If you select this pushbutton, a dialog screen with four tabstrip control pages appears, in which the input fields are again displayed in tabular form in Table Controls. The tabstrip pages are separated based on individual value comparison, interval limitation, and settings for the column sign.

As a user, you can select an input field with a double-click on the selection screen or use the dialog screen for multiple selection. On the selection screen, you can also choose the value for column sign. On the dialog screen for multiple selection, you can do this by selecting the respective tabstrip page. If the selection option is not equal to "EQ" or "BT", then it is displayed as an icon in front of the first input field. The color of the icon is green if the content of column sign is "I", and red if it is "E".

The selection options "CP" and "NP" can be selected only if the first input field contains one of the wildcard characters "*" or "+". If one of these characters is specified, the selection option is automatically set to "CP" when there is a user action.

The attributes of the elements on the selection screen can be influenced with the statement screen_options or the statement SELECTION-SCREEN.

The first row of the selection table selcrit is linked with the input fields on the selection screen. All rows are displayed in the dialog box for multiple selection. Before sending the selection screen, the content of the selection table is transported to the selection screen and displayed in the corresponding location. If the length of columns low and high in the first row of the selection table is larger than 45, then the excessive content is cut off to the right. In remaining rows excessive content is only cut off if the dialog box for multiple selection is displayed. After an user action on the selection screen or in the dialog box for multiple selections, the content of the input fields and the selected settings are transported into the internal table, whereby several events are raised. In doing so, the content of character type fields is converted into upper case by default. All setting regarding the content of the input field can be done in value_options.

If selection criteria are defined in the selection-include of a logical database, further additions ldb_options are necessary resp. possible.

If in an executable program, a selection criterion is defined for a component of a node of the linked logical database, declared by TABLES or NODES, and the node in question is intended for free selection on the logical database, then the selection table is transferred to the logical database after selection screen processing and is treated there as a free selection. Furthermore, in this case, the input fields for the corresponding free selection are displayed directly on the selection screen, without the necessity for the user having to select them via the function free selection.

PARAMETERS

Declaration of a para parameter of length len. Parameters are components of a selection screen that are assigned a global elementary data object in the ABAP program and an input field on the selection screen.

The name of the para parameter may contain a maximum of eight characters. This statement is permitted in the global declaration part of executable programs, function groups and module pools. In function groups and module pools it is only permitted within the definition of a independent selection screen. In executable programs, it is otherwise automatically assigned to the standard selection screen.

The length len can only be specified if the data type specified in type_options is generic in terms of the length ( c, n, p and x). The length len must be specified as a numeric literal or as numeric constants. If len is not specified, the length is set to 1 for a generic data type, or else it is set to the length of the data type. From Release 6.10, it is possible to specify the length using the LENGTH addition.

In detail, the PARAMETERS statement has the following effect:

The statement declares a global variable para of the specified length in the program. The type of the data object is specified in type_options.

On the current selection screen, an input field with the same name and a suitable external data type is created in a new line at position 35. The length of the input field is aligned with the length of the parameter. The maximum length of the input field is 132. The maximum visible length of the input field is between 39 and 45, depending on the nesting depth in blocks with frames. If the len length is greater than the maximum visible length, the content is displayed as movable.

In front of the input field, in the first possible position, an automatically generated output field is displayed as a description, whose length is between 23 and 30 depending on the nesting depth in blocks with frames. The output field contains either the name of the para parameter or the selection text to which the parameter is assigned in the text elements of the program. If the user requests field or input help on the output field using the F1 or F4 function keys, the same output is displayed as when the input field itself is selected.

The attributes of the elements on the selection screen can be influenced in screen_options and with the SELECTION-SCREEN statement.

Before the selection screen is sent, the content of the para data object is transported to the input field on the selection screen. If the length of the parameter is greater than 132, the content is truncated from the right. After a user action on the selection screen, the content of the input field is transported to the data object, where various events are triggered. The content of character-type fields is converted into uppercase by default. Settings with regarding the content of the input field can be made in value_options.

If parameters are defined in the selection include in the logical database, additional ldb_options additions are necessary or possible.