• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Circumventing Integer Division

    March 16, 2011 Hey, Ted

    My computer seems to have forgotten how to divide. When I divide one value by another, using SQL, the answer is always exactly one or zero. What gives?

    –RPG Professional

    This took a little while, but we finally found his problem. First, the background.

    Assume a database file with three fields: customer ID, the number of orders from that customer, and the number of shipments to the customer.

    select CustID, Orders, Shipments
      from Summary  
              
    CUSTID  ORDERS       SHIPMENTS
         1      75              25
         2      10              16
         3      20               0
         4       8               8
    

    Now let’s add a ratio to the query to gauge order fulfillment.

    select CustID, Orders, Shipments, 
           Shipments / Orders as Ratio
      from Summary              
    CUSTID  ORDERS       SHIPMENTS           RATIO
         1      75              25               0
         2      10              16               1
         3      20               0               0
         4       8               8               1
    

    The problem, as it turned out, was the orders and shipments fields were defined as integer. The programmer had used SQL to create the summary table on the fly, and the system had inferred that the two fields would always contain whole numbers.

    Since the two fields were integer fields, SQL used integer division. According to the SQL reference:

    If both operands of an arithmetic operator are integers with zero scale, the operation is performed in binary, and the result is a large integer unless either (or both) operand is a big integer, in which case the result is a big integer. Any remainder of division is lost.

    To get a result with decimal positions, I had him use the DOUBLE function over the two fields. Since both operands were double precision, the system carried out division of real numbers. To get a readable result, I had him use the DEC function.

    select CustID, Orders, Shipments,
           dec(double(Shipments) / double(Orders),4,3) as Ratio
      from Summary
    
    CUSTID          ORDERS       SHIPMENTS   RATIO
         1              75              25    .333
         2              10              16   1.600
         3              20               0    .000
         4               8               8   1.000
    

    RPG also supports integer division, if you want it. Use the %DIV built-in function.

    eval Ratio = %div(Shipments: Orders);
    

    Integer division has its purposes, but for business computing, I have rarely used it.

    –Ted



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    DRV Technologies, Inc.

    DRV’s FlexTools family of software solutions streamline resources, improve efficiency and enable pro-active system management.

    Solutions include:

    • Convert spooled files to Excel & PDF
    • Automated email distribution & archive
    • Electronic forms to print, email & archive
    • Secure laser check printing
    • IBM i system monitoring

    Better software, better service, DRV Tech.

    Learn how you can get more from your IBM i at www.drvtech.com

    Call 866 378-3366 for a Free Demonstration

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    SEQUEL Software:  FREE Webinar: Overcoming query limits with SEQUEL. March 23
    SkyView Partners:  The IBM i security compliance provider who does all the heavy lifting
    COMMON:  Join us at the 2011 Conference & Expo, May 1 - 4 in Minneapolis, MN

    IT Jungle Store Top Book Picks

    BACK IN STOCK: Easy Steps to Internet Programming for System i: List Price, $49.95

    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The iSeries Pocket Database Guide: List Price, $59
    The iSeries Pocket SQL Guide: List Price, $59
    The iSeries Pocket WebFacing Primer: List Price, $39
    Migrating to WebSphere Express for iSeries: List Price, $49
    Getting Started with WebSphere Express for iSeries: List Price, $49
    The All-Everything Operating System: List Price, $35
    The Best Joomla! Tutorial Ever!: List Price, $19.95

    VAI Completes Dual ERP Integration for Two Distribution Companies Raz-Lee Feeds IBM i Data into RSA SIEM

    Leave a Reply Cancel reply

Volume 11, Number 9 -- March 16, 2011
THIS ISSUE SPONSORED BY:

WorksRight Software
SEQUEL Software
Botz & Associates, Inc.

Table of Contents

  • Duplicating CPYF Function Using SQL
  • Circumventing Integer Division
  • Admin Alert: Corralling i/OS Storage Hogs, Part 2

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • The IBM i Power10 Upgrade Cycle Forecast Looks Favorable
  • White Hats Completely Dismantle Menu-Based Security
  • Cloud Software To Drive Enterprise Application Growth
  • How Do You Stay In Touch With The IBM i Community?
  • IBM i PTF Guide, Volume 25, Number 6
  • Security Still Top Concern, IBM i Marketplace Study Says
  • Bob Langieri Shares IBM i Career Trends Outlook for 2023
  • Kisco Brings Native SMS Messaging to IBM i
  • Four Hundred Monitor, February 1
  • 2023 IBM i Predictions, Part 4

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.