Sir/Madam
What does the sql statement "as set with 1 rows" do.
Regards
Django

Hi,

With this option you can indicate the maximum number of records the query can produce
Source:
http://www.baanboard.com/programmers_manual_baanerp_help_functions_database_handling_set_specification

I hope it helps,

- George

it improves the performance of the queries by letting the bshell to know the number of rows to fetch from the table beforehand itself.

this is used to get the no of records from the table.
ex:
as set with 1 rows - returns the first record of the of the particualr table and if you use order by fieldname desc, it will get last record.
as set with 5 rows - returns the first five records of the particuar table.

Thanks Sir

Hi All,

I want to know how exactly as set with 'n' rows works?
How it help in increasing the performance?
Is it required to use in a query which is going to return me a unique record?
e.g. select tccom100.nama
from tccom100
where tccom100._index1 = {:i.bpid}
|* as set with 1 rows
selectdo
endselect
Will using as set with 1 rows in this query improve the performance?

I have read all above post. But I want to know 'HOW' it works and 'HOW' it improves the performance in case of above example if it really does?

From baan optimization guide:

Problem
Extra communication is done in the level 2 driver on each query to know whether
more records are present or not. This creates some overhead.
Solution
If the number of returning records is known beforehand, implement the as set with x
rows.
It is especially preferable for situations where the select statement has a high hit ratio
(frequently carried out).

as set with 1 rows selected the last record of the condition selection.

as set with 1 rows is also used in many cases when the program logic so requires like whether any matching record exists or to get the highest or lowest number (used in conjunction with order by clause) .

Thanks Mark,

This is what I was looking for. But after reading, this next question that pops in my mind is, why even for the index, there is a second search in level 2 driver?
But at least I found the valid/correct reason why I have to use as set with 1 rows even in case of index.

Dear MilindV,
no this command will select only 1 record from the table instead of unique record.

if you want to pick only 1 record from the multiple lines/record
then you can use this command, but not for the unique record.

example :

table have records

code Date intime outtime
A001 01-01-2010 10.10 10.30
A001 01-01-2010 11.00 11.30
A001 01-01-2010 12.00 02.00

if you want to pick last record from the table then query will be.


select tablename.*
from tablename
where tablename._index1 ={:}
order by index1 desc
as set with 1 rows
selectdo
endselect