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