SnowConvert AI - Sybase IQ - SELECT¶

Description¶

Retrieves information from the database. (Sybase SQL Language Reference (https://help.sap.com/docs/SAP_IQ/a899599784f21015a466ed42e24d07f9/a624e72e84f210159276a39335acd358.html?version=16.0.11&locale=en-US))

Warning

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

Input Code:¶

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;

Output Code:¶

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;

Window-function ORDER BY NULL Ordering¶

Sybase treats NULL as the smallest value, so ORDER BY x ASC sorts NULLs first and ORDER BY x DESC sorts NULLs last. Snowflake’s default treats NULL as the largest value, which silently inverts the position of NULLs and changes window-function ranks/values for any partition that contains NULLs in the order key.

To preserve Sybase semantics, SnowConvert AI emits an explicit NULLS FIRST or NULLS LAST clause on every ORDER BY item inside an OVER (...) window-function clause that does not already carry one: ASC (or no direction) becomes NULLS FIRST; DESC becomes NULLS LAST. The rewrite is idempotent — items that already specify a NULLS … clause are preserved verbatim, including caller overrides such as ORDER BY x ASC NULLS LAST.

Statement-level ORDER BY (outside OVER) and WITHIN GROUP (ORDER BY ...) carry the same divergence but are intentionally not rewritten; add an explicit NULLS … clause manually or set the session parameter DEFAULT_NULL_ORDERING if the result depends on NULL position.

Input Code:¶

Sybase¶

 SELECT
  Name,
  DENSE_RANK() OVER (PARTITION BY Region ORDER BY SalesTotal DESC) AS RankInRegion,
  ROW_NUMBER() OVER (ORDER BY HireDate) AS HireOrder
FROM Employees;

Output Code:¶

Snowflake¶

 SELECT
  Name,
  DENSE_RANK() OVER (PARTITION BY Region ORDER BY SalesTotal DESC NULLS LAST) AS RankInRegion,
  ROW_NUMBER() OVER (ORDER BY HireDate NULLS FIRST) AS HireOrder
FROM Employees;

Non-Standard Comparison Operators (`!>`, `!<`)¶

Sybase supports two non-standard comparison operators that Snowflake does not accept:

Sybase	Meaning	Snowflake equivalent
`!>`	not greater than	`<=`
`!<`	not less than	`>=`

Leaving these operators untranslated produces invalid Snowflake SQL that fails at compile time (SQL compilation error: unexpected '>'). SnowConvert AI rewrites them to their standard equivalents. The translation is exact for every operand combination — including NULLs — because NULL !> x and NULL <= x both evaluate to UNKNOWN, matching Sybase semantics. Both the compact form (!>, !<) and the legacy spaced form (! >, ! <) are rewritten; user-written IS NOT NULL guards or any other surrounding predicates are preserved verbatim.

Input Code:¶

Sybase¶

 SELECT *
FROM Orders
WHERE OrderQty IS NOT NULL
  AND MinQty IS NOT NULL
  AND OrderQty !> MinQty
  AND Discount !< 0;

Output Code:¶

Snowflake¶

 SELECT *
FROM
  Orders
WHERE
  OrderQty IS NOT NULL
  AND MinQty IS NOT NULL
  AND OrderQty <= MinQty
  AND Discount >= 0;

Into Clause¶

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.

Input Code:¶

Sybase¶

 SELECT
* INTO mynewtable
FROM TABLE1;

SELECT
* INTO LOCAL TEMPORARY TABLE mynewtable
FROM TABLE1;

SELECT
* INTO #mynewtable
FROM TABLE1;

Output Code:¶

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 does not contain indexes for query optimization.

Input Code:¶

Sybase¶

 SELECT * FROM MyTable FORCE INDEX (MyIndex);

Output Code:¶

Snowflake¶

 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¶

 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¶

 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 does not support OPENSTRING (https://help.sap.com/docs/SAP_IQ/a898e08b84f21015969fa437e89860c8/a7749cf084f21015b73b899c1520fb06.html#parameters) functionality.

Input Code:¶

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;

Output Code:¶

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

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

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

Input Code:¶

Sybase¶

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

Output Code:¶

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 transforms the clause the CROSS APPLY into LEFT OUTER JOIN and OUTER APPLY to INNER JOIN.

Input Code:¶

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;

Output Code:¶

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 Clause¶

In Sybase the CONTAINS (https://help.sap.com/docs/SAP_IQ/a898e08b84f21015969fa437e89860c8/a7749cf084f21015b73b899c1520fb06.html) 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¶

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

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

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

SnowConvert AI - Sybase IQ - SELECT¶

Description¶

Grammar Syntax¶

Sample Source Patterns¶

Row Limitation¶

Input Code:¶

Sybase¶

Output Code:¶

Snowflake¶

Window-function ORDER BY NULL Ordering¶

Input Code:¶

Sybase¶

Output Code:¶

Snowflake¶

Non-Standard Comparison Operators (!>, !<)¶

Input Code:¶

Sybase¶

Output Code:¶

Snowflake¶

Into Clause¶

Input Code:¶

Sybase¶

Output Code:¶

Snowflake¶

Force Index¶

Input Code:¶

Sybase¶

Output Code:¶

Snowflake¶

TABLE FUNCTIONS¶

Input Code:¶

Sybase¶

Output Code:¶

Snowflake¶

OPEN STRING¶

Input Code:¶

Sybase¶

Output Code:¶

Snowflake¶

DML Derived Table¶

Input Code:¶

Sybase¶

Output Code:¶

Snowflake¶

KEY JOIN¶

Input Code:¶

Sybase¶

Output Code:¶

Snowflake¶

OUTER-CROSS APPLY¶

Input Code:¶

Sybase¶

Output Code:¶

Snowflake¶

CONTAINS Clause¶

Input Code:¶

Sybase¶

Output Code:¶

Snowflake¶

Related EWIs¶

Non-Standard Comparison Operators (`!>`, `!<`)¶