Query/400 not displaying column descriptions for DDL defined table

Tags:
AS/400
DDL
We recently updated a file and converted it from DDS to DDL during the process. During testing no one mentioned the column descriptions were no longer displayed when using Query/400. Is there a way to make Query/400 display column descriptions for DDL defined tables?


Software/Hardware used:
IBM i power 7 machine, v7r1 TR 8
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 16  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
    Were column descriptions included in the DDL? Can you show an example of the DDL that included the descriptions? Actuall, what "description" was missing? I'd guess that some 'attribute' of a column description was missing, but which one? (Technically, you can't create a table if it doesn't include column descriptions. But a column description has many possible attributes.)
    35,610 pointsBadges:
    report
  • DancingInTheMoonlight
    This is the ddl for the table.
    I am hoping to find a way to display the column labels in query/400.
    The query/400 screen did not paste as I expected in
    my previous reply.
     
    Create Table IFDTAAGM/IFDTL
    (Activity_Code for column IDACT char(1) not null,
    Company_Number for column IDCOM numeric(2) not null,
    Division_Number for column IDDIV numeric(3) not null,
    Warehouse_Code for column IDWHS char(3) not null,
    Item_Code for column IDITM char(12) not null,
    Prod_Receipt_Century for column IDPRDC numeric(2) not null,
    Production_Receipt_Date for column IDPRD numeric(6) not null,
    Mfg_Sequence_Code for column IDMVN char(3) not null,
    Location_Code for column IDLOC char(10) not null,
    Pallet_Code for column IDPAL char(10) not null,
    Units_Code for column IDUCD char(5) not null,
    Lot_Code for column IDLOT char(10) not null,
    Effective_Century for column IDEPRC numeric(2) not null,
    Effective_Date for column IDEPR numeric(6) not null,
    Product_Status for column IDSTS char(2) not null,
    Quantity_On_Hand for column IDQOH decimal(11,2) not null,
    Quantity_Allocated for column IDQAL decimal(11,2) not null,
    Total_Units for column IDUNT numeric(5) not null,
    Total_Units_Allocated for column IDUAL numeric(5) not null,
    Testing_Sequence_Number for column IDTSQ numeric(9) not null,
    Assigned_Bill_To for column IDBTC char(5) not null,
    Assigned_Ship_To for column IDSTC char(5) not null,
    Grade for column IDGDE char(1) not null,
    Date_Graded_Century for column IDDTGC numeric(2) not null,
    Date_Graded for column IDDTG numeric(6) not null,
    Original_Company_Number for column IDOCM numeric(2) not null,
    Original_Division for column IDODV numeric(3) not null,
    Original_Warehouse_Code for column IDOWH char(3) not null,
    Original_Item_Code for column IDOIT char(12) not null,
    Original_Prod_Rcpt_Century for column IDOPRC numeric(2) not null,
    Original_Prod_Rcpt_Date for column IDOPR numeric(6) not null,
    Original_Mfg_Sequence_Code for column IDOMS char(3) not null,
    Original_Location_Code for column IDOLC char(10) not null,
    Original_Pallet_Code for column IDOPL char(10) not null,
    Original_Units_Code for column IDOUC char(5) not null,
    Original_Lot_Code for column IDOLT char(10) not null,
    Original_Effective_Century for column IDOEFC numeric(2) not null,
    Original_Effective_Date for column IDOEF numeric(6) not null,
    Manufacturing_Warehouse for column IDMFW char(3) not null,
    Order_Number for column IDORN numeric(9) not null,
    Cost_Data_Base for column IDCDB char(2) not null,
    WIP_Level for column IDWIP numeric(1) not null,
    Remanufacture_Cost_Sequence for column IDMCS numeric(2) not null,
    Warehouse_Trans_Eff_Cen_Rsvd for column IDWTC numeric(2) not null,
    Warehouse_Trans_Eff_Date_Rsvd for column IDWTD numeric(6) not null,
    Expiration_Century for column IDEXC numeric(2) not null,
    Expiration_Date for column IDEXP numeric(6) not null,
    Last_Program_To_Update for column IDPGM char(7) not null,
    Last_User_To_Update for column IDUSR char(8) not null,
    Last_Time_Updated for column IDTME numeric(6) not null,
    Last_Century_Updated for column IDUPDC numeric(2) not null,
    Last_Date_Updated for column IDUPD numeric(6) not null,
    Date_of_Expiration for column IDEXD date not null with default,
    Expiration_Update_User for column IDEUSR char(18) not null with
    default USER,
    Expiration_Update_Date for column IDEDAT date not null with
    default,
    Minimum_Delivery_Date for column IDMDAT date not null with
    default,
    Reference_Lot for column IDRLOT char(10) not null,
    primary key(Idcom, Iddiv, Idwhs, Iditm, Idprdc, Idprd, Idmvn,
    Idloc, Idpal, Iducd, Idlot))
    rcdfmt ifdtlpf;
    label on table IFDTL is 'Inventory Detail Table "D"';
    label on column IFDTL
    (Idact is 'Activity Code',
    Idcom is 'Company Number',
    Iddiv is 'Division Number',
    Idwhs is 'Warehouse Code',
    Iditm is 'Item Code',
    Idprdc is 'Production Receipt Century CC',
    Idprd is 'Production Receipt Date YYMMDD',
    Idmvn is 'Manufacturing Sequence Code',
    Idloc is 'Location Code',
    Idpal is 'Pallet Code',
    Iducd is 'Units Code',
    Idlot is 'Lot Code',
    Ideprc is 'Effective Century CC',
    Idepr is 'Effective Date YYMMDD',
    Idsts is 'Product Status',
    Idqoh is 'Quantity On Hand',
    Idqal is 'Quantity Allocated',
    Idunt is 'Total Units',
    Idual is 'Total Units Allocated',
    Idtsq is 'Testing Sequence Number',
    Idbtc is 'Assigned Bill-to',
    Idstc is 'Assigned Ship-to',
    Idgde is 'Grade',
    Iddtgc is 'Date Graded Century CC',
    Iddtg is 'Date Graded YYMMDD',
    Idocm is 'Original Company Number',
    Idodv is 'Original Division Number',
    Idowh is 'Original Warehouse Code',
    Idoit is 'Original Item Code',
    Idoprc is 'Original Production Receipt Century CC',
    Idopr is 'Original Production Receipt Date YYMMDD',
    Idoms is 'Original Manufacturing Sequence Code',
    Idolc is 'Original Location Code',
    Idopl is 'Original Pallet Code',
    Idouc is 'Original Units Code',
    Idolt is 'Original Lot Code',
    Idoefc is 'Original Effective Century CC',
    Idoef is 'Original Effective Date YYMMDD',
    Idmfw is 'Manufacturing Warehouse',
    Idorn is 'Order Number',
    Idcdb is 'Cost Database',
    Idwip is 'Work In Process Level',
    Idmcs is 'Remanufacture Cost Sequence',
    Idwtc is 'Warehouse Transfer Effective Century - Reserved',
    Idwtd is 'Warehouse Transfer Effective Date - Reserved',
    Idexc is 'Expiration Century CC',
    Idexp is 'Expiration Date YYMMDD',
    Idpgm is 'Last program to update data',
    Idusr is 'Last User to update data',
    IdTME is 'Last time data updated',
    Idupdc is 'Century when data last updated CC',
    Idupd is 'Date when data last updated YYMMDD',
    Idexd is 'Expiration Date CCYYMMDD',
    Ideusr is 'Expiration Date updated by user',
    Idedat is 'Date Expiration Date updated',
    Idmdat is 'Minimum Delivery Date CCYYMMDD',
    Idrlot is 'Reference Lot Code');
    145 pointsBadges:
    report
  • RossHowatson

    In addition to the LABEL ON statement for table (file) and Column (field) level description you need to use the COMMENT ON statement.  Best part is you can add/modify the comments without changing the structure of the table (file) so no level checks will occur.

    630 pointsBadges:
    report
  • DancingInTheMoonlight

    Thank you both for the assistance. I will add the comment on section to the table.

    145 pointsBadges:
    report
  • RossHowatson
    For the Comment on phrase at the column (field) level make sure that for long text (i.e. 'GL Account Number') that you put in the field as 'GL with 18 spaces then Account with 13 spaces and then Number'.  This way in Query/400 you will have three lines showing for the text description and not one long field (assuming that the column (field) has a short length.
    630 pointsBadges:
    report
  • DancingInTheMoonlight

    Ross,

    I must be doing something wrong with the statement.  I keep getting keyword comment not expected.  Statement below.

    alter table ifdtaagm/ifdtl                 
    Comment on ifdtl                           
    (Idact is 'Activity Code',                 
    Idcom is 'Company Number',                 
    Iddiv is 'Division Number',                
    Idwhs is 'Warehouse Code',                 
    Iditm is 'Item Code',                      
    Idprdc is 'Production Receipt Century CC', 
    Idprd is 'Production Receipt Date YYMMDD', 
    Idmvn is 'Manufacturing Sequence Code',    
    Idloc is 'Location Code',                  
    Idpal is 'Pallet Code',                    
    Iducd is 'Units Code',                     
    Idlot is 'Lot Code',                       
    ideprc is 'Effective Century CC',          
    Idepr is 'Effective Date YYMMDD')
    145 pointsBadges:
    report
  • DancingInTheMoonlight

    Ross,

    I have tried the statement with comment on column and just column on with the same results.

    145 pointsBadges:
    report
  • RossHowatson

    Drop the alter table ifdtaagm/ifdtl (you are not changing the file layout but adding to the description of the table - you did not use ALTER for the labels) and end the comment statement with a ; and it will work.

    Change the comments to be like        'Activity            Code' and
            'Production          Receipt             Date YYMMDD' - note the spaces between the words marking the characters at position 1, 21 and 41 and not exceeding 60 characters in length.


    630 pointsBadges:
    report
  • DancingInTheMoonlight
    I was just planning to alter the table.  I did not want to have to drop and recreate it.  Can I do this with alter table?
    145 pointsBadges:
    report
  • RossHowatson

    You do not need to drop the table.  Just run the COMMENT ON statements.  When you did the LABEL ON statement you did not have to do the ALTER TABLE as part of the command.  The COMMENT ON and LABEL ON are separate SQL statements.

    So remove the ALTER TABLE command and just issue the COMMENT ON statement.  Remember to also do the COMMENT ON the TABLE(file) level.


    630 pointsBadges:
    report
  • DancingInTheMoonlight

    Ross,

    Thank you. I missed what you were saying.

    I added the labels during the create. I thought you were saying to recreate the table and include the comment on statement during the create. I did as you stated and the comment on worked perfectly. Thank you for all your help.

    145 pointsBadges:
    report
  • DancingInTheMoonlight

    Unfortunately, this did not correct the problem.

    The text is still not displaying in query/400.

    Seq   Field             Text                                          Len  Dec
          IDACT                                                             1     
          IDCOM                                                             2    0
          IDDIV                                                             3    0
          IDWHS                                                             3     
          IDITM                                                            12     
          IDPRDC                                                            2    0
          IDPRD                                                             6    0
          IDMVN                                                             3     
          IDLOC                                                            10     
          IDPAL                                                            10     
          IDUCD                                                             5     
          IDLOT                                                            10     

    145 pointsBadges:
    report
  • RossHowatson

    Now if you really want to get fancy you should look at the LABEL ON statement.  There are two ways this puts text on the fields.

    LABEL ON COLUMN ifdtaagm/ifdtl (
      Idact is 'Activity           Code',                
      Idcom is 'Company            Number');
    LABEL ON COLUMN ifdtaagm/ifdtl (
      Idact TEXT IS 'Activity           Code',                
      Idcom TEXT IS 'Company            Number');


    630 pointsBadges:
    report
  • DancingInTheMoonlight

    "Label on column text is" fixed the query/400 problem.

    Thank you. 

    145 pointsBadges:
    report
  • RossHowatson

    Best to use both LABEL ON and COMMENT ON so that the table is fully documented.  When you do a DSPFFD to an output file and query the data it will not show all of the data and you will have more difficulty with generating documentation on the fly.  Remember that SQL has no concept of tables (files) having members so you must use the CHGPFM command to add a text description to the member.


    630 pointsBadges:
    report
  • DancingInTheMoonlight

    Ross,

    Thanks, normally this is not a problem as the tools we use recognize the SQL table structure.  This is the first time I had someone use query/400 to create a report after I created a table.  I will add text and comments when creating tables in addition to the label on column.

    145 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: