fullselect & subselect

Full Select

Description

A subdivision of the SELECT statement done in IBM DB2.

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

Grammar Syntax

Sub Select

The subselect is a component of the fullselect.

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

Grammar Syntax

Select Clause

The SELECT clause specifies the columns of the final result table.

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

Grammar Syntax

All the grammar specified in this select clause of DB2 is ANSI compliant, equivalent to Snowflake, and is therefore translated as is by SnowConvert.

From Clause

All information about this part of the syntax is specified on the from-clause page.

Where Clause

The WHERE clause specifies an intermediate result table that consists of those rows of R for which the search-condition is true. R is the result of the FROM clause of the subselect.

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

Grammar Syntax

All the grammar specified in this where clause of DB2 is ANSI compliant, equivalent to Snowflake, and is therefore translated as is by SnowConvert.

Group By Clause

The GROUP BY clause specifies an intermediate result table that consists of a grouping of the rows of R. R is the result of the previous clause of the subselect.

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

Grammar Syntax

Grouping Expressions

A grouping expression is an expression used in defining the grouping of R. Each expression or column name included in grouping-expression must unambiguously identify a column of R. A grouping expression cannot include a scalar fullselect or an XMLQUERY or XMLEXISTS expression, or any expression or function that is not deterministic or has an external action.

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

Sample Source Patterns

IBM DB2

Code 1

   select job from employee group by job;

Result

Snowflake

Code 1

   select job from employee group by job;

Result

No explicit column reference

The following expressions, which do not contain an explicit column reference, can be used in a grouping-expression to identify a column of R:

• ROW CHANGE TIMESTAMP FOR table-designator

• ROW CHANGE TOKEN FOR table-designator

• RID_BIT or RID scalar function

ROW CHANGE Expressions and RID/RID_BIT scalar functions are not supported in Snowflake.

Sample Source Patterns

IBM DB2

Input Code for ROW CHANGE Expression

   select * from product group by ROW CHANGE TIMESTAMP FOR product;

Snowflake

Output Code for ROW CHANGE Expression

   select * from product
-- ** MSC-ERROR - MSCEWI1021 - GROUP BY ROW CHANGE TIMESTAMP FOR NOT SUPPORTED **
--                      group by ROW CHANGE TIMESTAMP FOR product
;

IBM DB2

Input code for RID scalar function

   select * from product group by RID();

Snowflake

Result

   select * from product
-- ** MSC-ERROR - MSCEWI1021 - GROUP BY scalar function RID NOT SUPPORTED **
--                      group by RID()
;

Grouping Sets

A grouping-sets specification can be used to specify multiple grouping clauses in a single statement.

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

Grammar Syntax

Sample Source Patterns

IBM DB2

Code 1

   select job from employee group by GROUPING SETS ((job));

Result

Snowflake

Code 1

   select job from employee group by GROUPING SETS ((job));

Result

Super Groups

Super-groups are grouping extensions to the GROUP BY clause, that produces a result set with additional aggregate rows whose values are derived by applying the same aggregate functions that were used to obtain the grouped rows.

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

Grammar Syntax

Sample Source Patterns

IBM DB2

Code 1

   select job from employee group by ROLLUP(job);

Result

Snowflake

Code 1

   select job from employee group by ROLLUP(job);

Result

IBM DB2

Code 1

   select job from employee group by job WITH CUBE;

Result

Snowflake

Code 1

   select job from employee
GROUP BY CUBE(job);

Result

Offset Clause

All information about this part of the syntax is specified on the offset-clause page.

Fetch Clause

All information about this part of the syntax is specified on the fetch-clause page.

Isolation Clause

All information about this part of the syntax is specified on the isolation-clause page.

Related EWIs

1. MSCEWI1021: NODE NOT SUPPORTED