Is there a way to use concatenation with numeric fields using AS/400 Query

105 pts.
Tags:
AS 400
Is there a way to use concatenation with numeric fields with an AS/400 query?

Answer Wiki

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

You may have to do it in 2 steps

Something like this perhaps. Convert the numeric field to a char so you can substring it. Then substring that filed and combine it with the other, 
If the quantity can only be from 1 to 9999 then set the substring parameter to only grab the last 4.

NEWFIELD    digits(field1)                 
NEWFIELD2   substring(newfield,7,4)||otherfield

Discuss This Question: 5  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.
  • TheRealRaven
    What query language (or tool) are you using? How is the concatenated result going to be used? Printed report? Stored in a database field? Shown on a display?
    25,175 pointsBadges:
    report
  • xeroxprinter
    converting character to the numeric character in this way
       Query . . . . . . . . . . . . . . . . . QRYDATE5                           
          Library . . . . . . . . . . . . . . . QRYLIB                             
        Query text  . . . . . . . . . . . . . . Converting character to numeric    
        Selected files                                                             
          ID     File           Library       Member        Record Format          
          T01    DATES          QRYLIB        *FIRST        DATER        
    20 pointsBadges:
    report
  • azohawk
    If you are using Query/400: in the define results fields, first convert the number to a character value using the DIGITS keyword, then you can substring (substr) and/or concatenate '||' the character value.  I do this often with dates that are stored as numeric cyymmdd that we want to appear in a more conventional mm/dd/yy format.
    3,505 pointsBadges:
    report
  • queryhelpserena
    My problem is query only allows 9 breaks and I've used the concatenate on all the character fields. I need one more break which is not possible. I'd like to concatenate one more field and it happens to be numeric. I used the digits to make it a character field. The problem is the quantity result instead of 10 is 000000000000010. I can't edit the field to suppress leading zeros. This field can contain anything between 1 and 9999 so I can't substring it either.
    105 pointsBadges:
    report
  • ToddN2000
    Sounds like the job may be to involved for a query if you are using all the current breaks and are looking to concatenate fields. What is the initial reason for the concat and what tow fields are you looking to combine and the desired result of this task? 
    104,480 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: