Hi Guys,

SOS !! Urgent

I have been trying to retrieve the last records from a table with various means and methods , although using some codes was able to get the correct results but it takes too much time. :mad:
I need something like Oredr by tdpur041.odat desc limit 1 / "select top 1* from titim001315" / TOP N :cool: results in 4 GL for a report
code am trying to use is somewhat like

function read.main.table()

{
select tihyv701.*, tiitm001.*
from tihyv701, tiitm001
where tihyv701.item between :item.f and :item.t
and tiitm001.kitm = 1
and tihyv701.sitm refers to tiitm001
selectdo
date = tiitm001.ltpp
currency = tiitm001.ccur
price = tiitm001.prip
select tdpur041.*, tdpur040.*
from tdpur041, tdpur040
where tdpur041.item = :tihyv701.sitm
and tdpur041.odat > :date
and tdpur041.orno refers to tdpur040
order by tdpur041.odat desc
selectempty
rprt_send()
selectdo
date = tdpur041.odat
currency = tdpur040.ccur
price = tdpur041.pric
rprt_send()
endselect
|rprt_send()
endselect
}

===========================
or may be

{
select tihyv701.* from tihyv701
where tihyv701.item between :item.f and :item.t
|as set with 1 rows
selectdo
select tiitm001.item from tiitm001
where tiitm001.item = :tihyv701.sitm
and tiitm001.kitm = 1
|as set with 1 rows
selectdo
select tdpur041.* from tdpur041
where tdpur041.item = :tiitm001.item
order by tdpur041.odat desc
|as set with 1 rows
selectdo
rprt_send()
endselect
endselect
endselect
}
==================================

or even this will do

select tihyv701.*, tiitm001.item, tiitm001.dsca, tdpur041.orno, tdpur041.pono, tdpur041.odat, tdpur041.cupp, tdpur041.pric, tdpur041.suno, tdpur040.ccur
from tihyv701, tiitm001, tdpur041, tdpur040
where tihyv701.item between :item.f and :item.t
and tihyv701.sitm refers to tiitm001
and tiitm001.kitm = 1
|and tdpur041.odat between :date.f and :date.t
and tdpur041.item refers to tiitm001
and tdpur041 refers to tdpur040
order by tdpur041.odat desc
selectdo
rprt_send()
endselect
===========================

Would request you all to help ASAP as this has taken me too long tthan ai expected and noe its super urgent

Thanks:)

select top 1* from ttdpur041315 where t_item in (select t_item from ttiitm001315 where t_kitm = 1 and t_item in (select t_sitm from ttihyv701315 where rtrim(ltrim(t_item)) = '10400000S'))
order by t_odat desc


I need something like this..SQL server to BaaN 4 GL script

This retrieves only the last record :


select tablefields
from tables
where conditions
order by field desc
as set with 1 rows
selectdo
.................
endselect

Hello,

Try to use

select *
from XXYYYZZZ
where XXYYYZZZ.AAAA = :BBBB
selectdo

selecteos

selectempty

endselect

I hope this will work fine.

Regards
Dharam

Thanks for the reply .. tried this out already but it takes to much time..to retrieve .. still will chk it again

Regards,
Heramb

Not sure what the tihyv701 table is but try as follows:
[This is just an example, since I am not clear what the exact requirement is].


|* define p.curr.item.c as a variable

select tihyv701.sitm:p.curr.item.c
from thhyv701,
tiitm001
where tihyv701.item inrange :item.f and :item.t
and tiitm001.kitm = tckitm.purchase
and tihyv701.sitm refers to tiitm001
group by tihyv701.sitm
selectdo
|* So now we have the list of 'purchased' sitm we are interested in.
|* This is just a trick to avoid going through ALL the sitm
|* in the item master.
select tdpur041.*,
tiitm001.*
from tdpur041,
tiitm001
where tdpur041._index7 = {:p.curr.item.c} |At least in BAAN IV c3
and tiitm001._index1 = {tdpur041.item}
and tdpur041.odat > tiitm001.ltpp
order by tdpur041.odat desc
as set with 1 rows
selectdo
|* Now we have the LAST record ordered by odat - print it.
rprt.send()
endselect
endselect

Try this:

select tn.*
from tn
where cond.

as set with 1 rows

order by tn._index1 desc |This will sort the record in descending order

Hope this would help

Not sure what the tihyv701 table is but try as follows:
[This is just an example, since I am not clear what the exact requirement is].


|* define p.curr.item.c as a variable

select tihyv701.sitm:p.curr.item.c
from thhyv701,
tiitm001
where tihyv701.item inrange :item.f and :item.t
and tiitm001.kitm = tckitm.purchase
and tihyv701.sitm refers to tiitm001
group by tihyv701.sitm
selectdo
|* So now we have the list of 'purchased' sitm we are interested in.
|* This is just a trick to avoid going through ALL the sitm
|* in the item master.
select tdpur041.*,
tiitm001.*
from tdpur041,
tiitm001
where tdpur041._index7 = {:p.curr.item.c} |At least in BAAN IV c3
and tiitm001._index1 = {tdpur041.item}
and tdpur041.odat > tiitm001.ltpp
order by tdpur041.odat desc
as set with 1 rows
selectdo
|* Now we have the LAST record ordered by odat - print it.
rprt.send()
endselect
endselect



Thank You Mr. Shah will try this out...looks like ite will optimize the Query very nicely

its a gr8 advice