Dear All,

I want to write a update query in baan.

Query:in the session tdsls0104 in want to change the expiry date of the previous line of the last line to yesterday's date.

my query is here:

functions:
function read.main.table1()
{
db.retry.point()
select tdsls032.*
from tdsls032 for update
where tdsls032.tdat > date.num()

selectdo
|selecteos
if tdsls032.tdat > date.num() then
tdsls032.tdat = date.num()-1
db.update(ttdsls032,db.retry)
commit.transaction()

endif
message("THE PRICES ARE UPDATED NOW")
endselect


}

The problem with this query is it updates all the lines to yesterday's date .

I am not familiar with this table, but just looking at the query and the code it is getting all records where the expiry date is greater than today. Then it updates all of those records to today-1. Is there one or multiple records to be updated? Do you have the item or other information to go on? Are you just trying to roll thru tdsls032 and update certain records?

dear Mark,

I want to compare two records as in image"Price".
It contains the expiry date "31-12-2012".
and i want to update the (last -1)th record to today's date.

I assume that your table has a field tdsls032.pono for position number.
We can have a few modification to the query, this will update last-1 record.

functions:
function read.main.table1()
{
db.retry.point()
select tdsls032.*
from tdsls032 for update
where tdsls032.tdat > date.num()
order by tdsls032.pono desc
as set with 2 rows
|selectdo
selecteos
if tdsls032.tdat > date.num() then
tdsls032.tdat = date.num()-1
db.update(ttdsls032,db.retry)
commit.transaction()
endif
message("THE PRICES ARE UPDATED NOW")
endselect


}

no, i don't have a tdsls032.pono field.

In that case,
try:
order by tdsls032._index1 desc

Well I like to keep things simple, maybe not the best, but below is how I would do something like this. I did not check the code, declare variables but something like this should work to roll through whole table. I also did not take into account the quantity or effective date - you could build those in if really needed.

| Get all parts regardless of the expiry date. Here you can limit if you
| want with a.tdat = date.num()
select a.cpsl:cpsl, a.item:Item, a.cntr:Cntr, a.dile:dile
from tdsls032 a
group by a.cpsl, a.item, a.cntr, a.dile
selectdo
|Now go get last expiry date. This should get the latest
|expiry date.
select b.tdat:expdate
from tdsls032 b
where b._index1 = {:cpsl, :item, :cntr, :dile}
order by b.tdat desc
as set with 1 rows
selectdo
endselect

| Now get and update the second to last date
db.retry.point()
select tdsls032.*
from tdsls032 for update
where tdsls032._index1 = b._index1 = {:cpsl, :item, :cntr, :dile}
and tdsls032.tdat < :expdate
order by tdsls032.tdat desc
as set with 1 rows
selectdo
endselect
tdsls032.tdate = date.num() - 1
db.update(ttdsls032,db.retry)
commit.transaction()
endselect

Dear Mark,

Thank you for your reply.
I want to update last 2nd row after comparing
But this script updates the last row itself.
How can I update the last 2nd row?
this is my script
functions:
function read.main.table1()
{
select a.cpls:cpls, a.item:item, a.cntr:cntr, a.dile:dile
from tdsls032 a
group by a.cpls, a.item, a.cntr, a.dile
selectdo

|*************************SELECT THE LAST ROW**********************************************************

select b.tdat:expdate
from tdsls032 b
where b._index1 = {:cpls, :item, :cntr, :dile}
order by b.tdat desc
as set with 1 rows
selectdo
endselect

|****************************************SELECT THE (LAST-1)TH ROW***************************************

select c.tdat:expdate2
from tdsls032 c
where c._index1 = {:cpls, :item, :cntr, :dile}
order by c.tdat desc
as set with 2 rows
selectdo
endselect
|****************************************COMPARE THE LAST TWO ROWS ****************************************
|***************************************** UPDATE (LAST-1)TH ROW IF BOTH EXPIRY DATES ARE EQUAL*************
if expdate2 = expdate then
db.retry.point()
select tdsls032.*
from tdsls032 for update
where tdsls032._index1 = {:cpls, :item, :cntr, :dile}
|and tdsls032.tdat < :expdate
| order by tdsls032.tdat desc
| as set with 2 rows
selectdo
selecteos
tdsls032.tdat= date.num() - 1
db.update(ttdsls032,db.retry)
commit.transaction()
endselect
else
|: message("Expiry Date from the last two rows are not equal")
endif
endselect
}

Yep - I just noticed it has the same expiry date as the last row. Just change the sort to using the effective date. Keep in mind I do not know your data, and am assuming the second to last record will have an effective date < the last record. Or if you know the last two records have the same expiry date then you could find it with "as set with 1 rows" to get first row(which we be second from end).


select a.cpsl:cpsl, a.item:Item, a.cntr:Cntr, a.dile:dile
from tdsls032 a
group by a.cpsl, a.item, a.cntr, a.dile
selectdo
|Now go get last expiry date. This should get the latest
|expiry date.
select b.tdat:expdate, b.stdt:effdate
from tdsls032 b
where b._index1 = {:cpsl, :item, :cntr, :dile}
order by b.tdat desc
as set with 1 rows
selectdo
endselect

| Now get and update the second to last date
db.retry.point()
select tdsls032.*
from tdsls032 for update
where tdsls032._index1 = b._index1 = {:cpsl, :item, :cntr, :dile}
and tdsls032.stdt < :effdate
order by tdsls032.tdat desc
as set with 1 rows
selectdo
endselect
tdsls032.tdate = date.num() - 1
db.update(ttdsls032,db.retry)
commit.transaction()
endselect