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

Regarding Select Query

Former Member — Tue, 19 Feb 2008 07:05:35 GMT

What is the difference between SELECT SINGLE* and SELECT...........UPTO ONE ROW?

Re: Regarding Select Query

Former Member — Tue, 19 Feb 2008 07:11:00 GMT

select upto----this will select only records based on our requirement.

i.e if we give select kunnr nmae1 from kna1 upto one row.

this will give only one row from that table.

select single -

this will select single record from the table depending on where condition..

u can check in debugging ..the way data is fetched is different.

Edited by: Jyothsna M on Feb 19, 2008 8:12 AM

Re: Regarding Select Query

Former Member — Tue, 19 Feb 2008 07:11:11 GMT

Hi,

The only difference i believe is in syntax. Apart from that there is no difference. Both will get the first db reocord that matches the where condition. besides UPTO Can be used to fetch multiple rows.

Hope this helps..,

Thanks in advance,

Harikrishna Indugu.

Re: Regarding Select Query

Former Member — Tue, 19 Feb 2008 07:12:45 GMT

select single read db records with primary key.

select upto one row doesnt read for a primary key, instead it will try to find the most suitable index.

hence select upto one row is faster than select single if primary keys are not known.

Thanks

Vasudha

Re: Regarding Select Query

Former Member — Tue, 19 Feb 2008 07:17:47 GMT

HI,

The basic rule we follow in production is

Use SELECT SINGLE if you only need one result row or if you are validating whether the record exists and have the whole key.

If you read data using SELECT, it is worth using multiple operations (on an internal table) if you want to process the data more than once, otherwise, a simple SELECT loop is more efficient.

Avoid selecting all fields (i.e. SELECT *) unless absolutely necessary.

Watch the use of DISTINCT (it can perform badly and will bypass the buffer).

If you are doing a read to validate whether something is there (expecting only one record), use the UP TO 1 ROWS command after the INTO clause to avoid reading data you don’t need.

<REMOVED BY MODERATOR>

Edited by: Alvaro Tejada Galindo on Feb 19, 2008 2:11 PM

Re: Regarding Select Query

vinod_vemuru2 — Tue, 19 Feb 2008 14:37:12 GMT

Hi,

Use Select Single when u r sure that your where clause will get only one record. This will be generally used for selection screen validations.(Selects on check tables for checking valid entry or not)

Select upto one rows will also get one record but the difference is it will first select all the records which satisfies the where clause then it will return the first record.

Thanks,

Vinod.

Re: Regarding Select Query

Former Member — Tue, 19 Feb 2008 14:51:16 GMT

Both the statements are same in functionality point of view but differs only in Performance point of view.

Select single have good performance than select upto 1 row.

Thanks

Kiran.