Posted On: June 27, 2019
Finding Differences Between UDCs (SQL Commands)
When comparing an individual or small number of UDC values between environments it will be easiest to use the Work With User Defined Codes application (P0004A).
However, in order to ensure that your UDCs are in sync between environments, it may be best to use SQL. The following tables make up UDCs:
- F0004 – User Defined Code Types
- F00041 – User Defined Code Types Language Status
- F0004D – User Defined Codes – Alternate Language Descriptions
- F0005 – User Defined Code Values
- F00051 – User Defined Code Language Status
- F0005D – User Defined Code – Alternate Language Descriptions
Below are a few queries that can be used to determine that status of your UDCs.
Note: Your system, environments, database platform or configuration may require changes to the below SQL scripts in order to run effectively. Please consult your DBA before running any of them.
Determine if the record counts are equal between environments. Run this on each table listed above:
SELECT 'DV920' AS Env, COUNT(*) AS Count FROM TESTCTL.F0004 UNION SELECT 'PY920' AS Env, COUNT(*) AS Count FROM CRPCTL.F0004 UNION SELECT 'PD920' AS Env, COUNT(*) AS Count FROM PRODCTL.F0004
Find the UDC Types that are in PY but NOT in PD:
SELECT DTSY,DTRT FROM CRPCTL.F0004 EXCEPT SELECT DTSY,DTRT FROM PRODCTL.F0004
Find UDC Type descriptions that are NOT equal between PD & PY:
SELECT a.DTSY,a.DTRT,a.DTDL01, b.DTDL01 FROM CRPCTL.F0004 a INNER JOIN PRODCTL.F0004 b ON (a.DTSY = b.DTSY AND a.DTRT = b.DTRT) WHERE a.DTDL01 <> b.DTDL01
Find UDC values that are in PY but NOT in PD:
SELECT DRSY,DRRT,DRKY FROM CRPCTL.F0005 EXCEPT SELECT DRSY,DRRT,DRKY FROM PRODCTL.F0005
Find UDC value descriptions that are NOT equal between PD & PY:
SELECT a.DRSY,a.DRRT,a.DRKY,a.DRDL01, b.DRDL01 FROM PRODCTL.F0005 a INNER JOIN CRPCTL.F0005 b ON (a.DRSY = b.DRSY AND a.DRRT = b.DRRT AND a.DRKY = b.DRKY) WHERE a.DRDL01 <> b.DRDL01