I'm new to Informix and a 3rd party system we're recently installed @ work is pushing my google-powers to their limit.

I have a table into which a row is inserted everytime an a/c limit is changed. Unfortunately this a/c limit is contained within a a/c details management page, on the 3rd party system, and everytime the user saves their a/c details a new row is inserted (into the a/c limit table) regardless of whether that information has changed . This being a 3rd party system I have no control how it behaves.

The table contains the following -

A/c ref
Limit
Creation date
Current limit (true/false)

My query needs to return all rows contain records within a specific time period where the limit has actually changed. Something like -

select * from limit table were creation date > x and creation date < y and limit <> (select last(limit) from limit table where current limit = false)

Am I attempting the impossible? Should I look @ a code solution? Or am I missing a trick?

Forgive my ignorance, what is a/c? Alpha Centauri? Alternating current? Air conditioner?....

And what is meaning of "last" in this subquery:
Is it last record sorted asc by creation date? Or something else...

Sorry.

A/c ref is the name of a column - short for account reference, this will be unique the for account holder & yes -
would be in creation date order (it's the latest row I'm interested in)

This is pretty unusal query - your subquery does not depend on a/c ref field and it can return multiple values (you can have more non current limits for one last date). If this is true, you cannot use

limit <> (select ...)

because you'll get "subquery does not return one record" error.
Instead use

limit not in (select...)

but, use it only if you do not want to connect outer query with subquery by means of a/c ref.
Your subquery can be something like this:

select limit from ac_limit
where not current_limit
and creation_date = (select min(creation_date) from ac_limit)
;

HTH

Thanks, i'll give that a go.

Just to let you know, it works a treat.

Thanks