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¶

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

Sample Source Patterns¶

IBM DB2¶

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

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
                                         ;

IBM DB2¶

    select * from product group by RID();

Snowflake¶

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

Related EWIs¶

1. 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;

Snowflake ¶

SELECT * FROM
   Product
FETCH NEXT 1 ROW ONLY;

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;

Snowflake ¶

SELECT * FROM
   Product
LIMIT NULL
OFFSET 3;

Limit X,Y¶

IBM DB2¶

SELECT * FROM Product LIMIT 3,2;

Snowflake ¶

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

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;

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;

Related EWIs¶

1. 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

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¶

:force:

VALUES 1, 2, 3

Snowflake¶

:force:

SELECT 1, 2, 3

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

IBM DB2¶

:force:
VALUES (1, 1, 1),
    (2, 2, 2),
    (3, 3, 3)

Snowflake¶

:force:

SELECT
   1, 1, 1
UNION
SELECT
   2, 2, 2
UNION
SELECT
   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;

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;