Page History

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. 

SELECT	E.ENAME, D.DNAME
FROM	EMPLOYEE E, DEPARTMENT D
WHERE E.DEPT_ID = D.DEPT_ID(+)

SELECT	E.ENAME,	D.DNAME
FROM	EMPLOYEE E LEFT OUTER JOIN DEPARTMENT D
WHERE E.DEPT_ID = D.DEPT_ID

SELECT	E.ENAME,	D.DNAME
FROM	DEPARTMENT D RIGHT OUTER JOIN EMPLOYEE E
WHERE E.DEPT_ID = D.DEPT_ID

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

   Section

   Column width 50% ORACLE Code Block lang sql SELECT LPAD(' ',2*(LEVEL-1))|| item_name AS item_names, CONNECT_BY_ROOT item_id, CONNECT_BY_ROOT item_name FROM PC START WITH parent_id IS NULL CONNECT BY PRIOR item_id = parent_id; Column width 50% ALTIBASE HDB Code Block lang sql SELECT a.item_names, b.connect_by_root_item_id, b.connect_by_root_item_name FROM ( SELECT LPAD(' ',2*(LEVEL-1))|| item_name item_names FROM pc START WITH parent_id IS NULL CONNECT BY PRIOR item_id = parent_id ) a, ( SELECT item_id AS connect_by_root_item_id, item_name AS connect_by_root_item_name FROM pc WHERE LEVEL =1 START WITH parent_id IS NULL CONNECT BY PRIOR item_id = parent_id ) b;

2. CONNECT_BY_IS_LEAF

   Section

   Column width 50% ORACLE Code Block lang sql SELECT LPAD(' ',2*(LEVEL-1))|| item_name AS item_names, CONNECT_BY_ISLEAF FROM pc START WITH parent_id IS NULL CONNECT BY PRIOR item_id = parent_id; Column width 50% ALTIBASE HDB Code Block lang sql SELECT a.item_names, DECODE(b.parent_id,NULL,1,0) connect_by_isleaf FROM ( SELECT LPAD(' ',2*(level-1))|| item_name item_names, item_id FROM pc START WITH parent_id IS NULL CONNECT BY PRIOR item_id = parent_id ) a LEFT OUTER JOIN ( SELECT DISTINCT parent_id FROM pc ) b ON a.item_id = b.parent_id;

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

CREATE OR REPLACE FUNCTION sys_connect_by_path_pc ( pkey pc.parent_id%TYPE, plevel INTEGER, delim VARCHAR(10) )
RETURN VARCHAR2(200)
AS
path VARCHAR(200);
BEGIN
DECLARE CURSOR c1 IS
SELECT item_name
FROM pc
WHERE LEVEL <= plevel
START WITH item_id = pkey
CONNECT BY PRIOR parent_id=item_id ;


BEGIN
FOR crec IN c1 LOOP
path := delim || crec.item_name || path;
END LOOP;
RETURN path;
END ;
END;
/

SELECT LEVEL,
       SYS_CONNECT_BY_PATH(item_name,'/')
FROM   pc
START WITH parent_id IS NULL
CONNECT BY PRIOR item_id = parent_id;

SELECT LEVEL, SYS_CONNECT_BY_PATH_PC ( item_id, LEVEL, '/' )
FROM pc
START WITH parent_id IS NULL
CONNECT BY PRIOR item_id = parent_id;

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

  Section
  Column width 50% Oracle Code Block lang sql SELECT num, tri, level FROM triple WHERE num < 3001 START WITH num = 1 CONNECT BY NOCYCLE PRIOR tri = num; Column width 50% ALTIBASE HDB Code Block lang sql SELECT num, tri, level FROM triple WHERE num < 3001 START WITH num = 1 CONNECT BY PRIOR tri = num IGNORE LOOP;

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()

   Section
   Column width 50% ORACLE Code Block lang sql SELECT ename, sal, ROW_NUMBER() OVER (ORDER BY sal DESC) row_num FROM emp; Column width 50% ALTIBASE HDB Code Block lang sql SELECT ename, sal, rownum row_num FROM ( SELECT ename, sal FROM emp ORDER BY sal DESC ) x;

2. ROW_NUMBER PARTITION BY

SELECT ename,
       deptno,
       sal,
       ROW_NUMBER() OVER (PARTITION BY deptno
                          ORDER BY sal DESC)
FROM EMP;

SELECT ename,
deptno, (rownum - count_row) dense_rank
FROM
(
SELECT a.deptno,
a.ename,
a.sal,
(SELECT COUNT(*y) count_row
FROM emp b
WHERE b.deptno < a.deptno) count_row
FROM emp a ORDER BY deptno, sal DESC
) x
ORDER BY deptno, dense_rank;

1. RANK()

   Section
   Column width 50% ORACLE Code Block lang sql SELECT ename, sal, RANK() OVER (ORDER BY sal DESC) rank FROM emp Column width 50% ALTIBASE HDB Code Block lang sql SELECT ename, sal, rank FROM ( SELECT a.ename, a.sal, (SELECT 1+COUNT(*) FROM emp b WHERE b.sal > a.sal) rank FROM emp a ) x ORDER BY x.rank;

2. RANK() PARTITION BY

   Section
   Column width 50% ORACLE Code Block lang sql SELECT ename, deptno, sal, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) rank FROM EMP; Column width 50% ALTIBASE HDB Code Block lang sql SELECT ename, deptno, sal, rank FROM ( SELECT a.ename, a.deptno, a.sal, (SELECT 1+COUNT(*) FROM emp b WHERE b.deptno = a.deptno AND b.sal > a.sal) rank FROM emp a ) x ORDER BY x.deptno, x.rank;

3. DENSE_RANK()

   Section
   Column width 50% ORACLE Code Block lang sql SELECT ename, sal, DENSE_RANK() OVER (ORDER BY sal DESC) FROM emp; Column width 50% ALTIBASE HDB Code Block lang sql SELECT ename, sal, dense_rank FROM ( SELECT ename, a.sal, (SELECT 1+COUNT(DISTINCT sal) FROM emp b WHERE b.sal > a.sal) dense_rank FROM emp a ) x ORDER BY x.dense_rank;

4. DENSE_RANK PARTITION BY

   Section

   Column width 50% ORACLE Code Block lang sql SELECT ename, deptno, sal, DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) FROM EMP; Column width 50% ALTIBASE HDB Code Block lang sql SELECT ename, deptno, sal, dense_rank FROM ( SELECT a.ename, a.deptno, a.sal, (SELECT 1+COUNT(DISTINCT sal) FROM emp b WHERE b.deptno = a.deptno AND b.sal > a.sal) dense_rank FROM emp a ) x ORDER BY x.deptno, x.dense_rank;

ROLLUP / CUBE

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

1. ROLLUP

   Section

   Column width 50% ORACLE Code Block lang sql SELECT group_id, sales_emp, SUM(sales_qty) FROM tmp_sales GROUP BY ROLLUP(group_id,sales_emp); Column width 50% ALTIBASE HDB Code Block lang sql SELECT group_id, sales_emp, SUM(sales_qty) FROM ( SELECT DECODE(no, 1, group_id, 2, group_id) group_id, DECODE(no, 1, sales_emp) sales_emp, sales_qty FROM tmp_sales, (SELECT LEVEL no FROM dual CONNECT BY LEVEL <= 3) copy_t ) GROUP BY group_id, sales_emp ORDER BY 1, 2;

2. CUBE

   Section

   Column width 50% ORACLE Code Block lang sql SELECT group_id, sales_emp, SUM(sales_qty) FROM tmp_sales GROUP BY CUBE(group_id, sales_emp); Column width 50% ALTIBASE HDB Code Block lang sql SELECT NVL(group_id,' '), NVL(sales_emp,' '), Sum(sales_qty) FROM ( SELECT DECODE(no, 1, group_id, 2, group_id) group_id, no, DECODE(no, 1, sales_emp, 4, sales_emp) sales_emp, sales_qty FROM tmp_sales, (SELECT LEVEL no FROM dual CONNECT BY LEVEL <= 4) copy_t ) GROUP BY group_id, sales_emp ORDER BY 1, 2;

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

SELECT *
 FROM (SELECT customer_id, product_code, quantity
     FROM pivot_test) PIVOT(SUM(quantity) AS sum_quantity FOR (product_code)IN ('A' AS a, 'B' AS b, 'C' AS c, 'D' AS d))
     ORDER BY customer_id;

ALTIBASE HDB

SELECT customer_id, SUM(DECODE(product_code, 'A', quantity, NULL)) AS a_sum_quantity,
	SUM(DECODE(product_code, 'B', quantity, NULL)) AS b_sum_quantity,
         SUM(DECODE(product_code, 'C', quantity, NULL)) AS c_sum_quantity,
	SUM(DECODE(product_code, 'D', quantity, NULL)) AS d_sum_quantity
 FROM pivot_test
 GROUP BY customer_id
 ORDER BY customer_id;

UNPIVOT

 
ORACLE  

SELECT *
FROM (SELECT customer_id, product_code, quantity FROM pivot_test) UNPIVOT
(SUM(quantity) AS sum_quantity
FOR (product_code)
IN ('A' AS a, 'B' AS b, 'C' AS c, 'D' AS d))
ORDER BY customer_id;

ALTIBASE HDB

SELECT customer_id, product_code, quantity as quantity-- altibase
FROM (SELECT customer_id, decode(product_code,'A','A_SUM_QUANTITY', 'B', 'B_SUM_QUANTITY', 'C', 'C_SUM_QUANTITY', 'D', 'D_SUM_QUANTITY', 0) product_code,
			sum(quantity) as quantity FROM pivot_test
			group by customer_id, product_code)
ORDER BY customer_id, product_code;

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.

create or replace procedure proc1
as
cursor c1 is
select i1, i2 from t2;

	v1 varchar(10);
 	v2 varchar(10);
begin
 	open c1;
loop
 	fetch c1 into v1, v2;
	exit when c1%NOTFOUND;
	update t1 set i2 = v2, i3 = 'UPDATE' where i1 = v1;

	if SQL%NOTFOUND then
 		insert into t1 (i1, i2, i3) values (v1, v2, 'INSERT');
	end if;
end loop;

 	close c1;
end;
/

RETURNING clause

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

create or replace procedure proc1 as
x1 integer;
x2 varchar(30);
x3 integer;
begin
delete from employee where eno = 1 return eno, ename, deptno into x1, x2, x3;
println( 'x1='||x1||', x2='||x2||', x3='||x3);
end;
/

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.

SELECT DNO, E_LASTNAME, SALARY,
FIRST_VALUE(E_LASTNAME) OVER (PARTITION BY DNO ORDER BY SALARY DESC
ROWS UNBOUNDED PRECEDING) as DEPT_RICH
FROM EMPLOYEES;

SELECT B.DNO, B.E_LASTNAME, B.SALARY, A.DEPT_RICH
 FROM (SELECT DNO, E_LASTNAME AS DEPT_RICH
     FROM EMPLOYEES
     WHERE (DNO, SALARY) IN (SELECT DNO, MAX(SALARY)
                  FROM EMPLOYEES
                   GROUP BY DNO))A, EMPLOYEES B
 WHERE A.DNO = B.DNO;

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.

SELECT dno, e_lastname, salary,
    LAST_VALUE(E_LASTNAME) OVER
    (PARTITION BY DNO ORDER BY SALARY DESC
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as DEPT_POOR
 FROM employees;

SELECT B.DNO, B.E_LASTNAME, B.SALARY, A.DEPT_RICH
 FROM (SELECT DNO, E_LASTNAME AS DEPT_RICH
     FROM EMPLOYEES
     WHERE (DNO, SALARY) IN (SELECT DNO, MIN(SALARY)
                  FROM EMPLOYEES
                   GROUP BY DNO))A, EMPLOYEES B
 WHERE A.DNO = B.DNO
 ORDER BY DNO ASC, SALARY DESC;