CREATE OR REPLACE VIEW mig_td_ext.vw_td_dbc_columnsV 
/*  This view is used to support some specific columns like SchemaName, TableName,  
    ColumnName, ColumnType, ColumnLength, DecimalTotalDigits, DecimalFractionalDigits, 
	Nullable and UpperCaseFlag in the TD system view "dbc.columnsV"
*/
AS 
SELECT UPPER(isc.table_schema) as DatabaseName
	 , UPPER(isc.table_name) as TableName
	 , UPPER(isc.column_name) as ColumnName
     , isc.ordinal_position as ColumnId
	 , CASE WHEN isc.data_type = 'integer' THEN 'I'
			WHEN isc.data_type = 'smallint' THEN 'I2'
			WHEN isc.data_type = 'bigint' THEN 'I8'
			WHEN isc.data_type = 'numeric' THEN 'D' 
			WHEN isc.data_type = 'character varying' THEN 'CV'
			WHEN isc.data_type = 'character' THEN 'CF'
			WHEN isc.data_type = 'time without time zone' THEN 'AT'
			WHEN isc.data_type = 'time with time zone' THEN 'TZ'
			WHEN isc.data_type = 'timestamp without time zone' THEN 'DA'
			WHEN isc.data_type = 'timestamp with time zone' THEN 'SZ'
			ELSE 'CF' 
	   END AS ColumnType
	 , DECODE(isc.is_nullable, 'YES', 'Y', 'NO', 'N') AS Nullable
	 , isc.character_maximum_length AS ColumnLength
	 , CAST(NULL AS CHAR(1)) AS UpperCaseFlag
	 , isc.numeric_precision AS DecimalTotalDigits
	 , CASE WHEN isc.data_type IN ( 'time without time zone'
								  , 'time with time zone'
								  , 'timestamp without time zone'
								  , 'timestamp with time zone' ) 
							THEN isc.datetime_precision
		    ELSE isc.numeric_scale
	   END AS DecimalFractionalDigits
  FROM information_schema.columns isc 
 ORDER BY isc.table_schema, isc.table_name, isc.ordinal_position;
