4. SQL Conversion

Overview

Converting SQL queries from ORACLEto ALTIBASE HDB might be easy because ALTIBASE HDB has almost same SQL syntax with ORACLE.

Because of compatibility with ORACLE, ALTIBASE HDB try to accept Oracle syntax and features such as rownum psedocolumn, hierachy query, ORACLE-like bulit-in functions and so on. However,

because ALTIBASE HDB is not identical to ORACLE, you need to convert some SQL queries from ORACLE to ALTIBASE HDB if there're ORACLE proprietary features not provided by ALTIBASE HDB.

From ALTIBASE HDB V6, ALTIBASE HDB supports ORACLE-Style outer join statement.

Besides, Pivot Query, Ranking Functions have been applied at ALTIBASE HDB V6.
In this chapter, we will discuss

• ORACLE features not provided by ALTIBASE HDB
• Workarounds when you use ORACLE-supported features not supported by ALTIBASE HDB

OUTER JOIN expression

ALTIBASE HDB supports three types of outer join: right, left, and full.

The syntax of outer join is  slightly different between ORACLE and ALTIBASE HDB.

Converting example from ORACLE outer join to ALTIBASE HDB outer join is as follows.

Besides workarounds described above, ALTIBASE HDB supports officially ORACLE-Style Outer Join from ALTIBASE HDB V6.

ALTIBASE HDB does support Pivot query from ALTIBASE HDB V6 as well.

An ORACLE statement shown below is completely compatible with an ALTIBASE HDB statement. 

Table order specified after FROM clause is important to use ANSI-SQL outer join.

Because in case of the outer join statement, it is bound to execute full table scan by accessing all rows of a Table order specified after FROM clause.
It frequently causes SQL Tuning issue.

Hierarchy Query

ALTIBASE HDB provides the limited functionality about hierarchy query.

ALTIBASE HDB does not support following features among of those of Oracle provides

• CONNECT_BY_ISCYCLE pseudocolumn
• CONNECT_BY_ISLEAF pseudocolumn
• CONNECT_BY_ROOT operator
• SYS_CONNECT_BY_PATH function
• SIBLINGS in order by clause

Aside from CONNECT_BY_ISCYCLE and SIBLINGS, there're workarounds to replace above features.  

Assume that we have a table named PC and the table stores data as below.

1. CONNECT_BY_ROOT

   ORACLE

   ALTIBASE HDB
2. CONNECT_BY_IS_LEAF

   ORACLE

   ALTIBASE HDB

SYS_CONNECT_BY_PATH
To convert this pseudocolumn to ALTIBASE, you can use a user-defined function as shown below.

• NOCYCLE in ORACLE
  ALTIBASE has IGNORE LOOP keyword corresponding to NOCYCLE in Oracle. Here's an example.

  Oracle

  ALTIBASE HDB

About analytic features regarding ranking functions, Oracle provides ranking functions as follows at ALTIBASE HDB V6.

• ROW_NUMBER()
• RANK()
• DENSE_RANK()

But ALTIBASE HDB does not support above functions before ALTIBASE HDB V6.

So, if you wish to use above functions, you need to convert SQL.

Let's assume that we have a table named EMP which stores data as below.

 
Here're workaround samples when you want to convert ORACLE ranking functions to ALTIBASE HDB.

Besides described below, ALTIBASE HDB ranking functions are almost identical with ORACLE.

Those were applied at ALTIBASE HDB Vesion 6

1. ROW_NUMBER()

   ORACLE

   ALTIBASE HDB
2. ROW_NUMBER PARTITION BY

1. RANK()

   ORACLE

   ALTIBASE HDB
2. RANK() PARTITION BY

   ORACLE

   ALTIBASE HDB
3. DENSE_RANK()

   ORACLE

   ALTIBASE HDB
4. DENSE_RANK PARTITION BY

   ORACLE

   ALTIBASE HDB

ROLLUP / CUBE

Let's assume that we have a table named SALES which stores data as shown below.

1. ROLLUP

   ORACLE

   ALTIBASE HDB
2. CUBE

   ORACLE

   ALTIBASE HDB

PIVOT / UNPIVOT

Pivot queries involve transposing rows into columns (pivot) or columns into rows (unpivot) to generate results in crosstab format.

Use sum(decode) function to get appropriate result as follows. 

For pivotting,  table named pivot_test is described as shown below.  

PIVOT 

ORACLE

ALTIBASE HDB

UNPIVOT

 
ORACLE  

ALTIBASE HDB

MERGE statement

MERGE statement is convenient way to combine multiple operations.

ALTIBASE HDB does not support MERGE statement yet.

If your application uses MERGE statement, please seperate MERGE statement into INSERT, UPDATE, DELETE statement according to the kinds of transaction or procedure as below.

Merge statement has a workaround using procedure  as follows.

Using this statement to insert row, if it already exists, update or delete operation will be available instead of insert operation.

On the contrary, when no duplication detected, insert operation might be executed instead.

RETURNING clause

This function is used to return value of the column to the bind variable after the execution of SQL.

Analytic Features

Here are explanations about aggregate functions except those described at the ranking function above. 

This function computes an aggregate value based on a group of rows.

They differ from aggregate functions in that they return multiple rows for each group. 

FIRST_VALUE

It returns the first value in an ordered set of values.

If the first value in the set is null, then the function returns NULL unless you
specify IGNORE NULLS.

LAST_VALUE

It returns the last value in an ordered set of values.

If the last value in the set is null, then the function returns NULL unless you
specify IGNORE NULLS.