Derived field text in logical file

285 pts.
Tags:
AS/400
AS/400 programming
Logical Files
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'. 
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

Answer Wiki

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

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.

Discuss This Question: 3  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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    Have you tried the CRTDUPOBJ (Create Duplicate Object) command? Tom
    125,585 pointsBadges:
    report
  • DLM2007
    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.
    285 pointsBadges:
    report
  • TomLiotta
    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
                LF
    It 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
    125,585 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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following