How to extract specific data in AS/400

85 pts.
Tags:
AS/400
WRKQRY
Hi there, I want to extract data which has last two digits as 00 (like in 10,000 and 10,001. I need to get only the records of 10,000) by using WRKQRY.

Software/Hardware used:
AS400

Answer Wiki

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

And that field is numeric? yes

Discuss This Question: 12  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.
  • philpl1jb

    Based on this field being numeric

    1. Create a result field Less100 = myField/100 format 8 0 (no decimals)

    2. Create a result field More10 = Less100 * 100

    3. Select records where myField = More100

    54,090 pointsBadges:
    report
  • azohawk

    Convert the numeric data to character and substring the last two characters of the character and use that as a comparison.

     

    3,890 pointsBadges:
    report
  • patnam
    How do I use union condition in WRKQRY?
    85 pointsBadges:
    report
  • philpl1jb
    No union.
    54,090 pointsBadges:
    report
  • philpl1jb
    Why not use SQL?
    54,090 pointsBadges:
    report
  • ToddN2000
    Define a new field converting it to CHAR. then in the select substring, the new work field and look at the last 2 positions.
    115,410 pointsBadges:
    report
  • Splat
    In WRKQRY you'll need to (1) define result fields and (2) select records.

    For the define result fields portion, do this:
    Field       Expression                         Column Heading        Len   Dec
    HUNDREDS    number / 100                                              10     0
                                                                                  
                                                                                  
                                                                                  
    THOUSANDS   hundreds * 100                                            10     0
                                                                                  
                                                         
    For the select records portion do this:
    AND/OR  Field             Test   Value (Field, Number, 'Characters', or ...)
            NUMBER            EQ     thousands                                  
                             
    12,670 pointsBadges:
    report
  • PS2112
    If field is numeric
    SELECT * FROM TAB1
    WHERE (FLD1 - INT(FLD1/100) * 100) =0

    if field is character

    SELECT * FROM TAB1
    WHERE SUBSTR((RTRIM(FLD1),LEN(RTRIM(FLD1)-1),2) = '00'

    95 pointsBadges:
    report
  • patnam
    I got it by defining with new field in Define result fields. But the problem is when i use this new field(like: thousands) in another wrkqry, while joining with another qry and giving ON condition, it's throwing error. As this new field is CHAR type which has numeric value and in the ON condition I'm providing numeric (I need numeric only).
    Specify How to Join Files Field Test Field
    THOUSANDS EQ NUMBER

    ERROR: Field used to test field THOUSANDS not compatible.Is there any way to work with this?
    85 pointsBadges:
    report
  • ToddN2000
    If the field data is going to be referenced elsewhere the select based on the newly defined field but save the original value in your output if it is to be used again. This way you are saving the original numeric value but not the character value you used in the select.
    115,410 pointsBadges:
    report
  • patnam
    But how can i save the original value in output where i will be using the newly defined field data in some other wrkqry?
    85 pointsBadges:
    report
  • ToddN2000
    If you are using this in another WRKQRY then you would need to do the same select method like those mentioned. You still would do your joins on the original field. If the data from the 1st WRKQRY is to be used as input for the 2nd WRKQRY the create an output file. Do this by selecting "Select output type and output form" odd the WRKQRY menu and define the file here. Then use this as your new input file in the 2nd WRKQRY.
    115,410 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.

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

Following

Share this item with your network: