Understanding the USEEDIT field in PSRECFIELD

USEEDIT is a binary number stored as a Decimal. I forget the term right now but each bit represents a field attribute such as List field, Required etc.

edit: new version


--1 1 Key
--2 2 Duplicate Order Key
--3 4 System Maintained
--4 8 Audit Field Add
--5 16 Alternate Search Key
--6 32 List Box Item
--7 64 Descending Key
--8 128 Audit Field Change
--9 256 Required
--10 512 Translate Table Edit
--11 102 Audit Field Delete
--12 2048 Search Key
--13 4096 Reasonable Date Edit
--14 8192 Yes/No Edit
--15 16384 Prompt Table Edit
--16 32768 Auto-Update
--17 65536 Unknown
--18 131072 Unknown
--19 262144 From Search Field
--20 524288 Through Search Field
--21 1048576 1/0 Table Edit
--22 2097152 Disable Advanced Search Options
--23 4194304 Unknown
--24 8388608 Unknown
--25 16777216 Default Search Field
--26 33554432 Unknown
--27 67108864 Unknown
--28 134217728 Unknown
--29 268435456 Search Edit
--30 536870912 Unknown
--31 1073741824 Unknown

SELECT A.FIELDNAME
FROM PSRECFIELDDB A
-- Oracle: WHERE bitand(A.USEEDIT, 512) > 0
-- SQL Server: WHERE A.USEEDIT & 512 > 0
-- DB2: WHERE (USEEDIT/512)/2 <> (USEEDIT/512)/2.0.
WHERE (USEEDIT/512)/2 <> (USEEDIT/512)/2.0.
AND A.FIELDNAME not in (SELECT FIELDNAME FROM PSXLATITEM)
GROUP BY A.FIELDNAME
ORDER BY A.FIELDNAME

edit: here is a nicer code snippet. This finds all fields marked as a XLAT field, that actually aren't : \ 512 is the key.
SELECT A.FIELDNAME
FROM PSRECFIELDDB A
WHERE bitand(A.USEEDIT, 512) > 0
AND A.FIELDNAME not in (SELECT FIELDNAME FROM PSXLATITEM)
GROUP BY A.FIELDNAME
ORDER BY A.FIELDNAME


Also handy


SELECT A.RECNAME, A.FIELDNAME
FROM PSRECFIELDDB A
WHERE bitand(A.USEEDIT, 512) > 0
AND A.FIELDNAME not in (SELECT FIELDNAME FROM PSXLATITEM)
ORDER BY A.RECNAME;

___


Following is a snippet of code I have blatantly copied from a fantastic Blog I found while researching the problem. http://xtrahot.chili-mango.net/

SELECT
A.RECNAME,
A.FIELDNAME,
CASE
WHEN B.FIELDTYPE = 0 THEN
'CHAR'
WHEN B.FIELDTYPE = 1 THEN
'LONG CHAR'
WHEN B.FIELDTYPE = 2 THEN
'NUMBER'
WHEN B.FIELDTYPE = 3 THEN
'SIGNED NBR'
WHEN B.FIELDTYPE = 4 THEN
'DATE'
WHEN B.FIELDTYPE = 5 THEN
'TIME'
WHEN B.FIELDTYPE = 6 THEN
'DATETIME'
WHEN B.FIELDTYPE = 7
OR B.FIELDTYPE = 8 THEN
'IMAGE'
ELSE NULL
END AS FIELDTYPE,
CASE
WHEN B.FIELDTYPE = 2
OR B.FIELDTYPE = 3 THEN
TRIM(TO_CHAR(B.LENGTH)) || '.' || TO_CHAR(B.DECIMALPOS)
ELSE TO_CHAR(B.LENGTH)
END AS FLDLEN,
CASE
WHEN bitand(A.USEEDIT, 256) > 0 THEN
'YES'
ELSE 'NO'
END AS REQ,
CASE
WHEN bitand(A.USEEDIT, 1) > 0 THEN
'KEY'
WHEN bitand(A.USEEDIT, 2) > 0 THEN
'DUP'
WHEN bitand(A.USEEDIT, 16) > 0 THEN
'ALT'
ELSE NULL
END AS KEY_TYPE,
CASE
WHEN bitand(A.USEEDIT, 64) > 0 THEN
'DESC'
WHEN ( bitand(A.USEEDIT, 1) > 0
OR bitand(A.USEEDIT, 2) > 0
OR bitand(A.USEEDIT, 16) > 0 )
AND bitand(A.USEEDIT, 64) = 0 THEN
'ASC'
ELSE NULL
END AS DIR,
CASE
WHEN bitand(A.USEEDIT, 2048) > 0 THEN
'YES'
ELSE 'NO'
END AS SRCH,
CASE
WHEN bitand(A.USEEDIT, 32) > 0 THEN
'YES'
ELSE 'NO'
END AS LIST,
CASE
WHEN bitand(A.USEEDIT, 4) > 0 THEN
'YES'
ELSE 'NO'
END AS SYS,
CASE
WHEN TRIM(A.DEFRECNAME) = '' THEN
A.DEFFIELDNAME
ELSE
TRIM(A.DEFRECNAME) || '.' || A.DEFFIELDNAME
END AS DEFAULT_VALUE,
CASE
WHEN bitand(A.USEEDIT, 8) > 0
AND bitand(A.USEEDIT, 128) = 0
AND bitand(A.USEEDIT, 1024) = 0 THEN
'A'
WHEN bitand(A.USEEDIT, 8) > 0
AND bitand(A.USEEDIT, 128) > 0
AND bitand(A.USEEDIT, 1024) = 0 THEN
'AC'
WHEN bitand(A.USEEDIT, 8) > 0
AND bitand(A.USEEDIT, 128) > 0
AND bitand(A.USEEDIT, 1024) > 0 THEN
'ACD'
WHEN bitand(A.USEEDIT, 8) = 0
AND bitand(A.USEEDIT, 128) > 0
AND bitand(A.USEEDIT, 1024) = 0 THEN
'C'
WHEN bitand(A.USEEDIT, 8) = 0
AND bitand(A.USEEDIT, 128) > 0
AND bitand(A.USEEDIT, 1024) > 0 THEN
'CD'
WHEN bitand(A.USEEDIT, 8) = 0
AND bitand(A.USEEDIT, 128) = 0
AND bitand(A.USEEDIT, 1024) > 0 THEN
'D'
ELSE NULL
END AS AUDT,
CASE
WHEN bitand(A.USEEDIT, 16384) > 0 THEN
'PROMPT'
WHEN bitand(A.USEEDIT, 512) > 0 THEN
'XLAT'
WHEN bitand(A.USEEDIT, 8192) > 0 THEN
'Y/N'
ELSE NULL
END AS EDIT,
A.EDITTABLE AS PROMPT_TABLE,
A.SETCNTRLFLD AS SET_CONTROL_FLD,
CASE
WHEN bitand(A.USEEDIT, 4096) > 0 THEN
'YES'
ELSE 'NO'
END AS REASONABLE_DT,
CASE
WHEN bitand(A.USEEDIT, 32768) > 0 THEN
'YES'
ELSE 'NO'
END AS AUTO_UPDT,
CASE
WHEN bitand(A.USEEDIT, 262144) > 0 THEN
'FROM'
WHEN bitand(A.USEEDIT, 524288) > 0 THEN
'THROUGH'
ELSE NULL
END AS SEARCH_FIELD,
CASE
WHEN A.SUBRECORD = 'Y' THEN
'YES'
ELSE 'NO'
END AS SUBRECORD,
A.LASTUPDDTTM,
A.LASTUPDOPRID
FROM PSRECFIELD A,
PSDBFIELD B
WHERE A.RECNAME = :1
AND A.FIELDNAME = B.FIELDNAME
ORDER BY FIELDNUM

0 comments: