I need to show last movement on my report.

I spent 1 day to print this report.

tiitm001 = 5000 record , item
tdilc301 = 720,000 record.

Have you anonter solution. ?

select tiitm001.item,tiitm001.csig
from tiitm001
order by tiitm001.item
selectdo
select tdilc301.*
from tdilc301
where tdilc301.item =:tiitm001.item
order by tdilc301.trdt desc
as set with 1 rows
selectdo

endselect
endselect

I hope you understand what's my point.

Thanks ,
Nopadon Thailand.

The next will be faster, I think:

domain tcitem old.item
old.item=""
select tdilc301.*,tiitm001.item,tiitm001.citg
from tdilc301,tiitm001
where tiitm001.item=tdilc301.item
order by tiitm001.item, tdilc301.trdt desc
selectdo
if old.item<>tiitm001.item then
print.record()
old.item=tiitm001.item
endif
endselect

I don't know if this will improve the last piece of code, but you try incorporating references:

...
select tdilc301.*,tiitm001.item,tiitm001.citg
from tdilc301,tiitm001
where tdilc301.item refers to tiitm001
order by tiitm001.item, tdilc301.trdt desc
selectdo
...
endselect


Whether you need the item/old.item concept depends on what you want to send to the report. If would help to include range limits here if applicable (e.g., [project, item] tdilc.301_index1 inrange {:cprj.f, :item.f} and {:cprj.t, :item.t}). Since you are only referencing tiitm001, I assume you don't want any customized items, in which case you can replace :cprj.f amd :cprj.t with "".

First of Thing , Thanks for everyone attention my question.

I have baan try your script but when I running report , it's use more than 6 hours for print result.

Do you have anonter choise for coding or special function for clear memory or ...... ?

Table tiitm001. total record is 5000 record.
Table tdilc301. total recode is 700,000 record.

Concept is show last movement for all Item.


Thanks
Nopadon Thailand.

Hi,

6 hours is better than 1 day ;)

I have test environment on low performance machine (Baan IVc3, Oracle 7.3.4).

tiitm001 - 2500
tdilc301 - 150000

The script executes for about 3 minutes.

What is your database?

If possible try to archive transaction by location (tdilc3211m000).

Some databases don't actually like that 'refers to' in the select. (ok, granted, tbase may be the one, I forget). To be sure you are using indexes, try the following:


select tiitm001.item,tiitm001.citg, tdilc301.*
from tiitm001, tdilc301
where tdilc301._index1 = {"", tiitm001.item}
order by tiitm001.item, tdilc301.trdt desc
selectdo
...
endselect


There are some ways to play with the group by clause, maybe that would help, but I think it just may slow it down more. Because of the number of records in the tdilc301 table, your original separate query may be faster, as it limits with the "as set with 1 rows", if you use an index:



select tiitm001.item,tiitm001.csig
from tiitm001
order by tiitm001.item
selectdo
select tdilc301.*
from tdilc301
where tdilc301._index1 = {"", :tiitm001.item}
order by tdilc301.trdt desc
as set with 1 rows
selectdo

endselect
endselect

Another possibility is to use max() combined with group by

extern domain tcdate max.date

select tdilc301.item, max(tdilc301.trdt):max.date
from tdilc301
group by tdilc301.item
order by tdilc301.item
selectdo
select tiitm001.csig
from tiitm001
where tiitm001._index1 = {:tdilc301.item}
selectdo
...
endselect
endselect

if performance is bad then check if you are faster using Easy-Sql.
If so use dynamical sql instead of embedded sql.

Greetings
Andy

If you don't have customized items, then why not forget about the tiitm001 table and use tdilc301._index1 and let the report pick up the signal code on a break.

:confused:

User Standard Item tiitm001

Search tdilc301 for print last transaction.

Ex. Table tiitm001
item Signal Code
AA01 OBS
BB02 -
CC03 -
DD04 -

Ex. Table tdilc301
item Transaction Date
CC03 01/28/2002
CC03 01/25/2002


Report Should be show
item Sinnal Code Transaction Date
AA01 OBS -
BB02 - -
CC03 - 01/28/2002
DD04 - -

That's mean , Main Table should be tiitm001 for print all item
and then search Last transaction on tdilc301

How to Solving for use times for print result.

Are we no choice ? Any one have special Idea ?
( BaaN Programming)

Assume ( No increase Server , No Increase REM , No Increase Lease Line , No Use Another Application Like VB link direct to Oracle Database.)


Thanks
Nopadon

Thanks Agramm

You script is faster. I have been testing.
I will adapt on my script.

extern domain tcdate max.date

select tdilc301.item, max(tdilc301.trdt):max.date
from tdilc301
group by tdilc301.item
order by tdilc301.item
selectdo
select tiitm001.csig
from tiitm001
where tiitm001._index1 = {:tdilc301.item}
selectdo
...
endselect

endselect

Thanks ..... Nopadon
:)