SQL Queries in SQL Server and Oracle - Intergraph Smart 3D - Help

Intergraph Smart 3D Common

Language
English
Product
Intergraph Smart 3D
Subproduct
Common
Search by Category
Help
Smart 3D Version
13.1

In Smart 3D, all Microsoft SQL Server and Oracle SQL syntax differences are handled internally, so you need not be concerned with the differences. The delivered filters work on both Microsoft SQL Server and Oracle. In addition, all SQL-based report query templates include the tag <ORASQL>, which defines the Oracle-specific version of that query. This tag has no impact on SQL Server users.

However, if you write your own direct queries for filters and report templates, you must be familiar with the syntax differences between SQL Server and Oracle. Both vendors have extended the SQL language.

Here are a few notable syntax differences. This list is not comprehensive.

  • Temporary tables - In Microsoft SQL Server, temporary tables can be created and referenced dynamically within an SQL statement. In Oracle, the temporary table must exist prior to the statement that references it.

  • Internal functions - Both SQL Server and Oracle have many built-in functions for string manipulation, date formatting, numerical tasks, and so forth. The function names may be different for the same functionality.

  • With Oracle 9i and above, the join syntax is the same so that should not be an issue for migration from SQL Server to Oracle.

Refer to the corresponding (Microsoft SQL Server or Oracle) documentation for more information about the best methods of writing SQL statements.