SnowConvert AI – Sybase IQ – SELECT¶
Descrição¶
Recupera informações do banco de dados. (Referência da linguagem Sybase SQL)
Aviso
Essa sintaxe é parcialmente compatível com o Snowflake.
Sintaxe da gramática¶
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>
Amostra de padrões da origem¶
Limitação de linhas¶
O Sybase permite a limitação de linhas em uma consulta usando a cláusula TOP com START AT opcional, com a qual o Snowflake não é compatível, mas que pode ser transformada do jeito mostrado abaixo para obter a mesma funcionalidade.
Código de entrada:¶
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;
Código de saída:¶
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;
Cláusula INTO¶
No Sybase, uma tabela pode ser definida selecionando várias linhas e definindo um nome para armazenar os dados recuperados. O Snowflake não é compatível com esse comportamento, mas isso pode ser emulado com o uso de CREATE TABLE AS.
Código de entrada:¶
Sybase¶
SELECT
* INTO mynewtable
FROM TABLE1;
SELECT
* INTO LOCAL TEMPORARY TABLE mynewtable
FROM TABLE1;
SELECT
* INTO #mynewtable
FROM TABLE1;
Código de saída:¶
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;
Índice Force¶
O Snowflake não contém índices para otimização de consultas.
Código de entrada:¶
Sybase¶
SELECT * FROM MyTable FORCE INDEX (MyIndex);
Código de saída:¶
Snowflake¶
SELECT
*
FROM
MyTable
-- --** SSC-FDM-SY0002 - FORCE INDEX IS NOT SUPPORTED IN SNOWFLAKE **
-- FORCE INDEX (MyIndex)
;
TABLE FUNCTIONS¶
O Snowflake permite chamar um procedimento armazenado (quando o procedimento atende a certas limitações) ou uma função de valor de tabela em uma cláusula FROM, mas RESULTSETS e janelas não podem ser utilizadas como parâmetros.
Código de entrada:¶
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) );
Código de saída:¶
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¶
O Snowflake não é compatível com a funcionalidade OPENSTRING.
Código de entrada:¶
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;
Código de saída:¶
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;
Tabela derivada do DML¶
No Sybase, durante a execução, a instrução DML especificada na tabela derivada do DML é executada primeiro, e as linhas afetadas por essa DML são materializadas em uma tabela temporária cujas colunas são descritas pela cláusula REFERENCING. A tabela temporária representa o conjunto de resultados da tabela derivada do DML. O Snowflake é incompatível com esse comportamento.
Código de entrada:¶
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);
Código de saída:¶
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¶
O Snowflake não é compatível com junção KEY, mas quando ON CLAUSE é definido na consulta, a palavra-chave KEY é removida; caso contrário, um EWI é inserido.
Código de entrada:¶
Sybase¶
SELECT * FROM Table1 KEY JOIN Table2;
SELECT * FROM Table1 KEY JOIN Table2 ON Table1.ID = Table2.ID;
Código de saída:¶
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¶
O Snowflake transforma a cláusula CROSS APPLY em LEFT OUTER JOIN e OUTER APPLY em INNER JOIN.
Código de entrada:¶
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;
Código de saída:¶
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;
Cláusula CONTAINS¶
No Sybase, a cláusula CONTAINS que segue o nome de uma tabela filtra a tabela e retorna apenas as linhas que correspondem à consulta de texto completo especificada com contains-query. Cada linha correspondente da tabela é retornada juntamente com uma coluna de pontuação que pode ser referenciada usando score-correlation-name. O Snowflake é incompatível com esse comportamento.
Código de entrada:¶
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;
Código de saída:¶
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;