Tags: code conversion, database conversion errors
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.
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
- 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 unsupported SQL clause
- 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.
