Who's On?

	Membership:
	Latest: Chris Radcliffe
	Past 24 Hours: 0
	Prev. 24 Hours: 0
	Overall: 5187

	People Online:
	Visitors: 381
	Members: 0
	Total: 381

Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3288
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1348
Roger French	1311
mark.cook	1244

Lawson Data Column Xref

Sort:

You are not authorized to post a reply.

Author

Messages

Joe O'Toole

Veteran Member

Posts: 314

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

2/23/2012 7:32 PM

Are you Oracle, SQL, or DB2?

John Henley

Senior Member

Posts: 3348

2/23/2012 7:32 PM

SELECT DISTINCT FILENAME FROM GEN.FILEFLD WHERE PRODUCTLINE = 'PROD' AND (FIELDNAME = 'DEPARTMENT' OR ELEMENTNAME = 'DEPARTMENT')

Chris Martin

Veteran Member

Posts: 277

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

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

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.

You are not authorized to post a reply.