Assert verifies that given value does not exist in provided column. Will fails if value has been found.

Arguments

  • v_TableName – name of the table, Variable Character(255) in Oracle and Netezza, SYSNAME in SQL Server
  • v_ColumnName - column that will be checked for a value, Variable Character(255) in Oracle and Netezza, SYSNAME in SQL Server
  • v_Value – value to be found, Variable Character(4000)
  • v_ValueDataType – type of the specified value, Variable Character(255)
  • 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_HAS_NO_VALUE name, which is also available for Netezza and SQL Server after installing compatibility pack 

 

Examples

SQL Server 

      EXEC DBTD_ASSERT_COLUMN_HAS_NO_VALUE
                'ArchivedOrders', 'OrderID',
                '0001B', 'VARCHAR(50)', 'specified order should not be archived';

      or

      EXEC DBTD_ASSERT_COL_HAS_NO_VALUE      
                'ArchivedOrders', 'OrderID',
                '0001B', 'VARCHAR(50)', 'specified order should not be archived';

Oracle 

      DBTD_ASSERT_COL_HAS_NO_VALUE (‘ComponentParts’, ‘PartNumber’, ‘ZB0023gdt-35’, ‘VARCHAR(50)’, ‘this part should not be used in provided component’);

Netezza

      CALL DBTD_ASSERT_COLUMN_HAS_NO_VALUE (‘Car’, ‘NumberOfAxels’, ‘35’, ‘INT’, ‘there should be no records with 35 axels’);

      or     

      CALL DBTD_ASSERT_COL_HAS_NO_VALUE (‘Car’, ‘NumberOfAxels’, ‘35’, ‘INT’, ‘there should be no records with 35 axels’);

 

See Also