SQL Translation Guidelines

This article provides you with an overview of what you need to take care of when you migrate your SQL statements, Views, Functions, and Stored Procedures from Oracle to Exasol, whether you are using scripts or ETL tools to load data into your data warehouse.

SQL Translation

General Differences

Object Names / Identifiers

Object Names / identifiers can be defined case sensitive (quoted identifiers) and case insensitive (nonquoted identifiers) in Oracle and Exasol. The difference is the characters allowed for each type. Exasol identifiers can be up to 128 characters long and can take up Oracle’s maximum potential identifier length of 128 bytes.

While in Oracle regular identifiers / nonquoted identifiers can contain alphanumeric characters, underscore (‘_’), dollar sign (‘$’) and pound sign (‘#’), even though Oracle strongly discourages using $ and # in nonquoted identifiers, Exasol supports alphanumeric characters and underscore. When a regular identifier in Oracle contains a $ or # symbol, it must be treated as a quoted identifier in Exasol.

SELECT Statement

In general, most Oracle SELECT Statements are compatible with Exasol. The following table describes some of the most important differences.

Oracle Exasol Comment 
select /*+parallel*/ *  from sales
 
select * from sales

Every query is executed in parallel by default.

 
WITH a AS (
SELECT trunc(sales_date, 'month') sales_month, product_price * quantity AS sub_total, category
FROM sales s 
JOIN sales_position sp ON s.sales_id = sp.sales_id 
JOIN dim_product dp ON sp.product_id = dp.product_id
)
SELECT sales_month, category, 
    sum(sub_total) sum_sub_total, 
    avg(sub_total) avg_sub_total
FROM a
GROUP BY sales_month, category
;
 
WITH a AS (
SELECT trunc(sales_date, 'month') sales_month, product_price * quantity AS sub_total, category
FROM sales s 
JOIN sales_position sp ON s.sales_id = sp.sales_id 
JOIN dim_product dp ON sp.product_id = dp.product_id
)
SELECT sales_month, category, 
    sum(sub_total) sum_sub_total, 
    avg(sub_total) avg_sub_total
FROM a
GROUP BY sales_month, category
;

This example shows a simple with clause, which is the same in Oracle and Exasol.

 
SELECT rowid FROM sales WHERE sales_id = 136006375;
 
SELECT rowid FROM sales WHERE sales_id = 136006375;

Both Oracle and Exasol, support the pseudo column ROWID. See ROWID for more details.

 
select next_day(sysdate, 'monday') from dual;
 
--/
create or replace function next_day(dt date, wd varchar(10)) 
return date
is 
wd_n integer;
d_diff integer;
begin
wd_n := 
case 
when lower(wd) = 'monday' then 1
when lower(wd) = 'tuesday' then 2
when lower(wd) = 'wednesday' then 3
when lower(wd) = 'thursday' then 4
when lower(wd) = 'friday' then 5
when lower(wd) = 'saturday' then 6
when lower(wd) = 'sunday' then 7
end;
d_diff := mod(abs(to_char(dt, 'ID') - wd_n), 7);
d_diff := case when d_diff = 0 then 7 else d_diff end;
return dt + d_diff;
end next_day;
/
;
select sysdate, next_day(sysdate, 'monday') from dual;

Exasol does not support the NEXT_DAY function , but it can be easily implemented as a function using a PL/SQL-like syntax. See CREATE FUNCTION for more details.

 
select sysdate, last_day(sysdate) ld from dual;
 
select sysdate, date_trunc('month', add_months(sysdate, 1)) -1 ld from dual;
 Exasol does not support the LAST_DAY function, but it can easily expressed using other functions. 
select standard_hash('Exasol rocks', 'MD5') from dual;
 
select hashtype_md5('Exasol rocks') from dual;

The return type of Oracle's STANDARD_HASH is RAW while the Exasol variant returns a hexadecimal representation of the type HASHTYPE. To compare both hashes, apply the RAWTOHEX() function on the RAW hash value.

 
select 1
from dual
where lnnvl(null = 1);
 
select 1 
from dual
where coalesce(not(null=1), true)
 Exasol does not support the LNNVL function, but it can easily expressed using other functions. 
select *
from sales_pv
pivot(
 median (no_sales) as med_sales
 for (week_day_number)
 in (1,2,3,4,5,6,7)
) 
;
 
Select
 store_id,
 median (case when week_day_number = 1 then no_sales end) med_no_sales_1,
 median (case when week_day_number = 2 then no_sales end) med_no_sales_2,
 median (case when week_day_number = 3 then no_sales end) med_no_sales_3,
 median (case when week_day_number = 4 then no_sales end) med_no_sales_4,
 median (case when week_day_number = 5 then no_sales end) med_no_sales_5,
 median (case when week_day_number = 6 then no_sales end) med_no_sales_6,
 median (case when week_day_number = 7 then no_sales end) med_no_sales_7
from sales_pv
group by store_id
 Exasol does not support the PIVOT function, but this example shows how it can be expressed in Exasol anyway. 
select 
 store_id,
 week_day_number,
 med_sales
from sales_upv
unpivot(
 med_sales
 for week_day_number in (
  "1_MED_SALES" AS 1,
  "2_MED_SALES" AS 2, 
  "3_MED_SALES" AS 3, 
  "4_MED_SALES" AS 4, 
  "5_MED_SALES" AS 5, 
  "6_MED_SALES" AS 6, 
  "7_MED_SALES" AS 7
 )
)
;
 
Select
store_id, 
week_day_number,
case 
when week_day_number = 1 then 
med_no_sales_1
when week_day_number = 2 then med_no_sales_2
when week_day_number = 3 then med_no_sales_3
when week_day_number = 4 then med_no_sales_4
when week_day_number = 5 then med_no_sales_5
when week_day_number = 6 then med_no_sales_6
when week_day_number = 7 then med_no_sales_7
end med_no_sales
from sales_upv, 
(select * from values 1, 2, 3, 4, 5, 6, 7 as t(week_day_number)) 
;
 Exasol does not support the UNPIVOT function, but this example shows how it can be expressed in Exasol anyway. 
SELECT s.*, avg(total_price) over(PARTITION BY sales_date ORDER BY sales_timestamp desc ROWS BETWEEN CURRENT ROW AND 20 following) avg_last_20_sales FROM sales s
 
SELECT s.*, avg(total_price) over(PARTITION BY sales_date ORDER BY sales_timestamp desc ROWS BETWEEN CURRENT ROW AND 20 following) avg_last_20_sales FROM sales s
 Both, Oracle and Exasol, support analytic functions. See Analytic Functions for more details. 
SELECT t.* -- all varchar
FROM t_json
NESTED json COLUMNS(PONumber, Reference,
NESTED LineItems[*] COLUMNS(ItemNumber, Quantity)) t;
 
select id,     json_extract(json, '$.PONumber', '$.Reference', '$.LineItems#.ItemNumber', '$.LineItems#.Quantity') 
emits(ponumber integer, poref varchar(200), itemnumber integer, quantity integer) from t_json;
 NESTED JSON COLUMNS is an alternative to JSON_TABLE in Oracle. Both can be translated using the JSON_EXTRACT function in Exasol 
select id, po_document 
from j_purchaseorder
where json_exists(po_document, '$.AllowPartialShipment');
 

select id, po_document
from (
select id, po_document, json_extract(po_document, '$.AllowPartialShipment', '$.error()') emits(j_aps varchar(20), j_error varchar(2000000)) as l
from j_purchaseorder
)
where j_error is null;

 To see more details about JSON usage in Exasol, see JSON Path Expressions, JSON Error Handling, JSON_VALUE, and JSON_EXTRACT. Exasol does not support the JSON_EXISTS function, but it can be rewritten using the error output of the JSON_EXTRACT function as a filter condition. 
select po.po_document 
from j_purchaseorder
where json_exists(po_document, '$?(@.LineItems.Part.UPCCode == 85391628927)');
 

select id, po_document
from (
select id, po_document, json_extract(po_document, '$.LineItems#.Part.UPCCode') emits(j_upccode decimal(36,0))
from j_purchaseorder
)
where j_upccode = 85391628927;
 Exasol does not support filters in JSON Path Expressions. To filter on a JSON document, the filter column needs to be extracted via the JSON_EXTRACT function, using the “#” symbol for normalization. On that dataset, the filter can be applied. 
select id, json_query(po_document, '$.LineItems[*].Part.UPCCode' with wrapper) as j_upccode_array
from j_purchaseorder;
 
SELECT id, json_value(po_document, '$.LineItems[*].Part.UPCCode.toarray()') as j_upccode_array
FROM j_purchaseorder;
 Oracle's JSON_QUERY function can be replaced by Exasol's JSON_VALUE function. The “WITH WRAPPER” option in Oracle transforms the output into an array like a varchar output. This can be achieved by applying the toarray() functions at the end of your json path expression. For more details see JSON Path Expressions. 
select id, jt.*
from j_purchaseorder po,
json_table(po.po_document, '$' columns(
j_user VARCHAR2(20) path '$.User',
nested path '$.LineItems[*]' 
columns (
j_upccode NUMBER path '$.Part.UPCCode',
j_quantity NUMBER path '$.Quantity',
j_unitprice NUMBER path '$.Part.UnitPrice'
)
)
) AS jt
where jt.j_quantity <= 5;
 
select *
from (
select id, json_extract(po_document, '$.User', '$.LineItems#.Part.UPCCode', 
'$.LineItems#.Quantity', '$.LineItems#.Part.UnitPrice') emits(j_user varchar(20), 
j_upccode decimal(18,0), 
j_quantity int, j_unitprice decimal(5,2)
)
FROM j_purchaseorder
)
where j_quantity <= 5
;
 The JSON_TABLE function in Oracle can be used to create a relational structure on a JSON document. This can be done using the JSON_EXTRACT function in Exasol.

DML Statements

Please note that RETURNING (used in PL/SQL) and ERROR clauses in DML statements are not supported in Exasol. The IMPORT and EXPORT statements, however, support reject limits, and the IMPORT statement can define error tables as well. Also, when executing queries via a Lua script, you can retrieve some metadata from the queries sent to the database. See Executing SQL statements through query() and pquery() for more details.

In Oracle, DMLs on very simple views can be pushed down to the base table. DMLs on views are not supported in Exasol and must be made on the base tables.

The following tables give you the most important differences in INSERT, UPDATE and DELETE statements.

INSERT

The following table describes the most important differences in INSERT statements between Oracle and Exasol:

Oracle Exasol Comment 
insert all
into sale_dst(
id, country , sale) values (id, 'DE', sale_de)
into sale_dst(
id, country , sale) values (id, 'UK', sale_uk)
into sale_dst(
id, country , sale) values (id, 'US', sale_us)
select * from sales_src;
 
insert into sale_dst
(id, country, sale)
select id, 'DE', sale_de from sales_src;

insert into sale_dst
(id, country, sale)
select id, 'UK', sale_uk from sales_src;

insert into sale_dst
(id, country, sale)
select id, 'US', sale_us from sales_src;

A MULTI-INSERT statement is not supported in Exasol, but It can be translated into multiple simple INSERT statements. The INSERT ALL variant goes through all records to insert them into multiple tables. If conditions are defined, all conditions are checked, even if a previous condition is true. 

insert first
when id <= 3 then
into dst_1 (id1, descr1) values (id, descr_de)
when id <= 5 then
into dst_2 (id2, descr2) values (id, descr_uk)
into dst_3 (id3, descr3) values (id, descr_us)
else
into dst_err(id, descr, ts) values(id, descr, current_timestamp)
select *
from src;
 

insert into dst_1
(id1, descr1) 
select id, descr_de 
from src where id <= 3;

insert into dst_2 
(id2, descr2) 
select id, descr_uk 
from src where not((id <= 3)) and id <=5;

insert into dst_3 
(id3, descr3) 
select id, descr_us 
from src where not((id <= 3)) and id <=5;

insert into dst_err
(id, descr, ts) 
select id, descr, current_timestamp 
from src where not((id <=3) or (id <= 5));

In this example, a conditional INSERT FIRST is used, meaning that the source record is only processed by the block, which can include multiple insert statements, where the condition becomes true first. When translating this to multiple Exasol INSERT statements, make sure to exclude all the records that were processed by a condition that was true before.

UPDATE

The following table describes the most important differences in UPDATE statements between Oracle and Exasol:

Oracle Exasol Comment 
update emp e set 
dep_id = (select dep2_id from dep n where e.dep_id = n.dep_id)
where e.dep_id in (select dep_id from dep)
;
 
update emp e
set dep_id = n.dep2_id
from emp e, dep n
where e.dep_id = n.dep_id;

While Exasol supports subsets returning single rows in the SET clause of the UPDATE statement, it does not support correlated subqueries in the SET clause. Those subqueries can be represented using the FROM clause to define the source tables and the WHERE clause for the join and filter conditions. 

update (
select sal
from emp e join dep d on e.dep_id = d.dep_id 
where mod(emp_id, 3) = 0
) e
set sal = sal * 1.15
;
 
update emp e
set sal = sal * 1.15
from emp e, dep d
where e.dep_id = d.dep_id
and mod(emp_id, 3) = 0
;

When certain requirements are met in Oracle, a query can be used to define a base dataset instead of a table. This feature is not supported in Exasol, but it can be easily rewritten using the FROM and WHERE clause of the UPDATE statement in Exasol. 

update emp e set 
dep_id = (
select dep_id 
from dep 
where loc_id = 3
),
(sal, com_pct) = (
select 
1.1 * avg(sal), 
1.5 * avg(com_pct) from emp b 
where e.dep_id = b.dep_id
)
where dep_id in ( select dep_id 
from dep 
where loc_id = 1
);
 
update emp e set
dep_id = n.n_dep_id,
sal = n.n_sal,
com_pct = n.n_com_pct
from emp e, (
select 
n_dep_id, 
dep_id, 
1.1 * avg(sal) n_sal,
1.5 * avg(com_pct) n_com_pct 
from emp, (
select dep_id n_dep_id 
from dep 
where loc_id = 3
) 
group by dep_id, n_dep_id
) n
where e.dep_id = n.dep_id and e.dep_id in (
select dep_id 
from dep 
where loc_id = 1)
;

Setting value in tuples is not supported in Exasol, hence all column updates must be set separately. Correlated updates can be rewritten using the FROM and WHERE clause of the UPDATE statement in Exasol. In the FROM clause, the target table must be referenced outside a subquery.

 

 
merge into emp e using(
select 
emp_id, 
n_dep_id, 
1.1 * avg(sal) n_sal, 
1.5 * avg(com_pct) n_com_pct 
from emp, 
(select dep_id n_dep_id 
from dep 
where loc_id = 3
)
where dep_id in (
select dep_id 
from dep 
where loc_id = 1)
group by emp_id, n_dep_id
) as n 
on(e.emp_id = n.emp_id)
when matched then update set 
dep_id = n_dep_id,
sal = n_sal,
com_pct = n_com_pct
;

Alternatively, the query can be rewritten into a MERGE statement as well. Note that grouping might be necessary to ensure a stable set of rows in the source query.

DELETE

The following table describes some important differences in DELETE statements between Oracle and Exasol. The FROM keyword in the DELETE statement is optional in Oracle whereas in Exasol it is mandatory.

Oracle Exasol Comment 
delete (
select * 
from emp e 
join dep d 
on e.dep_id = d.dep_id and mod(d.dep2_id, 2) = 1);
 
delete from emp e
where exists(
select 1 
from dep d 
where e.dep_id = d.dep_id and mod(d.dep2_id, 2) = 1);

When certain requirements are met in Oracle, a SELECT query can be defined to delete rows. This is not supported in Exasol. However, it can easily be rewritten to an Exasol DELETE statement, e. g. using the EXISTS predicate.