Foxhound is the better* Database Monitor for SQL Anywhere. *better: More thorough, more relevant, more effective. ...more Alerts, more All Clears, more details, more control in your hands.

Watcom 'Rithmetic

Here's today's Pop Quiz: What's wrong with this select?

   select product.name,
          sum ( sales_order_items.quantity
                / 12 ) / 365
     from product,
          sales_order_items
    where sales_order_items.ship_date >= '1994 01 01'
      and sales_order_items.ship_date <= '1994 12 31'
    group by product.name
    order by product.name;

Here's a clue: The select is supposed to calculate average daily sales for 1994, by product, in dozens of units. And no, 1994 wasn't a leap year.

The sales_order_items.quantity column is declared as an integer, and result set looks like this:

   Baseball Cap   4
   Shorts         2
   Sweatshirt     4
   Tee Shirt      7
   Visor          4

That's close, but no cigar! The daily average baseball cap figure is closer to 5 dozen than 4; here's what the result set should look like:

   Baseball Cap   4.8936073
   Shorts         2.4468037
   Sweatshirt     4.8936073
   Tee Shirt      7.3404110
   Visor          4.8936073

It's a Watcom arithmetic problem: When all the operands in an expression are integers the database assumes that all the calculations, intermediate results and the final answer should be integer as well.

The solution is to force the calculation to switch to decimal or floating point arithmetic before any information is lost. One way is to use "12." instead of "12":

   select product.name,
          sum ( sales_order_items.quantity
                / 12. ) / 365
     from product,
          sales_order_items
    where sales_order_items.ship_date >= '1994 01 01'
      and sales_order_items.ship_date <= '1994 12 31'
    group by product.name
    order by product.name;

It's important to force the switch early in the expression. Using "365." instead of "12." will improve the result but won't be perfect since the first division will still use integer arithmetic.

Another perhaps clearer way is to use the cast operator:

   select product.name,
          sum ( cast ( sales_order_items.quantity as real )
                / 12 ) / 365
     from product,
          sales_order_items
    where sales_order_items.ship_date >= '1994 01 01'
      and sales_order_items.ship_date <= '1994 12 31'
    group by product.name
    order by product.name;

Precision problems don't just affect PowerScript code, they can pop up in SQL operations as well. Be careful out there!