SELECT
  c.bcname AS component_interface,
  a.recname AS record,
  a.fieldname AS FIELD,
  d.shortname AS description,
  CASE
WHEN b.fieldtype = 0 THEN
  'CHARACTER'
WHEN b.fieldtype = 1 THEN
  'LONG_CHARACTER'
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)
WHEN b.fieldtype = 1 THEN
  '100'
ELSE
  to_char(b.LENGTH)
END AS
fldlen,
  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,   256) > 0 THEN
  'YES'
ELSE
  ' '
END AS
req,
  CASE
WHEN TRIM(a.defrecname) = '' THEN
  a.deffieldname
ELSE
  TRIM(a.defrecname) || '.' || a.deffieldname
END AS
default_value,
  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,
  c.commentshort
FROM psrecfielddb a,
  psdbfield b,
  psbcitem c,
  psdbfldlabl d
WHERE a.recname = c.recname
 AND a.fieldname = c.fieldname
 AND a.fieldname = b.fieldname
 AND c.bctype IN('1',   '4')
 AND c.bcname LIKE(': COMPONENT INTERFACE NAME')
 AND a.fieldname = d.fieldname
 AND d.default_label = '1'
ORDER BY c.bcname,
  c.sequence_nbr_6 ASC;