Derived field text in logical file
I have a logical file I need to create in a separate environment(inqhist). The file exist in our company's primary environment, but we don't have the source for the file. When I use the dspffd command I see three of the fields I need with the description 'derived field text'.
Software/Hardware used:
AS/400
They are date fields that take 1 date field from an order entry file and convert it to three different fields. One is mm/dd/yr(8), the other is mm/dd/year(10) and the final field is year-mm-dd(10). The three fields are converted character fields and not date fields. The program that runs it is dependent on this file being populated with these fields as described above.
I need assistance in creating the new logical file with the three fields.
Software/Hardware used:
AS/400
ASKED:
May 19, 2010 8:20 PM
UPDATED:
May 21, 2010 3:54 PM
Answer Wiki:
I apologize. You originally asked about a "logical file" that had no "source". I assumed that you meant the DDS source for a LF. However, you are showing that it is actually a SQL VIEW.
Therefore, you do have the source.
So, take the source, change the names of the libraries and use it in a CREATE VIEW statement to create the view in the library that you need. You can paste the source into a source member and execute it with RUNSQLSTM, or you might paste it into the Run SQL scripts... option of iSeries Navigator. Make sure that you CREATE VIEW csaldat.INQHIST to create it in csaldat.
Tom
========================================================
Here are some of the attributes from the dspffd command on our file inqhist in library savainddat.
I need to create the exact same file in library csaldat, but retreiving data from a different library.
SQL view create statement . . . . . . . . . :
CREATE VIEW INQHIST AS SELECT H.DCORD#, H.DCODAT, H.DCBCUS, I.DDPART
,H.DCSCUS, H.DCPO, CAST(MAX(IFNULL(EXT.COST,0)) AS DECIMAL(11,2)) A
S STDCOST, CAST(MAX(IFNULL(DDUNPR,0)) AS DECIMAL(12,3)) AS DDUNPR, C
AST(SUM(DDQTOI) AS DECIMAL(12,3)) AS DDQTOI, CAST(SUM(DDQTSI) AS DEC
IMAL(12,3)) AS DDQTSI, CHAR(DCODAT) AS DCODT6, CHAR(DCODAT,USA) AS D
COD10, CHAR(DCODAT) AS DCOD08, CHAR(DCODAT,ISO) AS DCODST FROM SAVAI
NDDAT.OCRH H INNER JOIN SAVAINDDAT.OCRI I ON H.DCORD# = I.DDORD# INN
ER JOIN SAVAINDDAT.CUST C ON H.DCBCUS = C.BVCUST INNER JOIN SAVALIB.
ITEMMAST S ON I.DDPART = S.PART LEFT JOIN SAVAINDDAT.SAVORDEXT EXT O
N I.DDORD# = EXT.ORDNO AND I.DDITM# = EXT.LINE WHERE I.DDGLC NOT LIK
E 'SC%' GROUP BY H.DCORD#, H.DCODAT, H.DCBCUS, I.DDPART, H.DCSCUS, H
.DCPO
Number of data members . . . . . . . . . : 5
Based on file . . . . . . . . . . . . . . : OCRH
Library . . . . . . . . . . . . . . . . : SAVAINDDAT
Member . . . . . . . . . . . . . . . . : OCRH
Based on file . . . . . . . . . . . . . . : OCRI
Library . . . . . . . . . . . . . . . . : SAVAINDDAT
Member . . . . . . . . . . . . . . . . : OCRI
Based on file . . . . . . . . . . . . . . : CUST
Library . . . . . . . . . . . . . . . . : SAVAINDDAT
Member . . . . . . . . . . . . . . . . : CUST
Based on file . . . . . . . . . . . . . . : ITEMMAST
Library . . . . . . . . . . . . . . . . : SAVALIB
Member . . . . . . . . . . . . . . . . : ITEMMAST
Based on file . . . . . . . . . . . . . . : SAVORDEXT
Library . . . . . . . . . . . . . . . . : SAVAINDDAT
Member . . . . . . . . . . . . . . . . : SAVORDEXT
The new file INQHIST should reside in library csaldat. It should get data from the following…
Where previous files picked up data from SAVAINDDAT the new files should get it from CSALDAT.
Where previous files picked up data from SAVALIB the new files should get it from CSALLIB.
To see all answers submitted to the Answer Wiki: View Answer History.
Have you tried the CRTDUPOBJ (Create Duplicate Object) command?
Tom
I tried it at you advice. It partially worked. It automatically picked up library A to the new library, but library B is still the original library. In addition I have 5 files that make this logical. It converted 4 files to the new library, but the 5th file still reads the previous library.
Okay, you’ve explained a little more. Now we know it’s based on multiple physical files and that you don’t want just a copy of the logical file. You also need the new logical file to point to particular physical files.
Here is an example map of some data:
LibraryA LibraryB PF1 PF2 PF3 PF4 PF5 LFIt shows two libraries with some physical files and a logical file. In my example, the LF is built over PF1, PF2 and PF3 from LibraryA and also PF4 and PF5 from LibraryB.
In your question, you have LF and you want to create a copy in a new library.
Can you create a similar list for your question? Can you show how your LF is currently defined? And then can you show how you want the copy to be defined?
Tom