HI

Can anyone tell me how to retrieve last record of any table in Easy SQL. For ex. I want to retieve the last transaction for a particular item from table tdinv700. This table contains multiple records for one item and for one warehouse.

Also tell me meaning of statement "as set with 1 rows". What it does.

Thanx


Ganesh

Hi

You can try following SQL to get last record from table :

Select from tdinv700.*
from tdinv700
where tdinv700._index1 = {:item}
order by tdinv700._index1 desc
as set with 1 rows
Selectdo
Endselect

This should give you the last record for that item.

Also you can go through this :

Baan SQL supports the following options in relation to sets:

§ Maximum set size.

With this option you can indicate the maximum number of records the query can produce. The syntax is:

SELECT .... [from][where] AS SET WITH <number> ROWS

§ Prepared set.

With this option, the entire set is retrieved before the first record is returned. The set is temporarily stored. This option is useful when a process simultaneously selects and maintains (or deletes or adds) records. In this case, changes must not be visible in the selected records. The prepared set option forces a consistent read. The syntax is as follows:

SELECT ... [from][where] AS PREPARED SET

§ Maximum set size and prepared set.

You can combine both options as follows:

SELECT ... [from][where] AS PREPARED SET WITH <number> ROWS

Dear,
Hope this code works for you .


select *
from tdinv700
where tdinv700.cwar=:cwar |G02
and tdinv700.item=:item |EG5002300JO6D000
and tdinv700.trtm =(select max(tdinv700.trtm) from ttdinv700804
where tdinv700.trdt=( select max(tdinv700.trdt) from ttdinv700804
where tdinv700.cwar=:tdinv700.cwar
and tdinv700.item=:tdinv700.item)
and tdinv700.cwar=:tdinv700.cwar
and tdinv700.item=:tdinv700.item)
and tdinv700.sern =(select max(tdinv700.sern) from ttdinv700804
where tdinv700.trdt=(select max(tdinv700.trdt) from ttdinv700804
where tdinv700.cwar=:tdinv700.cwar
and tdinv700.item=:tdinv700.item)
and tdinv700.cwar=:tdinv700.cwar
and tdinv700.item=:tdinv700.item)
and tdinv700.trdt=(select max(tdinv700.trdt) from ttdinv700804
where tdinv700.cwar=:tdinv700.cwar
and tdinv700.item=:tdinv700.item);
as set with 1 rows


as set with 1 rows is used to retrieve a single record from the query with the given conditions

Regards

Darpan

Hi , As set 1 rows statement provides to run your query only one time. But İf you want to get last transaction then you shoul use SELECTEOS
select ***************
from ****
selectdo
---> runs for each selected records
selectoes
---> runs for last selected record
endselect



GOOD LUCK !!!

v_chandra's solution is the correct one. If you only want last record of table, you just do it like that; only 1 record will be read and fetched. Of course if the requirement is just the last record of the table, as Ganesh mentioned, then you leave out the where clause.

It seems by using statement "as set with 1 rows" my purpose does not solves. I want to customize small module for Godawan Stock Status.

For this project transactions are Material Receipt, Issue and accordingly Balance qty should be updated. I planned to create a table having fields as item,tran.type,qty and stock.

If transaction type is Receipt then qty is added in to stock field. If next transaction type is Issue then qty will be less from stock field. These transactions may be more for one item. I want to retrieve Balance Stock qty which is stored in last Transaction record of each item. Here last transaction may be anything (Receipt or Issue). I have used index on item field.

Help me.

Thanx

Ganesh

So if I understand well, you need the last record for each item?? If this is the case, you just have the query sorted on the desired index desc (descending), and you maintain a hold.item variable. Like this:

domain tcitem hold.item

hold.item = ""

select tdinv700.*
from tdinv700
where ...
order by tdinv700._index1 desc
selectdo
if tdinv700.item <> hold.item then
<all_your_actions>
hold.item = tdinv700.item
endif
selectempty
endselect

Hope this helps..

Regards,
En

Hi

You can add one more field in index that could be date or date/time in the index or may be serial number, for first transaction of each item let the serial number be 1 and go on increasing it by one on transaction of that item.

So if you are using date and time then transaction with greatest date or date/time will be last record. Or if you use serial number then last serial number for that item.

In this both option you can retrieve record by using index descending and as set with 1 rows.

Hope this will help you ...

Thanks & Regards

Thanx En and Chandra. Both solutions helpful to me.

Regards,

Ganesh

select <yourtable>.*
from <yourtable>
where <yourtable>._index? = ..
order by <yourtable>._index? DESC
as set with 1 rows
selectdo
endselect
gives you only the last record of a table

as set with <n> rows returns max. <n> rows of a table.

Regards
csecgn