By Bill Ramos and Ankit Matta, Advaiya Inc.
This blog post covers the reason why SQL Server Migration Assistant (SSMA) for Oracle is not able to convert a BINARY_FLOAT column with a constraint for checking a NAN (Not a Number) condition. In Oracle, the BINARY_FLOAT data type allows an application to store number underflow or overflow values. SQL Server generates an error as soon as a number overflow or underflow is computed and thus can’t store the value in a FLOAT data type.
When you try to convert the schema of a table having a constraint condition which is checking for NAN values, SSMA generates an error “Error O2SS0007 Check constraint condition not parsed” because SQL Server doesn’t support floating point conditions like NAN.
Consider the below example where we have created a table and altered it by adding a constraint of NAN type:
CREATE TABLE NANTEST
ALTER TABLE NANTEST ADD CONSTRAINT NANCHECK1 CHECK (COL1 IS NOT NAN);
The solution of the above error can be, divided into 2 steps
Step 1: Create a new table in SQL Server [The equivalent of BINARY_FLOAT in SQL Server is FLOAT(53)] without including the check constraint.
Step 2: Modify the application code for inserting the values in this table. Create a TRY… CATCH block which would be restricting the users to insert an unsupported value and generate an error message similar to an error generated from a check constraint.
SET @VAL = 1/0
INSERT INTO NANTEST VALUES (@VAL)
Print ('SQL error is ' + convert(varchar(20),Error_Number()) + ' : ' + convert(varchar(100),ERROR_MESSAGE()))
Trying to use the Transact-SQL ISNUMERIC() function in a check constraint for this scenario is not a viable solution in the check constraint because ISNUMERIC() is designed to check for strings that might represent numeric values and not numeric underflow and overflow conditions.
For more information, check out the following references:
· Migrating Oracle to SQL Server 2008 White Paper