Tags: code conversion, database conversion errors
Oracle allows you to assign a non-scalar condition in WHERE clause. The problem is that SQL Server doesn’t support conditions of this type. Thus, the SQL Server Migration Assistant (SSMA) for Oracle doesn’t convert queries with a a non-scalar condition in WHERE clause. So, when you try to convert the source code that includes a non-scalar condition in a WHERE clause, SSMA will generate an error O2SS0001.
Source Code Example
Consider the following Oracle code example that includes a non-scalar condition in WHERE clause.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SELECT * FROM test_functions.test_func_emp_constr WHERE (first_name, last_name) IN ( SELECT 'SASHA','PIT' FROM dual UNION SELECT 'BRED', 'PIT' FROM dual ); SELECT * FROM test_functions.test_func_emp_constr WHERE (first_name, last_name) NOT IN ( SELECT 'SASHA','PIT' FROM dual UNION SELECT 'BRED', 'PIT' FROM dual ); |
This code fragment selects data from the table shown below.
Conversion Result
When you try to convert this code in SSMA, you will get the following error message: «O2SS0001: The following SQL clause cannot be converted».
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | /* * SSMA error messages: * O2SS0001: The following SQL clause cannot be converted: * ( first_name, last_name ) IN * ( * SELECT 'SASHA', 'PIT' * FROM dual * UNION * SELECT 'BRED', 'PIT' * FROM dual * ) SELECT TEST_FUNC_EMP_CONSTR.EMP_ID, TEST_FUNC_EMP_CONSTR.FIRST_NAME, TEST_FUNC_EMP_CONSTR.LAST_NAME, TEST_FUNC_EMP_CONSTR.DOB, TEST_FUNC_EMP_CONSTR.DEPT_ID, TEST_FUNC_EMP_CONSTR.JOB_ID, TEST_FUNC_EMP_CONSTR.COMM_PCT, TEST_FUNC_EMP_CONSTR.SAL FROM TEST_FUNCTIONS.TEST_FUNC_EMP_CONSTR WHERE /* * SSMA error messages: * O2SS0001: The following SQL clause cannot be converted: * ( first_name, last_name ) NOT IN * ( * SELECT 'SASHA', 'PIT' * FROM dual * UNION * SELECT 'BRED', 'PIT' * FROM dual * ) SELECT TEST_FUNC_EMP_CONSTR.EMP_ID, TEST_FUNC_EMP_CONSTR.FIRST_NAME, TEST_FUNC_EMP_CONSTR.LAST_NAME, TEST_FUNC_EMP_CONSTR.DOB, TEST_FUNC_EMP_CONSTR.DEPT_ID, TEST_FUNC_EMP_CONSTR.JOB_ID, TEST_FUNC_EMP_CONSTR.COMM_PCT, TEST_FUNC_EMP_CONSTR.SAL FROM TEST_FUNCTIONS.TEST_FUNC_EMP_CONSTR WHERE */ */ |
Possible Remedies
In order to resolve this issue, you should rewrite the converted query using EXISTS condition. This condition tests for the existence of rows in a subquery. Then you should substitute the AND condition with IN condition. Also, replace the OR condition with NOT IN. Thus, the updated SQL Server code will look as follows:
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT * FROM dbo.test_func_emp_constr e WHERE EXISTS ( SELECT 1 WHERE e.first_name IN ('BRED','SASHA') AND e.last_name IN ('PIT')) SELECT * FROM dbo.test_func_emp_constr e WHERE EXISTS ( SELECT 1 WHERE e.first_name NOT IN ('BRED','SASHA') OR e.last_name NOT IN ('PIT')) |
Do you have other questions or concerns about SSMA or Oracle to SQL Server migration issues? Make sure to check out our new Jumpstart for SSMA offer!
Related posts
- How to convert foreign keys with different types of columns and referenced columns
- How to convert cursor or cursor variable as a function or procedure call parameter
- How to convert unsupported table expressions
- How to convert Unparsed SQL — Pivot Operator
- How to convert materialized view with float type
- How to convert interval expressions
- How to convert interval literals
- How to convert database links
- How to convert aggregate functions referring to remote table
- How to convert virtual columns
Be sure to check the regularly updated table of contents for our series of blog posts on typical SSMA conversion errors.
Source of technical information: Oksana Eremenko, Technical Lead at DB Best.
