Select

In this section you could find information about the select query syntax and its convertions.

Some parts in the output codes are omitted for clarity reasons.

Overall Select Translation

Simple select

Input Code:

IN -> Oracle_01.sql
select * from table1;
select col1 from schema1.table1;

Output Code:

OUT -> Oracle_01.sql
select * from
table1;

select col1 from
schema1.table1;

Where clause

Input Code:

IN -> Oracle_02.sql
select col1 from schema1.table1 WHERE col1 = 1 and id > 0 or id < 1;

Output Code:

OUT -> Oracle_02.sql
select col1 from
schema1.table1
WHERE col1 = 1 and id > 0 or id < 1;

Order By clause

Input Code:

IN -> Oracle_03.sql
select col1 from schema1.table1 order by id ASC;

Output Code:

OUT -> Oracle_03.sql
select col1 from
schema1.table1
order by id ASC;

Group by

Input Code:

IN -> Oracle_04.sql
select col1 from schema1.table1 GROUP BY id;

Output Code:

OUT -> Oracle_04.sql
select col1 from
schema1.table1
GROUP BY id;

Model Clause

The model clause is not supported yet.

Row Limiting Clause

Input Code:

IN -> Oracle_05.sql
-- Using ONLY
select * from TableFetch1 FETCH FIRST 2 ROWS ONLY;
select * from TableFetch1 FETCH FIRST 20 percent ROWS ONLY;
select * from TableFetch1 order by col1 FETCH FIRST 2 ROWS with ties;
select * from TableFetch1 order by col1 FETCH FIRST 20 percent ROWS with ties;

-- Using OFFSET clause
select * from TableFetch1 offset 2 rows FETCH FIRST 2 ROWS ONLY;
select * from TableFetch1 offset 2 rows FETCH FIRST 60 percent rows ONLY;
select * from TableFetch1 
order by col1 offset 2 rows FETCH NEXT 2 ROWs with ties;
select * from TableFetch1 
order by col1 offset 2 rows FETCH FIRST 60 percent ROWs with ties;

-- Using WITH TIES clause
select * from TableFetch1 FETCH FIRST 2 ROWS with ties;
select * from TableFetch1 FETCH FIRST 20 percent ROWS with ties;
select * from TableFetch1 offset 2 rows FETCH NEXT 2 ROWs with ties;
select * from TableFetch1 offset 2 rows FETCH FIRST 60 percent ROWs with ties;

-- Using ORDER BY clause
select * from TableFetch1 order by col1 FETCH FIRST 2 ROWS ONLY;
select * from TableFetch1 order by col1 FETCH FIRST 20 percent ROWS ONLY;
select * from TableFetch1 order by col1 offset 2 rows FETCH FIRST 2 ROWS ONLY;
select * from TableFetch1 
order by col1 offset 2 rows FETCH FIRST 60 percent ROWS ONLY;

select * from TableFetch1 FETCH FIRST ROWS ONLY;

select * from TableFetch1 offset 2 rows;

Output Code:

OUT -> Oracle_05.sql
-- Using ONLY
select * from
TableFetch1
FETCH FIRST 2 ROWS ONLY;

select * from
TableFetch1
QUALIFY
(ROW_NUMBER() OVER (
ORDER BY
NULL) - 1) / COUNT(*) OVER () < 20 / 100;

select * from
TableFetch1
QUALIFY
RANK() OVER (
order by col1) <= 2;

select * from
TableFetch1
QUALIFY
(RANK() OVER (
order by col1) - 1) / COUNT(*) OVER () < 20 / 100;

-- Using OFFSET clause
select * from
TableFetch1
offset 2 rows FETCH FIRST 2 ROWS ONLY;

select * from
TableFetch1
QUALIFY
(ROW_NUMBER() OVER (
ORDER BY
NULL) - 1 - 2) / COUNT(*) OVER () < 60 / 100
LIMIT NULL OFFSET 2;

select * from
TableFetch1
QUALIFY
RANK() OVER (
order by col1) - 2 <= 2
LIMIT NULL OFFSET 2;