AS400 Query: Date Concatenation
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:
AS400
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:
AS400
ASKED:
August 2, 2011 9:20 PM
UPDATED:
March 31, 2012 7:20 PM
Answer Wiki:
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.
Phil
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.
Phil
To see all answers submitted to the Answer Wiki: View Answer History.
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
…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
That’s correct, any row of results will have at least 30 columns to sift through.
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
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.
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:
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
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.