AS400 Query: Date Concatenation

25 pts.
AS/400 Query
AS/400 Records
I work for a travel company whose Inventory file uses a unique field for each day of the month, e.g. LGRA1 is the first, LGRA2 is the second etc. The month and the year are single fields.

Is there a way of converting these fields to concatenate the date to be able to link easily to other files? Not sure why they chose to do it this way, maybe someone has experience of this.

Software/Hardware used:

Answer Wiki

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

Yes, you could define the field.
In AS/400 query is concatination cat or || or concat?

anyway if the year and month are character you could define the date as
Year cat Month cat ’01’ and use LGRA1 as the second field,

If they are numeric take Year * 10000 + Month * 100 + 01

I cannot think of a way to incorporate day 2 into the same query so, sad to say you are probably making 31 queries.

Tom .. I didn’t miss the bit about obsolete, old, past prime.. describes me to a ‘T’.

Phil here again.. got it now!!!
You output Query day1 to a new file
You output Query day2 .. day31 to the same file as adds
The result is a file with each day as a seperate row
This file can be used as an input files to a join to other files on date.

Remember that the file you’re creating is an extract of the data, when the original table changes, you will have to rerun the 31 queries to rebuild it.


Discuss This Question: 7  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.
  • WilsonAlano
    Hi, You can try creating a LF with CONCAT. From DDS manual : Example 1 MTH, DAY, and YEAR are fields in the physical file that are concatenated into one field DATE in the logical file, as shown in the following example. |...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 00010A R RECORD1 PFILE(PF1) 00020A DATE CONCAT(MTH DAY YEAR) A Regards, Wilson
    2,710 pointsBadges:
  • TomLiotta
    ...e.g. LGRA1 is the first, LGRA2 is the second etc. Is that meaning that there is no DAY field? I.e., you only know a value for 'day' by looking to see which of the LGRAn columns have values? Tom
    125,585 pointsBadges:
  • Sblackshaw
    That's correct, any row of results will have at least 30 columns to sift through.
    25 pointsBadges:
  • philpl1jb
    I would guess that a view could be made. Assuming that Year and Month are character then someting like this???? Create View Myview as Select Year || Month || '01' , LGRA01 union Select Year || Month || '02' , LGRA02 union Select Year || Month || '03' , LGRA03 etc. You could also make a multi-format logical this way. Phil
    54,090 pointsBadges:
  • Sblackshaw
    Thanks for the answers. Is there a way of doing this in Define Results Fields of AS400 query? I don't have access to a command line unfortunately.
    25 pointsBadges:
  • TomLiotta
    Phil's suggestion might be about as good as it gets, given the questionable design of the file records. It might be modified a little:
    Create View Myview as
    Select Year || Month || ‘01′ , LGRA01 ... where LGRA01 <> 0
    That is, each sub-SELECT might only contribute rows for day numbers when the corresponding LGRAn field has a non-zero value. But that would be a decision you'd have to make. By selecting only non-zero values, the result table could definitely be much smaller, especialy if early in a month when no days had had values set yet. Whether it can be done with WRKQRY will have to be answered by someone very familiar with the product. I haven't used that product much since it went nearly obsolete many years ago. Nothing simple in WRKQRY comes to mind. If Query Manager is available as well, though, it's easy to do. Tom
    125,585 pointsBadges:
  • WoodEngineer
    If the file is not too large, it might be worth writing a program to duplicate the records to a new file for query purposes and add a field for a single date in the format you need.
    8,245 pointsBadges:

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.


Share this item with your network: