Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2008
    Posts
    4

    Red face Unanswered: last record in sub query

    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?

  2. #2
    Join Date
    Nov 2008
    Posts
    65
    Provided Answers: 1
    Forgive my ignorance, what is a/c? Alpha Centauri? Alternating current? Air conditioner?....

    And what is meaning of "last" in this subquery:
    Quote Originally Posted by adinic
    select last(limit) from limit table where current limit = false
    Is it last record sorted asc by creation date? Or something else...

  3. #3
    Join Date
    Dec 2008
    Posts
    4
    Sorry.

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

  4. #4
    Join Date
    Nov 2008
    Posts
    65
    Provided Answers: 1
    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

  5. #5
    Join Date
    Dec 2008
    Posts
    4
    Thanks, i'll give that a go.

  6. #6
    Join Date
    Dec 2008
    Posts
    4

    Thumbs up

    Just to let you know, it works a treat.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •