Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    2

    Unanswered: Overflow error when specifying filter on calculated fields

    Code:
    SELECT 
    [1997-march-and-april-stats].Symbol, 
    [1997-march-and-april-stats].Date, 
    [1997-march-and-april-stats].PMktOpen, 
    [1997-march-and-april-stats].PMktClose, 
    [PMktClose]-[PmktOpen] AS Delta, 
    ([PMktClose]-[PmktOpen])/[PMktOpen] AS DeltaPct
    
    FROM 
    [1997-march-and-april-stats]
    
    WHERE 
    ((([1997-march-and-april-stats].PMktOpen)<>0) 
    AND 
    (([PMktClose]-[PmktOpen])<>0) 
    AND ((([PMktClose]-[PmktOpen])/[PMktOpen])>0.03)  THIS CAUSES AN OVERFLOW
    AND (([1997-march-and-april-stats].Exchange)="N"))
    ORDER BY [1997-march-and-april-stats].Symbol, 
    [1997-march-and-april-stats].Date;
    Now, if I remove the criteria AND ((([PMktClose]-[PmktOpen])/[PMktOpen])>0.03), everything runs fine.

    Any help?

  2. #2
    Join Date
    Aug 2003
    Location
    Bosnia & Hercegovina
    Posts
    57
    what I see here is that PMktOpen in some case is zero, and that causes overflow (division by zero)....

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...which you could maybe work-around with a simple
    AND ((([PMktClose]-[PmktOpen])/([PMktOpen]+0.00001))>0.03)

    izy

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I was able to duplicate this last night. The overflow only happens when
    both the numerator and denominator are equal to zero. Even though your query tests for both, it doesn't prevent the problem. I tried several versions of nested queries, but always got the same result.

    The only solution I could find was to use:
    AND ((([PMktClose]-[PmktOpen])/[PMktOpen]) between 0.03 and X)

    X could be any number. I don't know why this works and the other method doesn't! I think I actually lost sleep over this one!
    Inspiration Through Fermentation

  5. #5
    Join Date
    Aug 2003
    Posts
    2
    Outstanding! My thanks for the help folks, this was driving me utterly mad!

Posting Permissions

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