Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2001
    Location
    Washington, DC
    Posts
    24

    Unanswered: "Datetime field overflow" w/ MAX in subselect?

    Hey guys---hoping you can help with a problematic query. When I run it, I get an error, copied here from the ColdFusion application of which it's a part: "ODBC Error Code = 22008 (Datetime field overflow) [MERANT][ODBC SQL Server Driver][SQL Server]Syntax error converting character string to smalldatetime data type."

    And here's the query:

    SELECT *
    FROM Stories
    WHERE (StoryID IN
    (SELECT MAX(PublishTime) AS PublishTime
    FROM Stories
    WHERE (Fixture_Code IN ('test1', 'test2'))
    GROUP BY Fixture_Code))

    The subselect works great when run by itself; it's only when it's a subselect that I get type errors. PublishTime is a smalldatetime column. The above query actually runs without an error if my values for WHERE IN are values that don't exist in the the Fixture_Code column anywhere---obviously I get back an empty set, but no error.

    In case it's not clear, I'm trying in the subselect to pick the single most recent record for each of a list of fixture codes, so that I can then go back and get full information on each of those stories. If someone can tell me a better way to, essentially, do a query where the result set contains only one row (the most recent) for each of my "fixture codes", I'm open to that too.

    Thanks again for any help.

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    The error occurs because you are comparing PublishTime (smalldatetime) from the subselect with StoryID (I assume integer or character). Like compare apples to oranges. You could try this:
    Code:
    SELECT s.* 
    FROM Stories s,
         (
         SELECT MAX(PublishTime) AS PublishTime,
                Fixture_Code
         FROM Stories 
         GROUP BY Fixture_Code
         )    x
    WHERE s.Fixture_Code = x.Fixture_Code
    AND   s.PublishTime = x.PublishTime
    So this will return the most recent record for each Fixture_Code.
    MCDBA

  3. #3
    Join Date
    Aug 2001
    Location
    Washington, DC
    Posts
    24

    Smile

    Ahh, okay. That makes perfect sense. Your code works great... thanks a lot for the help; as you may have guessed I'm still learning the elusive art of subselects :)

  4. #4
    Join Date
    Aug 2001
    Location
    Washington, DC
    Posts
    24
    Although, when I tweak the query to specify only the list of fixtures I want, plus bring in another table join to get a fixture name in addition to the fixture code, I get one row that's duplicated in the results.

    SELECT s.*, Fixture_Name
    FROM Stories s INNER JOIN
    (SELECT MAX(PublishTime) AS PublishTime, Fixture_Code
    FROM Stories
    GROUP BY Fixture_Code) x
    JOIN FixtureCodes ON x.Fixture_Code = FixtureCodes.Fixture_ID
    ON s.Fixture_Code = x.Fixture_Code AND s.PublishTime = x.PublishTime AND s.Fixture_Code IN ('test1','test2','test3','test4','test5','test6',' test7','test8')

    Very easily taken care of with DISTINCT but if anyone cared to further edify me with the reason that's happening, I'd be grateful :) Thanks again to all.

Posting Permissions

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