Assert validates columns data type, precision and scale. Assert will fail if data type or precision/scale do not match.

Arguments

  • v_ObjectName – table name, Variable Character(255) in Oracle and Netezza, SYSNAME in SQL Server
  • v_ColumnName – name of the column, Variable Character(255) in Oracle and Netezza, SYSNAME in SQL Server
  • v_ColumnType - Column Type, for instance NUMERIC.
  • v_ColumnPrecScale - Column precision and scale, for instance (18,5)
  • v_UserMessage – message to report when assertion fails, Variable Character(255) in Oracle and Netezza, NVARCHAR(MAX) in SQL Server

Note: Oracle uses DBTD_ASSERT_COL_TYPE_AND_PRCN assert name, which is available for Netezza and SQL Server after installing compatibility pack.

Examples

SQL Server

EXEC DBTD_ASSERT_COLUMN_TYPE_AND_PRECISION 'DBTD_TMP_DATATYPEs_A', 'T_decimal_P_S', 'decimal', '(16,4)','this column have issues with the type';    
EXEC DBTD_ASSERT_COLUMN_TYPE_AND_PRECISION 'DBTD_TMP_DATATYPEs_A', 'T_float_P25', 'float', '(25)','this column have issues with the type';
EXEC DBTD_ASSERT_COLUMN_TYPE_AND_PRECISION 'DBTD_TMP_DATATYPEs_A', 'T_varchar_P', 'varchar', '(54)','this column have issues with the type';
EXEC DBTD_ASSERT_COLUMN_TYPE_AND_PRECISION 'DBTD_TMP_DATATYPEs_A', 'T_varbinary_P', 'varbinary', '(4578)','this column have issues with the type';
EXEC DBTD_ASSERT_COLUMN_TYPE_AND_PRECISION 'DBTD_TMP_DATATYPEs_A', 'T_varbinary_PMax', 'varbinary', '(max)','this column have issues with the type';
EXEC DBTD_ASSERT_COLUMN_TYPE_AND_PRECISION 'DBTD_TMP_DATATYPEs_A', 'T_rowversion', 'rowversion', '','this column have issues with the type';
EXEC DBTD_ASSERT_COLUMN_TYPE_AND_PRECISION 'DBTD_TMP_DATATYPEs_A', 'T_hierarchyid', 'hierarchyid', '','this column have issues with the type';

or one can use following

EXEC DBTD_ASSERT_COL_TYPE_AND_PRCN 'DBTD_TMP_DATATYPEs_A', 'T_decimal_P_S', 'decimal', '(16,4)','this column have issues with the type';    
 

or procedure can look like this:

CREATE PROCEDURE UT_Infrastructure_CreditQuality_Columns
AS
BEGIN
    EXEC DBTD_UNIT_TEST 'Infrastructure';
    EXEC DBTD_ASSERT_COLUMN_TYPE_AND_PRECISION
        'CreditQuality', 'AAA', 'NUMERIC', '(5, 2)','Precision should be set to 5';

    EXEC DBTD_ASSERT_COLUMN_TYPE_AND_PRECISION
        'CreditQuality', 'AA', 'NUMERIC', '(5, 2)','Precision should be set to 5';
END;

 

Oracle

DBTD_ASSERT_COL_TYPE_AND_PRCN('DBTD_TMP_DATATYPEs_A', 'T_varchar2_P', 'varchar2', '(54)','this column have issues with the type');

Netezza

CALL DBTD_ASSERT_COLUMN_TYPE_AND_PRECISION('DBTD_TMP_DATATYPEs', 'T_dec_P', 'dec','(20)', 'issues with the dec(20)');
CALL DBTD_ASSERT_COLUMN_TYPE_AND_PRECISION('DBTD_TMP_DATATYPEs', 'T_decimal_P_S', 'decimal','(18,2)', 'issues with the DECIMAL(18,2)');
CALL DBTD_ASSERT_COLUMN_TYPE_AND_PRECISION('DBTD_TMP_DATATYPEs', 'T_nvarchar_n', 'nvarchar','(65)', 'issues with the nvarchar(65)');
CALL
DBTD_ASSERT_COLUMN_TYPE_AND_PRECISION('DBTD_TMP_DATATYPEs', 'T_timespan', 'timespan','', 'issues with the timespan');
CALL DBTD_ASSERT_COLUMN_TYPE_AND_PRECISION('DBTD_TMP_DATATYPEs', 'T_time_without_time_zone', 'TIME WITHOUT TIME ZONE','', 'issues with the TIME WITHOUT TIME ZONE');

or one can use following

CALL DBTD_ASSERT_COL_TYPE_AND_PRCN('DBTD_TMP_DATATYPEs', 'T_dec_P', 'dec','(20)', 'issues with the dec(20)');
 

See Also