This handy SQL script will provide how to find Record Field properties such as Key Fields, Edit Fields, Default values etc.. of a Record. Presented here are two versions of SQLs. First one give is quite simple and give you enough information about record field properties of a record and it uses PSRECFIELD tools table. Second one is bit complex and will give you more in-depth information such as last update datetime and operator id, etc..
Side Note: The SQLs are using Oracle Function
BITAND. Also if you want to know more info about USEEDIT field please
click here.
USEEDIT field
assigns a decimal value to each individual bit value.
SQL 1 (simple)
SELECT
FIELDNAME,
DECODE(BITAND(USEEDIT, 1), 1 ,'Y', DECODE(BITAND(USEEDIT, 16), 16, 'Alt')) as Key,
DECODE(BITAND(USEEDIT, 2048),2048 , 'Y' , '') as Search,
DECODE(BITAND(USEEDIT, 32), 32 ,'Y','') AS List,
DECODE(BITAND(USEEDIT, 256), 256 ,'Y','')AS Req,
DECODE(BITAND(USEEDIT, 512), 512 ,'Xlat', DECODE(BITAND(USEEDIT, 8192), 8192 ,'Y/N', DECODE(BITAND(USEEDIT, 16384),16384 ,'Prompt',''))) AS Edit,
EDITTABLE, DEFFIELDNAME
FROM PSRECFIELDDB
WHERE
RECNAME = 'ACAD_PROG' --Record Name
ORDER BY FIELDNUM;
SQL 2 (complex)
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 --paste recname here
AND A.fieldname = B.fieldname
ORDER BY fieldnum;
