SINGLE TABLE REFERENCE

Description

Each table-name specified as a table-reference must identify an existing table at the application server or an existing table at a remote server specified using a remote-object-name. The intermediate result table is the result of the table

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

Grammar Syntax

Navigate to the following pages to get more details about the translation spec for the subsections of the Table Reference grammar.

Period Specification

A period-specification identifies an intermediate result table consisting of the rows of the referenced table where the period matches the specification. A period-specification can be specified following the name of a temporal table or the name of a view

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

Period Specification is currently not supported by SnowFlake

Grammar Syntax

Sample Source Patterns

IBM DB2

Code 1

SELECT
   *
FROM
   Table1
FOR BUSINESS_TIME AS OF "12-12-12"

Snowflake

Code 1

-- ** MSC-ERROR - MSCEWI5003 - PERIOD SPECIFICATION IS NOT SUPPORTED IN SNOWFLAKE. **
--SELECT
--   *
--FROM
--   Table1
--FOR BUSINESS_TIME AS OF "12-12-12";

Correlation Clause

When a correlation-name is specified, column-names can also be specified to give names to the columns of the table reference. If the correlation-clause does not include column-names

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

Grammar Syntax

Sample Source Patterns

IBM DB2

Code 1

SELECT
   *
FROM
   sampleTable
correlationName (identifier, firstName)

Result

IDENTIFIER	FIRSTNAME
1	MARIA CARMEN
2	MARIA
3	CARMEN
4	ANA MARIA
5	JOSEFA

Snowflake

Code 1

SELECT
   *
FROM
   sampleTable
correlationName (identifier, firstName)

Result

IDENTIFIER	FIRSTNAME
1	MARIA CARMEN
2	MARIA
3	CARMEN
4	ANA MARIA
5	JOSEFA

Table Sample Clause

The optional tablesample-clause can be used to obtain a random subset (a sample) of the rows from the specified table-name, rather than the entire contents of that table-name, for this query. This sampling is in addition to any predicates that are specified in the where-clause

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

Grammar Syntax

Sample Source Patterns

IBM DB2

Code 1

SELECT
   *
FROM
   timetable
TABLESAMPLE SYSTEM (10) REPEATABLE (1);

Result

ID	NAME
56	YOLANDA
27	PILAR
42	PATRICIA
3	CARMEN
98	AINHOA

Snowflake

Code 1

SELECT
   *
FROM
   timetable
TABLESAMPLE SYSTEM (10) REPEATABLE (1);

Result

ID	NAME
56	YOLANDA
27	PILAR
42	PATRICIA
3	CARMEN
98	AINHOA

Related EWIs

1. MSCEWI5003: PERIOD SPECIFICATION is not supported in Snowflake.