-- 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';
Categories