Prev
Next
Forums Infor / Lawson Platforms S3 Systems Administration

Lawson Data Column Xref

 5 Replies
 0 Subscribed to this topic
 27 Subscribed to this forum
Sort:
Author
Messages
Joe O'Toole
Veteran Member
Posts: 314
Veteran Member
2/23/2012 7:07 PM
    We are conducting a change impact analysis and need to find every table in Lawson that contains "DEPARTMENT". I have the technical documents in PDF but does anyone know where or how can this be determined within Lawson or via SQL? Thanks.
    Chris Martin
    Veteran Member
    Posts: 277
    Veteran Member
    2/23/2012 7:32 PM
      Are you Oracle, SQL, or DB2?
      John Henley
      Posts: 3353
      2/23/2012 7:32 PM
        SELECT DISTINCT FILENAME FROM GEN.FILEFLD WHERE PRODUCTLINE = 'PROD' AND (FIELDNAME = 'DEPARTMENT' OR ELEMENTNAME = 'DEPARTMENT')
        Thanks for using the LawsonGuru.com forums!
        John
        Chris Martin
        Veteran Member
        Posts: 277
        Veteran Member
        2/23/2012 7:41 PM
          I don't have the SQL or DB2 query handy, but you can do this in Oracle with something along these lines:

          SELECT DISTINCT TABLE_NAME, COLUMN_NAME
          FROM ALL_TAB_COLUMNS
          WHERE OWNER = 'PROD'
          AND (COLUMN_NAME = 'DEPARTMENT' OR
                   COLUMN_NAME LIKE '%DEPT%' OR
                   COLUMN_NAME LIKE '%DPT%')

          You will need to replace 'PROD'  with the schema name where your Lawson tables reside.
          Joe O'Toole
          Veteran Member
          Posts: 314
          Veteran Member
          2/23/2012 9:22 PM
            We are on MS SQL - not sure what the equivalent of all_tab_columns is in SQL but John's query worked - just had to change column name from "fieldname" to "fldname".

            Thanks guys!
            Joe O'Toole
            Veteran Member
            Posts: 314
            Veteran Member
            2/27/2012 9:19 PM
              Here is another way if anyone is interested - this came from Lawson:

               o into dbdef, select the productline, Click F6 and choose A. Files, Enter a table into the File Name field, like EMPLOYEE, which contains the DEPARTMENT field.
              Click F5 and choose A. Field Usage Report.