Skip to main content

Search

Welcome to Tibero GTS!

Cross Apply Syntax Workaround

Document Type | Technical Information

Category | Migration

Applicable Product Version | 7FS02PS

Document Number | TMITI007

 

Overview

Tibero 7 FS02 PS02 version does not support Cross Apply syntax.
This guide explains how to work around the Cross Apply syntax used in Oracle to handle it in Tibero.
 
 
 

Method

Explanation of Cross Apply and Outer Apply Features

FeatureCross ApplyOuter Apply
Right Operand
Table expression (including table functions)
Table expression (including table functions)
Evaluation Method
Evaluates the right table expression for each row of the left table.
Evaluates the right table expression for each row of the left table.
Result Rows
(When no right expression result)
The corresponding row from the left table is retained, and columns from the right table expression are filled with NULL values. (Similar to LEFT OUTER JOIN)
The corresponding row from the left table is retained, and columns from the right table expression are filled with NULL values. (Similar to LEFT OUTER JOIN)

 

1. Cross Apply

Sample Data
SQL> create table A ( id number, name varchar(20));
SQL> insert into A values (1, 'KIM');
SQL> insert into A values (2, 'LEE');
SQL> insert into A values (3, 'PARK');
SQL> commit;
SQL> create table B ( id number, nickname varchar(20));
SQL> insert into B values (1, 'TEST01');
SQL> insert into B values (2, 'TEST02');
SQL> commit;

Executing cross apply syntax in Oracle
SQL> select * from A cross apply ( select * from B where A.id = B.id ) B;
    ID      NAME 			     ID         NICKNAME
---------- ---------  ----------      --------------------
     1      KIM				      1         TEST01
     2      LEE				      2         TEST02


SQL> select A.id, A.name, B.nickname from A cross apply ( select * from B where A.id = B.id ) B;


ID       NAME 		         NICKNAME
------- ------------      --------------------
 1       KIM			         TEST01
 2       LEE			         TEST02

Executing cross apply syntax in Tibero
SQL> select * from A cross apply ( select * from B where A.id = B.id ) B;
    ID      NAME 			     ID         NICKNAME
---------- ---------  ----------      --------------------
     1      KIM				      1         TEST01
     2      LEE				      2         TEST02


SQL> select A.id, A.name, B.nickname from A cross apply ( select * from B where A.id = B.id ) B;


ID       NAME 		         NICKNAME
------- ------------      --------------------
 1       KIM			         TEST01
 2       LEE			         TEST02

Executing cross apply syntax in Tibero
SQL> select * from A cross apply ( select * from B where A.id = B.id ) B;
TBR-8021: Invalid table name.
at line 1, column 23 of null:
select * from A cross apply ( select * from B where A.id = B.id ) B
                      ^

SQL> select A.id, A.name, B.nickname from A cross apply ( select * from B where A.id = B.id ) B;
TBR-8021: Invalid table name.
at line 1, column 46 of null:
select A.id, A.name, B.nickname from A cross apply ( select * from B where A.id

Workaround in Tibero using inner join
SQL> select * from A inner join b on A.id = B.id;

    ID NAME                         ID NICKNAME
------- ------------             ------- ------------  

     1 KIM                           1 TEST01
     2 LEE                           2 TEST02

2 rows selected.


SQL> select A.id, A.name, B.nickname from A inner join b on A.id = B.id;


    ID NAME                 NICKNAME
------- ------------       ----------

     1 KIM                  TEST01
     2 LEE                  TEST02

2 rows selected.

 

2. Outer Apply

Sample Data
SQL> create table A ( id number, name varchar(20));
SQL> insert into A values (1, 'KIM');
SQL> insert into A values (2, 'LEE');
SQL> insert into A values (3, 'PARK');
SQL> commit;
SQL> create table B ( id number, nickname varchar(20));
SQL> insert into B values (1, 'TEST01');
SQL> insert into B values (2, 'TEST02');
SQL> commit;

Executing outer apply syntax in Oracle
SQL> select * from A outer apply ( select * from B where A.id = B.id ) B;

ID      NAME 			     ID        NICKNAME
----  ---------  ----------      --------------------

 1      KIM				     1          TEST01
 2      LEE				     2          TEST02
 3      PARK

SQL> select A.id, A.name, B.nickname from A outer apply ( select * from B where A.id = B.id ) B;


ID      NAME 		        NICKNAME
------- ------------       ----------

 1      KIM			         TEST01
 2      LEE			         TEST02
 3      PARK

Executing cross apply syntax in Tibero
SQL> select * from A outer apply ( select * from B where A.id = B.id ) B;
TBR-8022: Invalid end of SQL.
at line 1, column 23 of null:
select * from A outer apply ( select * from B where A.id = B.id ) B
                      ^

SQL> select A.id, A.name, B.nickname from A outer apply ( select * from B where A.id = B.id ) B;
TBR-8022: Invalid end of SQL.
at line 1, column 46 of null:
select A.id, A.name, B.nickname from A outer apply ( select * from B where A.id

Workaround in Tibero using left outer join
SQL> select * from A left outer join b on A.id = B.id;

    ID NAME                         ID NICKNAME
------- ------------             ------- ------------  

     1 KIM                           1 TEST01
     2 LEE                           2 TEST02
     3 PARK

3 rows selected.


SQL> select A.id, A.name, B.nickname from A left outer join b on A.id = B.id;


    ID NAME                 NICKNAME
------- ------------       ----------

     1 KIM                  TEST01
     2 LEE                  TEST02
     3 PARK

3 rows selected.

 

 

 

Related to