SnowConvert AI - IBM DB2 - SELECT STATEMENT¶

Description¶

A subdivision of the SELECT statement done in IBM DB2.

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

Grammar Syntax¶

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 documentation page for this syntax.

Grammar Syntax¶

SuccessPlaceholder

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

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 documentation page for this syntax.

Grammar Syntax¶

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

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

Copy

Snowflake

select * from
 product
--!!!RESOLVE EWI!!! /*** SSC-EWI-0021 - GROUP BY ROW CHANGE TIMESTAMP FOR NOT SUPPORTED IN SNOWFLAKE ***/!!!
--group by ROW CHANGE TIMESTAMP FOR product
                                         ;

Copy

IBM DB2

    select * from product group by RID();

Copy

Snowflake

select * from
 product
--!!!RESOLVE EWI!!! /*** SSC-EWI-0021 - GROUP BY scalar function RID NOT SUPPORTED IN SNOWFLAKE ***/!!!
--group by RID()
              ;

Copy

SSC-EWI-0021

Fetch Clause

Description

Sets a maximum number of rows to be retrieved.

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

Grammar Syntax

Sample Source Patterns

Fetch without row count

IBM DB2

 SELECT * FROM Product FETCH First Row ONLY;
/* or */
SELECT * FROM Product FETCH First Rows ONLY;
/* or */
SELECT * FROM Product FETCH Next Row ONLY;
/* or */
SELECT * FROM Product FETCH Next Rows ONLY;

Copy

Snowflake

SELECT * FROM
   Product
FETCH NEXT 1 ROW ONLY;

Copy

Offset Clause

Description

Sets the number of rows to skip.

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

Grammar Syntax

Sample Source Patterns

Offset row-count

IBM DB2

 SELECT * FROM Product OFFSET 3 ROW;
/* or */
SELECT * FROM Product OFFSET 3 ROWS;

Copy

Snowflake

SELECT * FROM
   Product
LIMIT NULL
OFFSET 3;

Copy

Limit X,Y

IBM DB2

SELECT * FROM Product LIMIT 3,2;

Copy

Snowflake

SELECT * FROM
   Product
OFFSET 3 ROWS
FETCH NEXT 2 ROWS ONLY;

Copy

Order by Clause

Description

The ORDER BY clause specifies an ordering of the rows of the result table.

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

Grammar Syntax

Sample Source Patterns

The only paths of ORDER BY in Db2 that are not supported in Snowflake are those when it is used with ORDER OF and INPUT SEQUENCE; hence, if these are present, the clause will be marked with an EWI.

IBM DB2 Not Supported Examples

Select * from ORDERBYTest ORDER BY ORDER OF TableDesignator;
Select * from ORDERBYTest ORDER BY INPUT SEQUENCE;

Copy

Snowflake

Select * from
   ORDERBYTest
!!!RESOLVE EWI!!! /*** SSC-EWI-0021 - ORDER BY ORDER OF NOT SUPPORTED IN SNOWFLAKE ***/!!!
ORDER BY ORDER OF TableDesignator;


Select * from
   ORDERBYTest
!!!RESOLVE EWI!!! /*** SSC-EWI-0021 - ORDER BY INPUT SEQUENCE NOT SUPPORTED IN SNOWFLAKE ***/!!!
ORDER BY INPUT SEQUENCE;

Copy

Related EWIs

SSC-EWI-0021: NODE NOT SUPPORTED

Values Clause

Description

Derives a result table by specifying the actual values, using expressions or row expressions, for each column of a row in the result table. hin

Note

The VALUES clause is not supported in Snowflake. For this reason, it is translated to a SELECT statement, as shown in the examples below.

Grammar Syntax¶

Sample Source Patterns¶

The Values clause is not supported in Snowflake. For this reason, the values clause is translated to a select query.

IBM DB2¶

VALUES 1, 2, 3

Copy


1
2
3

Snowflake¶

SELECT 1, 2, 3

Copy


1	2	3

For the values with multiple rows, a Union is used:

IBM DB2¶

VALUES (1, 1, 1),
    (2, 2, 2), 
    (3, 3, 3)

Copy


1	1	1
2	2	2
3	3	3

Snowflake¶

SELECT
   1, 1, 1
UNION
SELECT
   2, 2, 2
UNION
SELECT
   3, 3, 3

Copy


1	1	1
2	2	2
3	3	3

Removed Clauses¶

Description¶

The following clauses are removed since they are not applicable in Snowflake:

FOR READ ONLY
Update Clause
Optimize for Clause
Concurrent access resolution Clause
Isolation Clause

Sample Source Patterns¶

IBM DB2¶

-- For Read Only
SELECT
   *
FROM
   Table1
FOR READ ONLY;


-- Update Clause
SELECT
   *
FROM
   Table1
FOR UPDATE OF
   COL1,
   COL2;


--Optimize For Clause
SELECT
   *
FROM
   Table1
OPTIMIZE FOR 2 ROWS;


-- Concurrent access resolution Clause
SELECT
   *
FROM
   Table1
WAIT FOR OUTCOME;


-- Isolation Clause
SELECT
   *
FROM
   Table1
WITH RR USE AND KEEP EXCLUSIVE LOCKS;

Copy

Snowflake¶

-- For Read Only
SELECT
   *
FROM
   Table1;


-- Update Clause
SELECT
   *
FROM
   Table1;


--Optimize For Clause
SELECT
   *
FROM
   Table1;


-- Concurrent access resolution Clause
SELECT
   *
FROM
   Table1;


-- Isolation Clause
SELECT
   *
FROM
   Table1;

Copy