SnowConvert AI - Sybase IQ - SELECT

説明

データベースから情報を取得します。 （ Sybase SQL 言語リファレンス ）

警告

この構文はSnowflakeで部分的にサポートされています。

文法構文

 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>

サンプルソースパターン

行の制限

Sybase allows row limitation in a query by using the TOP clause with an optional START AT. Snowflake does not support this syntax but it can be transformed as shown below to achieve the same functionality.

入力コード:

Sybase
 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;

出力コード:

Snowflake
 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句

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

入力コード:

Sybase
 SELECT
* INTO mynewtable
FROM TABLE1;

SELECT
* INTO LOCAL TEMPORARY TABLE mynewtable
FROM TABLE1;

SELECT
* INTO #mynewtable
FROM TABLE1;

出力コード:

Snowflake
 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には、クエリ最適化のためのインデックスは含まれていません。

入力コード:

Sybase
 SELECT * FROM MyTable FORCE INDEX (MyIndex);

出力コード:

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

TABLE に FUNCTIONS

Snowflakeでは、ストアドプロシージャ（プロシージャが特定の 制限 に一致する場合）やテーブル値関数を FROM 句で呼び出すことができますが、RESULTSETS とウィンドウはパラメーターとして使用できません。

入力コード:

Sybase
 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) );

出力コード:

Snowflake
 SELECT
*
FROM
TABLE(MyProcedure(
                  !!!RESOLVE EWI!!! /*** SSC-EWI-SY0004 - UNSUPPORTED SYNTAX TABLE FUNCTION CAN'T RECEIVE A QUERY AS PARAMETER ***/!!!TABLE (SELECT * FROM TABLE1)));

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


SELECT
*
FROM
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
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は OPENSTRING の機能をサポートしていません。

入力コード:

Sybase
 -- 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;

出力コード:

Snowflake
 -- 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 派生テーブル

Sybaseでは、実行中にDML派生テーブルで指定された DML ステートメントが最初に実行され、その DML の影響を受けた行が REFERENCING 句で記述された列を持つ仮テーブルに実体化されます。仮テーブルはDML派生テーブルの結果セットを表します。Snowflakeはこの動作をサポートしていません。

入力コード:

Sybase
 -- 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);

出力コード:

Snowflake
 -- 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 is removed; otherwise, an EWI is inserted.

入力コード:

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

出力コード:

Snowflake
 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は、CROSS APPLY 句を LEFT OUTER JOIN 句に、OUTER APPLY 句を INNER JOIN 句に変換します。

入力コード:

Sybase
 -- 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;

出力コード:

Snowflake
 -- 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 句

Sybaseでは、テーブル名に続けて CONTAINS 句を使用すると、テーブルがフィルタリングされ、制約クエリで指定した全文クエリに一致する行のみが返されます。テーブルのマッチするすべての行が、スコア相関名を使って参照できるスコア列とともに返されます。Snowflakeはこの動作をサポートしていません。

入力コード:

Sybase
 -- 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;

出力コード:

Snowflake
 -- 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;