Blog: Oracle to SQL Server migration with SSMA: How to convert unsupported SQL clause

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.

O2SS0001 convert unsupported SQL clause

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.

O2SS0001 table

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

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.