topic Re: what is difference between single and * from in sql Query in Application Development and Automation Discussions

what is difference between single and * from in sql Query

laxman_sankhla3 — Thu, 15 Feb 2007 07:13:23 GMT

what is difference between single and * from in sql Query

thanks in advanced.

Re: what is difference between single and * from in sql Query

Former Member — Thu, 15 Feb 2007 07:15:39 GMT

Hi,

Select * fetches all the records that suits the Where condition.

Where as

Select Single * fetches a SINGLE record from the Database with the where condition. Genrally we use this when we pass a Key Field in where condition.

Regards,

Anji

Re: what is difference between single and * from in sql Query

Former Member — Thu, 15 Feb 2007 07:17:21 GMT

Laxman,

Single fetches a <b>Single </b>record.

fetches <b>all</b> which follows the given criterion.

Rgds,

Jothi.P

Re: what is difference between single and * from in sql Query

Former Member — Thu, 15 Feb 2007 07:17:35 GMT

Hello,

When we write "select single ",it means we will be selecting only a single record from the table and only a single record will satisfy the condition given in the where clause.

When we write "select *",it means we will be fetching all the records satisfying the condition in the where clause.

regards,

Beejal

**Reward if this helps

Re: what is difference between single and * from in sql Query

Former Member — Thu, 15 Feb 2007 07:18:44 GMT

i think you are asking about select single and select endselect.

select single only fetch one row which meets the where condition in your query.

if buffering is allowed for the table it is buffering in TABLP work area

select endselect fetches multiple rows which meet the where condition in your query.

if buffering is allowed for the table it is buffering in TABL work area.

regards

shiba dutta

Re: what is difference between single and * from in sql Query

Former Member — Thu, 15 Feb 2007 07:42:01 GMT

Select single will fecth only one record at a time

SINGLE SELECT statement (single_select_statement)

A SINGLE SELECT statement (single_select_statement) specifies a result table with one row and assigns the values in this row to parameters.

Syntax

<single_select_statement> ::=

SELECT [<distinct_spec>] <select_column>,...

INTO <parameter_spec>,... FROM <from_table_spec>,...

[<where_clause>] [<group_clause>] [<having_clause>] [<lock_option>]

Explanation

The number of rows in the result table must not be greater than one. If the result table is empty or contains more than one row, corresponding messages or return codes are issued and no values are assigned to the parameters specified in the parameter specifications. The return code 100 – row not found - is set if the result table is empty.

If the result table contains just one row, the values of this row are assigned to the corresponding parameters. The FETCH statement rules apply for assigning the values to the parameters.

The order of the GROUP and HAVING clauses is random.

A LONG column can be specified in a selected column select_column in the uppermost sequence of selected columns in a single select statement only if the DISTINCT specification distinct_spec DISTINCT was not used there.

where as select * will fetch all the records based on the condition

regards

Shiva