• 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
    Maxava

    Migrate IBM i with Confidence

    Tired of costly and risky migrations? Maxava Migrate Live minimizes disruption with seamless transitions. Upgrading to Power10 or cloud hosted system, Maxava has you covered!

    Learn More

    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

  • Public Preview For Watson Code Assistant for i Available Soon
  • COMMON Youth Movement Continues at POWERUp 2025
  • IBM Preserves Memory Investments Across Power10 And Power11
  • Eradani Uses AI For New EDI And API Service
  • Picking Apart IBM’s $150 Billion In US Manufacturing And R&D
  • FAX/400 And CICS For i Are Dead. What Will IBM Kill Next?
  • Fresche Overhauls X-Analysis With Web UI, AI Smarts
  • Is It Time To Add The Rust Programming Language To IBM i?
  • Is IBM Going To Raise Prices On Power10 Expert Care?
  • IBM i PTF Guide, Volume 27, Number 20

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