Hi there

I hope someone could get me a help.

I have to create an regular extract out of my SQL database.
The extract is collecting data from many different tables, as usual .. ;-)

What I do first is to collect the keys of data records I need to extract (already that is going over various tables with various criterias ..) into a temp table.
Nevertheless that's working well.

In next steps, I complete temp table by adding additional fields and filling them.
In one of this steps, I have now the problem, how to do it best ...

Let my try to explain.
I have a master table country_places like
country, place
CH, A
CH, B
CH, C
AT, D
AT, E
FR, G
...

Then I have a routing table, where I combine order items with place movements, like
order, item, movement sequence, from place, to place
order_1, 1, 1, A, B
order_1, 1, 2 B, D
order_1, 2, 1, A, B
order_1, 2, 2 B, D
order_2, 1, 1, A, G

What I want is to get
- the last place of same country where order starts
- even if item sequence not always starts with 1
- and even if movement sequence may vary from order item to order item


What I did so far is:
SELECT r.from_place
FROM routing r
JOIN key_collection k ON k.order = r.order

WHERE r.from_place IN ( SELECT place FROM country_places cp WHERE cp.country = k.from_country )
AND r.item = ( SELECT MIN(item)
FROM routing r2
WHERE r2.order = r.order
AND r2.from_place IN ( SELECT place from country_places cp WHERE cp.country = k.from_country )
AND r2.movement_sequence = ( SELECT MAX(movement_sequence)
FROM routing r3
WHERE r3.order = r2.order
AND r3.from_place IN ( SELECT place from country_places cp WHERE cp.country = k.from_country )
)

Imaginge, that I have around 200'000 rows in my key_collection.

I'm getting nuts .....

Any help and ideas welcome.


Thanks a lot

P.S. Sysbase Adaptive Server Enterprise/12.5.4

Hi

no-one a tip how to make it faster & better ..... ?

Thx
Wolfgang