SnowConvert AI – Sybase IQ – SELECT

Beschreibung

Ruft Informationen aus der Datenbank ab. (Sybase SQL-Sprachreferenz)

Warnung

Diese Syntax wird in Snowflake teilweise unterstützt.

Grammatikalische 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>

Beispielhafte Quellcode-Muster

Zeilenbeschränkung

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.

Eingabecode:

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;

Ausgabecode:

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-Klausel

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.

Eingabecode:

Sybase
 SELECT
* INTO mynewtable
FROM TABLE1;

SELECT
* INTO LOCAL TEMPORARY TABLE mynewtable
FROM TABLE1;

SELECT
* INTO #mynewtable
FROM TABLE1;

Ausgabecode:

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;

Index erzwingen

Snowflake enthält keine Indizes zur Optimierung von Abfragen.

Eingabecode:

Sybase
 SELECT * FROM MyTable FORCE INDEX (MyIndex);

Ausgabecode:

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

TABLE-FUNCTIONS

Snowflake erlaubt das Aufrufen einer gespeicherten Prozedur (wenn die Prozedur bestimmten Beschränkungen unterliegt) oder eine Tabellenwertfunktion in einer FROM-Klausel, RESULTSETS und Windowing können jedoch nicht als Parameter verwendet werden.

Eingabecode:

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

Ausgabecode:

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 unterstützt die Funktion OPENSTRING nicht.

Eingabecode:

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;

Ausgabecode:

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;

Abgeleitete DML-Tabelle

In Sybase wird während der Ausführung die DML-Anweisung, die in der DML-abgeleiteten Tabelle angegeben ist, zuerst ausgeführt. Die von dieser DML betroffenen Zeilen materialisieren sich in einer temporären Tabelle, deren Spalten durch die REFERENCING-Klausel beschrieben werden. Die temporäre Tabelle stellt das Resultset von dml-derived-table dar. Snowflake unterstützt dieses Verhalten nicht.

Eingabecode:

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

Ausgabecode:

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.

Eingabecode:

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

Ausgabecode:

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 transformiert die CROSS APPLY-Klausel in LEFT OUTER JOIN und OUTER APPLY in INNER JOIN.

Eingabecode:

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;

Ausgabecode:

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-Klausel

In Sybase folgt die CONTAINS-Klausel auf einen Tabellennamen, um die Tabelle zu filtern und nur jene Zeilen zurückzugeben, die der mit contains-query angegebenen Volltextabfrage entsprechen. Jede übereinstimmende Zeile der Tabelle wird zusammen mit einer Punktzahlenspalte zurückgegeben, auf die mit dieser Methode verwiesen werden kann. Snowflake unterstützt dieses Verhalten nicht.

Eingabecode:

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;

Ausgabecode:

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;