call us toll-free +1 855 855 3600
 
Home > Blog > Oracle to SQL Server migration with SSMA: How to convert aggregate functions referring to remote table

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

Posted by | On | In Database Migration, Microsoft SQL Server, Microsoft SQL Server Migration Assistant (SSMA), Operational Data Management, Oracle Database | Tags:
 

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.AA_CTL_BATCHCONTROL@TEST_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.

 
       
 

Comments are closed.