WRKQRY query

2505 pts.
Tags:
AS/400
CL Program
WRKQRY
Hi, I have a CL program which uses WRKQRY to generate a report out of it. Now I have a requirement to change a field which holds Quantity and I want to divide this by 10 to the power of another field say DecFld. ie if Quantity is 359686 and DecFld is 2, I want 359686/10**2 = 3596.86 . Is it possible to do this kind of 10 to the power in 'DEFINE RESULT FIELD' part? If so please give me the syntax for that.

Software/Hardware used:
As400

Answer Wiki

Thanks. We'll let you know when a new response is added.

Create one result field that is 10 * the decimal field.

Then create another result field that is Quantity / the 1st result field

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • philpl1jb

    No, doesn't appear that it can be done ...

     but you could make one query for the rows with DecFld = 1 and others for DecFld = 2, DecFld = 3 .. etc

    Have all queries output to the same file.  Sort the field result with another query and the result might look like what you want. 

    Possibly you want to use something other than query/400.

    50,425 pointsBadges:
    report
  • TomLiotta

    A QM query would do it easily. It could also be done by creating a VIEW that presents the calculated value. I don't know of a way to do it just within WRKQRY.

    Tom

    125,585 pointsBadges:
    report
  • PGMBOB
    WRKQRY from the command book:

    The Work with Query (WRKQRY) command shows the Work with Queries display. You can use this display to create, display, change, copy, delete, or print a query definition, or to run a query.

    There are no parameters for this command.

    Thyere must be a RUNQRY (qryname)

    the (qryname) definitions can be copied and chnged.

    Add a new field RESpower = 10**decfld

    Add a new field RESFLD = QTY/RESpower.

    Add RESFLD to your printed or file output

    Copy and try it before changing the repor in production.

    Good Luck!

    PGMBOB

    1,120 pointsBadges:
    report
  • RamvishakRamesh

    I tried to define result field RESPower with Expression 10**DecFld, but it says expression is not correct.

    2,505 pointsBadges:
    report
  • TomLiotta

    At least up to i 6.1, exponentiation (**) is not listed as a valid Query for i numeric operator. I haven't tried it, so maybe the documentation is wrong. The OP says it gives an error, and that's consistent with documentation.

    Create one result field that is 10 * the decimal field.

    I don't see how that would give the correct result. When DecFld=2, the desired result should be (100). But "10 * the decimal field" would only give (20) as a result.

    Tom

    125,585 pointsBadges:
    report
  • philpl1jb
    I can solve this problem provided the DecFld value is between 1 and 4 but the solution isn't worth the effort. It seemed that I should be able to get it to work for up to 6 but here is what I've got.. I for one, would never choose Query/400 for this issue. MYFIELD3 substr('00000',1,4-DecFld)||'1' TIMESTAMP '1988-12-25-16.30.00.00' || myfield3 NUMERIC6 microsecond(timestamp) 6 2 MYFIELDOUT Quantity*numeric6
    50,425 pointsBadges:
    report
  • philpl1jb

    oh .. wrong direction .. something like 

    MYFIELDOUT Quantity/numeric6

    50,425 pointsBadges:
    report
  • TomLiotta

    Phil has a solid possibility.

    MICROSECOND() could be used, but it would seem that (10**5) would be the maximum possible value since it takes up six digits and microseconds is a six-digit value. The sequence of powers of 10 from (10**0) through (10**5) would be:

    0 1
    000001
    1 10
    000010
    
    2 100
    000100
    
    3 1000
    001000
    
    4 10000
    010000
    
    5 100000
    100000

    The values can all be extracted as a substring of six characters from a string consisting of the characters '00000100000'. By varying the starting position, all the different values from '000001' through '100000' can be extracted. I think the initial substring should look like this:

    MYFIELD3   substr(’00000100000′,DecFld,6)

    So, when DECFLD has value (1), the first six digits are taken to result in '000001'; and when the value is (5), the last six result in '100000'.

    When the result characters are used to generate the microseconds portion of a timestamp value, the MICROSECONDS() result gives a numeric result ranging from (1) to (100000).

    But the DECFLD value is actually offset by (1) position since (10**0) has a value of (1), and the SUBSTR() function can't use an offset value of (0). So DECFLD needs to be incremented by (1) to give the correct starting offset. To make it seem more straightforward, how about 'Result' fields looking like this:

    Field    Expression                       Column Heading   Len  Dec
    
    DECFLD   ( 5 )                            df                 3   0
    PRE      ( DECFLD + 1 )                   pre                3   0
    PWRC     substr('000001000000',PRE,6)     pwrc
    TSTP     '1988-12-25-16.30.00.' || PWRC   tstp
    NUM6     microsecond( TSTP )              num6               6   0

    That shows DECFLD being defined as a 'Result' field, but that line is only included for testing. It should be deleted if DECFLD is a field in the file being queried.

    Good idea, Phil. Too bad MICROSECOND() is the only way to get numeric values out of characters.

    Tom

    125,585 pointsBadges:
    report
  • philpl1jb
    "Egads! What sorcery is this? There was enough paralyzing gas in that cork to keep ordinary men unconscious for hours!" - Joker
    50,425 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following