Nested Table Expression

Description

A fullselect in parentheses is called a nested table expression. The intermediate result table is the result of that fullselect.

Click here to navigate to the IBM DB2 docs page for this syntax.

Nested Table Expression is partially applicable in Snowflake.

Grammar Syntax

Sample Source Patterns

Supported cases

IBM DB2

Code 1

select empno, firstnme, lastname
      from
         (select * from employee where workdept = 'D21');

Result

Snowflake

Code 1

select empno, firstnme, lastname
      from
         (select * from employee where workdept = 'D21');

Result

IBM DB2

Code 1

select
   deptno,
   empno
from
   department as d,
   lateral(
      select
         e.empno
      from
         employee as e
      where
         e.workdept = d.deptno
   )
 where deptno = 'D21';

Result

Snowflake

Code 1

select
   deptno,
   empno
from
   department as d,
   LATERAL(
      select
         e.empno
      from
         employee as e
      where
         e.workdept = d.deptno
   )
 where deptno = 'D21';

Result

IBM DB2

Code 1

select
   deptno,
   empno
from
   department as d,
   table(
      select
         e.empno
      from
         employee as e
      where
         e.workdept = d.deptno
   )
 where deptno = 'D21';

Result

Snowflake

Code 1

select
   deptno,
   empno
from
   department as d,
   LATERAL(
      select
         e.empno
      from
         employee as e
      where
         e.workdept = d.deptno
   )
 where deptno = 'D21';

Result

Unsupported cases

IBM DB2

Code 1

Select
   AValue
from
   LATERAL RETURN DATA UNTIL FEDERATED SQLSTATE VALUE 'stringConstant' WITHIN(
      Select
         AValue
      from
         ATable
   );

Snowflake

Code 1

Select
   AValue
from
   LATERAL
-- ** MSC-WARNING - MSCEWI1002 - REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE. CONTINUE HANDLER **
--           RETURN DATA UNTIL FEDERATED SQLSTATE VALUE 'stringConstant' WITHIN
                                                                             (
      Select
         AValue
      from
         ATable
   );

Related EWIs

1. MSCEWI1002: REMOVED NEXT STATEMENT, NOT APPLICABLE IN SNOWFLAKE.