Rdb Datatypes Decoded

-- Copyright 2002 - Oracle Corporation
-- Example CASE expression to translate Rdb system table
-- type information to SQL text
--

select
  RDB$FIELD_NAME as Column_Name,    !as "Column Name"
  case RDB$FIELD_TYPE
    when 6  then          -- Byte Integer
      'TINYINT' ||
      case RDB$FIELD_SCALE
        when 0 then ''
        else
          ' (' || CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
      end
    when 7  then          -- Word Integer
      'SMALLINT' ||
      case RDB$FIELD_SCALE
        when 0 then ''
        else
          ' (' || CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
      end
    when 8  then          -- Longword Integer
      'INTEGER' ||
      case RDB$FIELD_SCALE
        when 0 then ''
        else
          ' (' || CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
      end
    when 9  then          -- Quadword Integer
      'BIGINT' ||
      case RDB$FIELD_SCALE
        when 0 then ''
        else
          ' (' || CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
      end
    when 10 then 'REAL'       -- F_floating
    when 27 then 'DOUBLE PRECISION' -- G_floating
    when 14 then          -- Character-coded text
      'CHAR (' ||
      CAST(RDB$FIELD_LENGTH AS VARCHAR(5)) || ')'
    when 37 then          -- Varying Character-coded Text
      'VARCHAR (' ||
      CAST(RDB$FIELD_LENGTH AS VARCHAR(5)) || ')'
    when 21 then 'DECIMAL'      -- Packed decimal string
    when 261 then
      'LIST OF BYTE VARYING (' ||
      CAST(RDB$SEGMENT_LENGTH AS VARCHAR(5)) || ')'
    when 35 then
      -- Absolute Date and Time
      case RDB$FIELD_SUB_TYPE
        when 0  then
          'DATE VMS'
        when 56 then
          'TIME (' ||
          CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
        when 7  then
          'DATE ANSI'
        when 63 then
          'TIMESTAMP (' ||
          CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
        else 'INTERVAL ' ||
          case RDB$FIELD_SUB_TYPE
            when 513 then
              'YEAR(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) || ')'
            when 515 then
              'YEAR(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) ||
              ') TO MONTH'
            when 514 then
              'MONTH(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) || ')'
            when 516 then
              'DAY(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) || ')'
            when 524 then
              'DAY(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) ||
              ') TO HOUR'
            when 540 then
              'DAY(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) ||
              ') TO MINUTE'
            when 572 then
              'DAY(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) ||
              ') TO SECOND(' ||
              CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
            when 520 then
              'HOUR(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) || ')'
            when 536 then
              'HOUR(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) ||
              ') TO MINUTE'
            when 568 then
              'HOUR(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) ||
              ') TO SECOND(' ||
              CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
            when 528 then
              'MINUTE(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) || ')'
            when 560 then
              'MINUTE(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) ||
              ') TO SECOND(' ||
              CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
            when 544 then
              'SECOND(' ||
              CAST(RDB$SEGMENT_LENGTH AS VARCHAR(9)) ||
              ',' ||
              CAST(-RDB$FIELD_SCALE AS VARCHAR(3)) || ')'
            else
              '(unknown interval)'
          end
      end
    else
      '(unknown data type)'
  end as Data_Type          ! as "Data Type"
from RDB$FIELDS
where RDB$FIELD_NAME not starting with 'RDB';