Blog: Oracle to SQL Server migration with SSMA: How to convert aggregate functions referring to remote table

Oracle allows you to create a query with aggregate functions referring to a remote table. For example, if your query includes two tables: one from the local schema and another from the remote database. You can simply use a database link in Oracle to create that construction. Previously, we already talked about converting database links using a linked server. But the problem is that SQL Server Migration Assistant (SSMA) for Oracle doesn’t convert queries that refer to the remote objects through the database link. So, when you try to convert a query with aggregate functions referring to a remote table, SSMA will generate an error O2SS0050.

SQL Server Migration Assistant for Oracle

Source Code Example

Consider the following Oracle code example that includes calls of MIN and MAX functions from a linked database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
EMP.FIRST_NAME,
EMP.LAST_NAME,
UPPER(BT.JOB_NAME) AS JN,
LOWER(BT.JOB_DESCRIPTION) AS JD,
SUM(EMP.SAL) AS SL,
MIN(BT.UNICENTER_STATUS/10) AS MS,
MAX(BT.CREATED_DATE - BT.JOB_STATUS_DATE) AS DT
FROM DWOWNER.[email protected]_DB_LINK BT
LEFT JOIN TEST_FUNCTIONS.TEST_FUNC_EMP_CONSTR EMP ON BT.JOB_ID = EMP.JOB_ID
GROUP BY EMP.FIRST_NAME,
EMP.LAST_NAME,
BT.JOB_NAME,
BT.JOB_DESCRIPTION;

When you try to convert this query in SSMA, you will get the following error message: «O2SS0050: Conversion of identifier ‘MIN(UNKNOWN)’ is not supported».

Conversion Result

So, the automatically converted code will look as follows:

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
/* SSMA error messages:
*O2SS0083: Identifier BT.JOB_NAME cannot be converted because it was not resolved.
*O2SS0083: Identifier BT.JOB_DESCRIPTION cannot be converted because it was not resolved.
*O2SS0050: Conversion of identifier 'MIN(UNKNOWN)' is not supported.
*O2SS0050: Conversion of identifier 'MAX(UNKNOWN)' is not supported.
*O2SS0083: Identifier DWOWNER.AA_CTL_BATCHCONTROL cannot be converted because it was
* not resolved.
*O2SS0083: Identifier BT.JOB_ID cannot be converted because it was not resolved.
*O2SS0083: Identifier BT.JOB_NAME cannot be converted because it was not resolved.
*O2SS0083: Identifier BT.JOB_DESCRIPTION cannot be converted because it was not resolved.
/* SSMA warning messages:
* O2SS0433: Unable to determine if GROUP BY clause contains constant expressions,
* because it contains unresolved identifiers.*/

SELECT
EMP.FIRST_NAME,
EMP.LAST_NAME,
UPPER(BT.JOB_NAME) AS JN,
LOWER(BT.JOB_DESCRIPTION) AS JD,
SUM(EMP.SAL) AS SL,
(NULL) AS MS,
(NULL) AS DT
FROM DWOWNER.AA_CTL_BATCHCONTROL AS BT
LEFT JOIN TEST_FUNCTIONS.TEST_FUNC_EMP_CONSTR AS EMP ON BT.JOB_ID = EMP.JOB_ID
GROUP BY
EMP.FIRST_NAME,
EMP.LAST_NAME,
BT.JOB_NAME,
BT.JOB_DESCRIPTION*/

As you can see, SSMA puts the (NULL) expression instead of expressions with aggregate functions referring to the remote table. If your source code includes a huge SELECT statement with several aggregate functions, you can skip some of the (NULL) expression accidentally. Please note that SSMA puts ‘O2SS0050’ errors at the beginning of the statement, but not near an unsupported expression. Consider that the (NULL) expression is valid in the terms of SQL syntax. So, you won’t receive an error during execution, but you will lose the query’s logic.

Possible Remedies

In order to resolve this issue, you should create the linked server on the SQL Server and convert all aggregate expressions manually. Consider using the same name for the linked server as Oracle’s database link. Finally, you will have to put the converted code in the appropriate place.

The updated SQL Server code will look as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
EMP.FIRST_NAME,
EMP.LAST_NAME,
UPPER(BT.JOB_NAME) AS JN,
LOWER(BT.JOB_DESCRIPTION) AS JD,
SUM(EMP.SAL) AS SL,
MIN(BT.UNICENTER_STATUS/10) AS MS,
MAX(ssma_oracle.datediff(BT.CREATED_DATE, BT.JOB_STATUS_DATE)) AS DT
FROM
DWOWNER.dbo.AA_CTL_BATCHCONTROL AS BT
LEFT JOIN dbo.TEST_FUNC_EMP_CONSTR AS EMP
ON BT.JOB_ID = EMP.JOB_ID
GROUP BY
EMP.FIRST_NAME,
EMP.LAST_NAME,
BT.JOB_NAME,
BT.JOB_DESCRIPTION

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.