• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Query/400 Does Exponentiation, Sort Of

    June 8, 2005 Ted Holt and Bob Ellsworth

    Dear Colleagues:

    Query/400, the software IBM never got around to completing, does not have an exponentiation operator, but that did not stop Bob Ellsworth from making Query carry out exponentiation. If you’ve seen Bob’s tips before, you may remember that Bob is the guy who makes Query do what it was never designed to do. Here’s the technique Bob shared with me to answer a question from a reader.

    –Ted

    I am new to IBM and Query/400 and have a problem with a result field. I am working with two pieces of data:

    1. Cost with no decimals (e.g., 300)

    2. Division factor (e.g., 2)

    The division factor is the power of 10 by which the cost should be divided to get the true cost. For example, here is the cost 300 adjusted for factors of 2 and 3.

    300/(10^2) = 3.00
    300/(10^3) = .300
    

    How can I make Query divide by the power of 10 indicated by the division factor?

    –Jack

    You came up with a good one. I love the ones that look very easy to answer, until I go to test it and find that there really is no exponential function.

    Create a fixed table of factors, from which you can substring out the correct multiplier based on the division factor.

    Field

    Expression

    Len

    Dec

    FACTORTBL

    ‘00000100001000010000100001000010

     

     

     

    0000′

     

     

    F

    microsecond(timestamp(‘2005-01-01

     

     

     

    -12.00.00.’||substr(factortbl,fac

     

     

     

    tor*6+1,6)))

     

     

    ADJCOST

    Cost / f

    13

    7

    Output looks like this:

    COST   FACTOR          ADJCOST
     300      0        300.0000000
     300      1         30.0000000
     300      2          3.0000000
     300      3           .3000000
     300      4           .0300000
     300      5           .0030000                 
    

    This technique is a little limited, though, in that the division factor can only range from 0 to 5.

    –Bob Ellsworth


    I’ll add just a few words to Bob’s solution.

    First, if you want to allow the division factor to range up to 9, add a second table.

    Field

    Expression

    Len

    Dec

    FACTORTBL1

    ‘00000000000000000000000000000000

     

     

     

    0000000001000010000100001000′

     

     

    FACTORTBL2

    ‘00000100001000010000100001000010

     

     

     

    0000000000000000000000000000′

     

     

    F1

    microsecond(timestamp(‘2005-01-01

     

     

     

    -12.00.00.’||substr(factortbl1,fa

     

     

     

    ctor*6+1,6)))

     

     

    F2

    microsecond(timestamp(‘2005-01-01

     

     

     

    -12.00.00.’||substr(factortbl2,fa

     

     

     

    ctor*6+1,6)))

     

     

    ADJCOST

    cost / (f1 * 1000000 + f2)

    15

    9

    Here’s the output:

    COST   FACTOR            ADJCOST 
     300      0        300.000000000 
     300      1         30.000000000 
     300      2          3.000000000 
     300      3           .300000000 
     300      4           .030000000 
     300      5           .003000000 
     300      6           .003000000 
     300      7           .000300000 
     300      8           .000030000 
     300      9           .000003000
    

    The second thing I’ll say is that this example is just one more reason to dump Query/400 and get a decent query package. There are some good ones on the market.

    –Ted

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    ARCAD Software

    Embrace VS Code for IBM i Development

    The IBM i development landscape is evolving with modern tools that enhance efficiency and collaboration. Ready to make the move to VS Code for IBM i?

    Join us for this webinar where we’ll showcase how VS Code can serve as a powerful editor for native IBM i code and explore the essential extensions that make it possible.

    In this session, you’ll discover:

    • How ARCAD’s integration with VS Code provides deep metadata insights, allowing developers to assess the impact of their changes upfront.
    • The role of Git in enabling seamless collaboration between developers using tools like SEU, RDi, and VS Code.
    • Powerful extensions for code quality, security, impact analysis, smart build, and automated RPG conversion to Free Form.
    • How non-IBM i developers can now contribute to IBM i projects without prior knowledge of its specifics, while ensuring full control over their changes.

    The future of IBM i development is here. Let ARCAD be your guide!

    Watch Now

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Raz-Lee Ships New iSeries Security Software GST Speeds Backups with New Virtual Tape Library

    2 thoughts on “Query/400 Does Exponentiation, Sort Of”

    • Ron Maltase says:
      February 3, 2024 at 2:54 am

      Hello, I’m trying to speak with the Bob Ellsworth, who wrote the 9/30/2000 article “The Hidden Power of Query, Part 1” on the the MC PRESS website. -Thanks, Ron Maltase

      Reply
      • Timothy Prickett Morgan says:
        February 4, 2024 at 11:47 am

        Why not contact MC Press? That’s not us.

        Reply

    Leave a Reply Cancel reply

Volume 5, Number 22 -- June 8, 2005
THIS ISSUE
SPONSORED BY:

WorksRight Software
Advanced Systems Concepts
Guild Companies

Table of Contents

  • Error Checking and Embedded SQL
  • Query/400 Does Exponentiation, Sort Of
  • Admin Alert: A Better Technique for Detecting Invalid Log-In Attempts

Content archive

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

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

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 © 2025 IT Jungle