Difference between SELECT SINGLE and UPTO 1 ROWS?

[Former Member]

Hi Experts,

1) Pls. let me know that, the Difference between SELECT SINGLE and UPTO 1 ROWS?

2) if possible, an example pls.

3) Which is better option among 2?

thanq

[Former Member]

Hi Srinivas ,

Select single should be used only when u have full primary key to hit the table. It directly hits the table for the key and gives u the result.

Where as we use upto 1 rows, only when there is no full primary key , but u want a row...

upto 1 rows is costlier than select single, bocz it puts load on database.

So when ever there is a complete key we should use select single.

Venkat

[Former Member]

Hye srinu,

Generally we use select * from ... upto x rows , if we want to use more than one row. upto 1 row is rarely used as upto n number of rows has flexiblity of putting the feched records in internal table.

If your fetching for only one record, it is best programing practise to use select single.

Reward if helfpful.

Thanks,

Imran.

[Former Member]

SELECT SINGLE :

SELECT SINGLE <LIST OF FIELDS> INTO <WA> FROM <DBT> WHERE <CONDITION>.

SELECT UP TO 1 ROWS :

SELECT <LIST OF FIELDS> INTO <WA> FROM <DBT> UP TO 1 ROWS WHERE <CONDITION>.

ENDSELECT.

SELECT SINGLE : IT READS EXACT RECORD

SELECT UP TO 1 ROWS: IT READS THE FIRST RECORD FOUND (MAY NOT BE EXACT)

SELECT SINGLE : THE WHERE CONDITION SHOULD INCLUDE THE ENTIRE PRIMARY KEY/CHECK TABLE COMBINATION TO IDENTIFY THE RECORD UNIQUELY.

SELECT UP TO 1 ROWS : THE WHERE CONDITION CAN HAVE PART OF THE PRIMARY KEY.

SELECT SINGLE : PREFER THIS TO READ THE EXACT INFORMATION.

SELECT UP TO 1 ROWS : PREFER THIS TO VALIDATE INPUT AS WE ARE NOT INTRESTED WITH THE EXACT MATCH. BECAUSE, A RECORD IS VALID IF AT LEAST ONE RECORD FOUND.

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

--> WHEN WE DON'T PASS ALL THE PRIMARY KEYS IN THE SELECT SINGLE, IT ACTS AS SELECT UP TO 1 ROWS, BUT DOES NOT THROW AN ERROR.

--> IF THE INPUT IS SELECT-OPTIONS WE CANNOT EXPECT SINGLE RECORD AND WHERE AS WE CAN EXPECT UP TO 1 ROWS. BECAUSE, IT CAN BE ANY RECORD FOR THE INPUT RANGE.

THESE ARE THE DIFF BET SELECT SINGLE AND SELECT UP TO 1 ROWS.

I HOPE THE INFO PROVIDED WAS HELPFUL. IF YOU NEED ANY CLARIFICATION THEN PLEASE LET ME KNOW.

REWARD POINTS, IF THE INFO PROVIDED WAS VERY HELPFUL.

HAVE A GREAT DAY!

REGARDS,

KRISHNA CHAITANYA N