SELECT

Description

Retrieves information from the database. (Sybase SQL Language Reference)

This syntax is partially supported in Snowflake.

Grammar Syntax

SELECT 
[ ALL | DISTINCT ] 
[ row-limitation-option1 ] 
   	select-list
   … 	[ INTO { host-variable-list | variable-list | table-name } ]
   … 	[ INTO LOCAL TEMPORARY TABLE { table-name } ]
   … 	[ FROM table-list ]
   … 	[ WHERE search-condition ]
   … 	[ GROUP BY [ expression [, ...]
         | ROLLUP ( expression [, ...] )
         | CUBE ( expression [, ...] ) ] ] 
   … 	[ HAVING search-condition ]
   … 	[ ORDER BY { expression | integer } [ ASC | DESC ] [, ...] ]
   | 	[ FOR JSON json-mode ] 
   … [ row-limitation-option ]

select-list:
   { column-name
   | expression [ [ AS ] alias-name ]
   | * 
   }

row-limitation-option1:
   FIRST 
   | TOP {ALL | limit-expression} [START AT startat-expression ]
  
limit-expression:
    simple-expression
  
startat-expression:
    simple-expression

row-limitation-option2:
   LIMIT { [ offset-expression, ] limit-expression 
   | limit-expression OFFSET offset-expression }

offset-expression:
   simple-expression

simple-expression:
   integer
   | variable
   | ( simple-expression )
   | ( simple-expression { + | - | * } simple-expression )


..FROM <table-expression> [,...]

<table-expression> ::=
   <table-name>
   | <view-name>
   | <procedure-name>
   | <common-table-expression>
   | ( <subquery> ) [ [ AS ] <derived-table-name> ( <column_name, ...>) ] ]
   | <derived-table>
   | <join-expression> 
   | ( <table-expression> , ... )
   | <openstring-expression>
   | <apply-expression>
   | <contains-expression>
   | <dml-derived-table>

<table-name> ::=
   [ <userid>.] <table-name> ]
   [ [ AS ] <correlation-name> ]
   [ FORCE INDEX ( <index-name> ) ]

<view-name> ::=
   [ <userid>.]<view-name> [ [ AS ] <correlation-name> ]

<procedure-name> ::=
   [  <owner>, ] <procedure-name> ([ <parameter>, ...])
   [  WITH(<column-name datatype>, )]
   [ [ AS ] <correlation-name> ]

<parameter> ::=
   <scalar-expression> | <table-parameter>

<table-parameter> ::= 
   TABLE (<select-statement)> [ OVER ( <table-parameter-over> )]

<table-parameter-over> ::=
   [ PARTITION BY {ANY
   | NONE|< table-expression> } ] 
   [ ORDER BY { <expression> | <integer> } 
   [ ASC | DESC ] [, ...] ]

<derived-table> ::=
   ( <select-statement> ) 
   	[ AS ] <correlation-name> [ ( <column-name>, ... ) ]

<join-expression> ::=
   <table-expression> <join-operator> <table-expression>
   	[ ON <join-condition> ]

<join-operator> ::=
   [ KEY | NATURAL ] [ <join-type> ] JOIN | CROSS JOIN

<join-type> ::=
   INNER
     | LEFT [ OUTER ]
     | RIGHT [ OUTER ]
     | FULL [ OUTER ]

<openstring-expression> ::=
   OPENSTRING ( { FILE | VALUE } <string-expression> )
     WITH ( <rowset-schema> ) 
   	[ OPTION ( <scan-option> ...  ) ]
   	[ AS ] <correlation-name>

<apply-expression> ::=
   <table-expression> { CROSS | OUTER } APPLY <table-expression>

<contains-expression> ::=
   { <table-name>  | <view-name> } CONTAINS 
   ( <column-name> [,...], <contains-query> ) 
   [ [ AS ] <score-correlation-name> ]

<rowset-schema> ::=
   <column-schema-list>
	   | TABLE [<owner>.]<table-name> [ ( <column-list> ) ]

<column-schema-list> ::=
   { <column-name user-or-base-type> |  filler( ) } [ , ... ]

<column-list> ::=
   { <column-name> | filler( ) } [ , ... ]

<scan-option> ::=
   BYTE ORDER MARK { ON | OFF }
   | COMMENTS INTRODUCED BY <comment-prefix>
   | DELIMITED BY <string>
   | ENCODING <encoding>
   | ESCAPE CHARACTER <character>
   | ESCAPES { ON | OFF }
   | FORMAT { TEXT  | BCP  }
   | HEXADECIMAL { ON | OFF }
   | QUOTE <string>
   | QUOTES { ON | OFF }
   | ROW DELIMITED BY string
   | SKIP <integer>
   | STRIP { ON | OFF | LTRIM | RTRIM | BOTH }

<contains-query> ::= <string>

<dml-derived-table> ::=
   ( <dml-statement>  ) REFERENCING ( [ <table-version-names>  | NONE ] )

<dml-statement> ::=
   <insert-statement> 
   <update-statement>
   <delete-statement>

<table-version-names> ::=
   OLD [ AS ] <correlation-name> [ FINAL [ AS ] <correlation-name> ]
     | FINAL [ AS ] <correlation-name>

Sample Source Patterns

Row Limitation

Sybase allow row limitation in a query by using TOP clause with an optional START AT that Snowflake not supports but can transformed as down below to achieve the same functionality.

Input Code:

Sybase

IN -> Sybase_01.sql

SELECT
TOP 10 START AT 2
COL1 
FROM TABLE1;

SELECT
FIRST
COL1 
FROM TABLE1;

SELECT
COL1 
FROM TABLE1
LIMIT 2, 1;

SELECT
COL1 
FROM TABLE1
LIMIT 1 OFFSET 2;

Output Code:

Snowflake

OUT -> Sybase_01.sql

SELECT
COL1
FROM
TABLE1
LIMIT 10 OFFSET 2;

SELECT
TOP 1
COL1
FROM
TABLE1;

SELECT
COL1
FROM
TABLE1
LIMIT 1 OFFSET 2;

SELECT
COL1
FROM
TABLE1
LIMIT 1 OFFSET 2;

Into Clause

In Sybase a Table can be defined by selecting multiple rows and defining a name to stored the date retrieved. Snowflake does not support this behavior but can emulated by doing a CREATE TABLE AS.

Input Code:

Sybase

IN -> Sybase_02.sql

SELECT
* INTO mynewtable
FROM TABLE1;

SELECT
* INTO LOCAL TEMPORARY TABLE mynewtable
FROM TABLE1;

SELECT
* INTO #mynewtable
FROM TABLE1;

Output Code:

Snowflake

OUT -> Sybase_02.sql

CREATE OR REPLACE TABLE mynewtable AS
SELECT
*
FROM
TABLE1;

CREATE OR REPLACE TEMPORARY TABLE mynewtable AS
SELECT
*
FROM
TABLE1;

CREATE OR REPLACE TEMPORARY TABLE T_mynewtable AS
SELECT
*
FROM
TABLE1;

Force Index

Snowflake does not contain indexes for query optimization.

Input Code:

Sybase

IN -> Sybase_03.sql

SELECT * FROM MyTable FORCE INDEX (MyIndex);

Output Code:

Snowflake

OUT -> Sybase_03.sql

SELECT
*
FROM
MyTable
--        --** SSC-FDM-SY0002 - FORCE INDEX IS NOT SUPPORTED IN SNOWFLAKE **
--        FORCE INDEX (MyIndex)
                             ;

TABLE FUNCTIONS

Snowflake allows calling a stored procedure(when the procedure meets certain limitations) or a table value function in a FROM clause, but RESULTSETS and windowing cannot be used as parameters.

Input Code:

Sybase

IN -> Sybase_04.sql

SELECT * FROM 
MyProcedure(TABLE (SELECT * FROM TABLE1));

SELECT * FROM MyProcedure(1, 'test');

SELECT * FROM 
MyProcedure(
TABLE (SELECT * FROM TABLE1) 
OVER (PARTITION BY Col1 ORDER BY Col2 DESC));

SELECT * FROM 
MyProcedure(
TABLE (SELECT * FROM AnotherTable) );

Output Code:

Snowflake

OUT -> Sybase_04.sql

  SELECT
    *
  FROM
    --** SSC-FDM-SY0001 - CALLING STORED PROCEDURE IN FROM CLAUSE MIGHT HAVE COMPILATION ERRORS **
    TABLE(MyProcedure());
  
  SELECT
    *
  FROM
    --** SSC-FDM-SY0001 - CALLING STORED PROCEDURE IN FROM CLAUSE MIGHT HAVE COMPILATION ERRORS **
    TABLE(MyProcedure(
                      !!!RESOLVE EWI!!! /*** SSC-EWI-SY0004 - UNSUPPORTED SYNTAX TABLE FUNCTION CAN'T RECEIVE A QUERY AS PARAMETER ***/!!!
                      TABLE(
      SELECT
        *
      FROM
        TABLE1
                      )));
  
  SELECT
    *
  FROM
    --** SSC-FDM-SY0001 - CALLING STORED PROCEDURE IN FROM CLAUSE MIGHT HAVE COMPILATION ERRORS **
    TABLE(MyProcedure(1, 'test'));
  
  SELECT
    *
  FROM
    --** SSC-FDM-SY0001 - CALLING STORED PROCEDURE IN FROM CLAUSE MIGHT HAVE COMPILATION ERRORS **
    TABLE(MyProcedure(
    !!!RESOLVE EWI!!! /*** SSC-EWI-SY0004 - UNSUPPORTED SYNTAX TABLE FUNCTION CAN'T RECEIVE A QUERY AS PARAMETER ***/!!!
    TABLE(
      SELECT
        *
      FROM
        TABLE1
    )
    !!!RESOLVE EWI!!! /*** SSC-EWI-SY0005 - UNSUPPORTED SYNTAX TABLE FUNCTION CAN'T BE USED WITH OVER EXPRESSION ***/!!!
    OVER (
    PARTITION BY
      Col1
    ORDER BY Col2 DESC)));
  
  SELECT
    *
  FROM
    --** SSC-FDM-SY0001 - CALLING STORED PROCEDURE IN FROM CLAUSE MIGHT HAVE COMPILATION ERRORS **
    TABLE(MyProcedure(
    !!!RESOLVE EWI!!! /*** SSC-EWI-SY0004 - UNSUPPORTED SYNTAX TABLE FUNCTION CAN'T RECEIVE A QUERY AS PARAMETER ***/!!!
    TABLE(
      SELECT
        *
      FROM
        AnotherTable
    )));

OPEN STRING

Snowflake does not support OPENSTRING functionality.

Input Code:

Sybase

IN -> Sybase_05.sql

-- Openstring from file
SELECT * FROM 
OPENSTRING (FILE '/path/to/file.txt') 
WITH (Col1 INT, Col2 VARCHAR(20)) AS OS;

-- Openstring from value
SELECT * FROM 
OPENSTRING (VALUE '1,test') 
WITH (Col1 INT, Col2 VARCHAR(20)) AS OS;

-- Openstring with options
SELECT * FROM 
OPENSTRING (FILE '/path/to/file.csv') 
WITH (Col1 INT, Col2 VARCHAR(20)) 
OPTION (DELIMITED BY ',' QUOTE '"') AS OS;

Output Code:

Snowflake

OUT -> Sybase_05.sql

-- Openstring from file
SELECT
*
FROM
!!!RESOLVE EWI!!! /*** SSC-EWI-SY0006 - OPEN STRING IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
OPENSTRING (FILE '/path/to/file.txt')
WITH (Col1 INT, Col2 VARCHAR(20)) AS OS;

-- Openstring from value
SELECT
*
FROM
!!!RESOLVE EWI!!! /*** SSC-EWI-SY0006 - OPEN STRING IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
OPENSTRING (VALUE '1,test')
WITH (Col1 INT, Col2 VARCHAR(20)) AS OS;

-- Openstring with options
SELECT
*
FROM
!!!RESOLVE EWI!!! /*** SSC-EWI-SY0006 - OPEN STRING IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
OPENSTRING (FILE '/path/to/file.csv')
WITH (Col1 INT, Col2 VARCHAR(20))
OPTION (DELIMITED BY ',' QUOTE '"') AS OS;

DML Derived Table

In Sybase, during execution, the DML statement specified in the dml-derived table is executed first, and the rows affected by that DML materialize into a temporary table whose columns are described by the REFERENCING clause. The temporary table represents the result set of dml-derived-table. Snowflake does not support this behavior.

Input Code:

Sybase

IN -> Sybase_06.sql

-- DML derived table with insert
SELECT * FROM (INSERT INTO TargetTable (Col1, Col2) VALUES (1, 'test')) REFERENCING (FINAL AS F);

-- DML derived table with update
SELECT * FROM (UPDATE TargetTable SET Col2 = 'updated' WHERE Col1 = 1) REFERENCING (OLD AS O FINAL AS F);

-- DML derived table with delete
SELECT * FROM (DELETE FROM TargetTable WHERE Col1 = 1) REFERENCING (OLD AS O);

Output Code:

Snowflake

OUT -> Sybase_06.sql

-- DML derived table with insert
SELECT
*
FROM
!!!RESOLVE EWI!!! /*** SSC-EWI-SY0007 - DML DERIVED TABLE NOT SUPPORTED IN SNOWFLAKE ***/!!! (INSERT INTO TargetTable (Col1, Col2) VALUES (1, 'test')) REFERENCING (FINAL AS F);

-- DML derived table with update
SELECT
*
FROM
!!!RESOLVE EWI!!! /*** SSC-EWI-SY0007 - DML DERIVED TABLE NOT SUPPORTED IN SNOWFLAKE ***/!!! (UPDATE TargetTable SET Col2 = 'updated' WHERE Col1 = 1) REFERENCING (OLD AS O FINAL AS F);

-- DML derived table with delete
SELECT
*
FROM
!!!RESOLVE EWI!!! /*** SSC-EWI-SY0007 - DML DERIVED TABLE NOT SUPPORTED IN SNOWFLAKE ***/!!! (DELETE FROM TargetTable WHERE Col1 = 1) REFERENCING (OLD AS O);

KEY JOIN

Snowflake does not support KEY join but when the ON CLAUSE is defined in the query the KEY keyword and removed otherwise an EWI is inserted.

Input Code:

Sybase

IN -> Sybase_07.sql

SELECT * FROM Table1 KEY JOIN Table2;
SELECT * FROM Table1 KEY JOIN Table2 ON Table1.ID = Table2.ID;

Output Code:

Snowflake

OUT -> Sybase_07.sql

SELECT
*
FROM
Table1
!!!RESOLVE EWI!!! /*** SSC-EWI-SY0009 - KEY JOIN NOT SUPPORTED IN SNOWFLAKE ***/!!!
KEY JOIN
Table2;

SELECT
*
FROM
Table1
JOIN
Table2
ON Table1.ID = Table2.ID;

OUTER-CROSS APPLY

Snowflake transforms the clause the CROSS APPLY into LEFT OUTER JOIN and OUTER APPLY to INNER JOIN.

Snowflaes

Input Code:

Sybase

IN -> Sybase_08.sql

-- Apply cross apply
SELECT * FROM Table1 CROSS APPLY (SELECT Col2 FROM Table2 WHERE Table1.ID = Table2.ID) AS AP;

-- Apply outer apply
SELECT * FROM Table1 OUTER APPLY (SELECT Col2 FROM Table2 WHERE Table1.ID = Table2.ID) AS AP;

Output Code:

Snowflake

OUT -> Sybase_08.sql

-- Apply cross apply
SELECT
    *
FROM
    Table1
    LEFT OUTER JOIN (
        SELECT
            Col2
        FROM
            Table2
        WHERE
            Table1.ID = Table2.ID
    ) AS AP;

-- Apply outer apply
SELECT
    *
FROM
    Table1
    INNER JOIN LATERAL (
        SELECT
            Col2
        FROM
            Table2
        WHERE
            Table1.ID = Table2.ID
    ) AS AP;

CONTAINS Clause

In Sybase the CONTAINS clause following a table name to filter the table and return only those rows matching the full text query specified with contains-query. Every matching row of the table is returned together with a score column that can be referred to using score-correlation-name. Snowflake does not support this behavior.

Input Code:

Sybase

IN -> Sybase_09.sql

-- Contains clause
SELECT * FROM MyTable CONTAINS (TextColumn, 'search term') AS Score;

-- Contains clause with multiple columns.
SELECT * FROM MyTable CONTAINS (TextColumn,TextColumn2, 'search term') AS Score;

Output Code:

Snowflake

OUT -> Sybase_09.sql

-- Contains clause
SELECT
*
FROM
MyTable
        !!!RESOLVE EWI!!! /*** SSC-EWI-SY0008 - CONTAINS CLAUSE NOT SUPPORTED IN SNOWFLAKE ***/!!!
        CONTAINS (TextColumn, 'search term') AS Score;

-- Contains clause with multiple columns.
SELECT
*
FROM
MyTable
        !!!RESOLVE EWI!!! /*** SSC-EWI-SY0008 - CONTAINS CLAUSE NOT SUPPORTED IN SNOWFLAKE ***/!!!
        CONTAINS (TextColumn,TextColumn2, 'search term') AS Score;

Related EWIs

SSC-FDM-0009: GLOBAL TEMPORARY TABLE FUNCTIONALITY NOT SUPPORTED.

SSC-FDM-SY0001: CALLING STORED PROCEDURE IN FROM CLAUSE MIGHT HAVE COMPILATION ERRORS

SSC-FDM-SY0002: FORCE INDEX IS NOT SUPPORTED IN SNOWFLAKE

SSC-EWI-SY0004 - UNSUPPORTED SYNTAX TABLE FUNCTION CAN'T RECEIVE A QUERY AS PARAMETER

SSC-EWI-SY0005 - UNSUPPORTED SYNTAX TABLE FUNCTION CAN'T BE USED WITH OVER EXPRESSION

SSC-EWI-SY0006 - OPEN STRING IS NOT SUPPORTED IN SNOWFLAKE

SSC-EWI-SY0007 - DML DERIVED TABLE NOT SUPPORTED IN SNOWFLAKE

SSC-EWI-SY0008 - CONTAINS CLAUSE NOT SUPPORTED IN SNOWFLAKE

SSC-EWI-SY0009 - KEY JOIN NOT SUPPORTED IN SNOWFLAKE