SnowConvert: Instruções Select do Oracle

Seleção

Nota

Algumas partes dos códigos de saída foram omitidas por motivos de clareza.

Conversão geral de Select

Select simples

Código de entrada:
 select * from table1;
select col1 from schema1.table1;
Copy
Código de saída:
 select * from
table1;

select col1 from
schema1.table1;
Copy

Cláusula Where

Código de entrada:
 select col1 from schema1.table1 WHERE col1 = 1 and id > 0 or id < 1;
Copy
Código de saída:
 select col1 from
schema1.table1
WHERE col1 = 1 and id > 0 or id < 1;
Copy

Cláusula Order by

Código de entrada:
 select col1 from schema1.table1 order by id ASC;
Copy
Código de saída:
 select col1 from
schema1.table1
order by id ASC;
Copy

Group by

Código de entrada:
 select col1 from schema1.table1 GROUP BY id;
Copy
Código de saída:
 select col1 from
schema1.table1
GROUP BY id;
Copy

Cláusula Model

A cláusula Model ainda não é compatível.

Cláusula Row Limiting

Código de entrada:
 -- Using ONLY
select * from TableFetch1 FETCH FIRST 2 ROWS ONLY;
select * from TableFetch1 FETCH FIRST 20 percent ROWS ONLY;
select * from TableFetch1 order by col1 FETCH FIRST 2 ROWS with ties;
select * from TableFetch1 order by col1 FETCH FIRST 20 percent ROWS with ties;

-- Using OFFSET clause
select * from TableFetch1 offset 2 rows FETCH FIRST 2 ROWS ONLY;
select * from TableFetch1 offset 2 rows FETCH FIRST 60 percent rows ONLY;
select * from TableFetch1 
order by col1 offset 2 rows FETCH NEXT 2 ROWs with ties;
select * from TableFetch1 
order by col1 offset 2 rows FETCH FIRST 60 percent ROWs with ties;

-- Using WITH TIES clause
select * from TableFetch1 FETCH FIRST 2 ROWS with ties;
select * from TableFetch1 FETCH FIRST 20 percent ROWS with ties;
select * from TableFetch1 offset 2 rows FETCH NEXT 2 ROWs with ties;
select * from TableFetch1 offset 2 rows FETCH FIRST 60 percent ROWs with ties;

-- Using ORDER BY clause
select * from TableFetch1 order by col1 FETCH FIRST 2 ROWS ONLY;
select * from TableFetch1 order by col1 FETCH FIRST 20 percent ROWS ONLY;
select * from TableFetch1 order by col1 offset 2 rows FETCH FIRST 2 ROWS ONLY;
select * from TableFetch1 
order by col1 offset 2 rows FETCH FIRST 60 percent ROWS ONLY;

select * from TableFetch1 FETCH FIRST ROWS ONLY;

select * from TableFetch1 offset 2 rows;
Copy
Código de saída:
 -- Using ONLY
select * from
TableFetch1
FETCH FIRST 2 ROWS ONLY;

select * from
TableFetch1
QUALIFY
(ROW_NUMBER() OVER (
ORDER BY
NULL) - 1) / COUNT(*) OVER () < 20 / 100;

select * from
TableFetch1
QUALIFY
RANK() OVER (
order by col1) <= 2;

select * from
TableFetch1
QUALIFY
(RANK() OVER (
order by col1) - 1) / COUNT(*) OVER () < 20 / 100;

-- Using OFFSET clause
select * from
TableFetch1
offset 2 rows FETCH FIRST 2 ROWS ONLY;

select * from
TableFetch1
QUALIFY
(ROW_NUMBER() OVER (
ORDER BY
NULL) - 1 - 2) / COUNT(*) OVER () < 60 / 100
LIMIT NULL OFFSET 2;

select * from
TableFetch1
QUALIFY
RANK() OVER (
order by col1) - 2 <= 2
LIMIT NULL OFFSET 2;

select * from
TableFetch1
QUALIFY
(RANK() OVER (
order by col1) - 1 - 2) / COUNT(*) OVER () < 60 / 100
LIMIT NULL OFFSET 2;

-- Using WITH TIES clause
select * from
TableFetch1
FETCH FIRST 2 ROWS ONLY;

select * from
TableFetch1
QUALIFY
(ROW_NUMBER() OVER (
ORDER BY
NULL) - 1) / COUNT(*) OVER () < 20 / 100;

select * from
TableFetch1
offset 2 rows FETCH NEXT 2 ROWS ONLY;

select * from
TableFetch1
QUALIFY
(ROW_NUMBER() OVER (
ORDER BY
NULL) - 1 - 2) / COUNT(*) OVER () < 60 / 100
LIMIT NULL OFFSET 2;

-- Using ORDER BY clause
select * from
TableFetch1
order by col1
FETCH FIRST 2 ROWS ONLY;

select * from
TableFetch1
QUALIFY
(ROW_NUMBER() OVER (
order by col1) - 1) / COUNT(*) OVER () < 20 / 100;

select * from
TableFetch1
order by col1 offset 2 rows FETCH FIRST 2 ROWS ONLY;

select * from
TableFetch1
QUALIFY
(ROW_NUMBER() OVER (
order by col1) - 1 - 2) / COUNT(*) OVER () < 60 / 100
LIMIT NULL OFFSET 2;

select * from
TableFetch1
FETCH FIRST 1 ROWS ONLY;

select * from
TableFetch1
LIMIT NULL OFFSET 2;
Copy

Nota

No Oracle, FETCH / OFFSET WITH TIES é ignorado quando nenhum ORDER BY é especificado em SELECT. Esse caso será transformado em um FETCH / OFFSET com a palavra-chave ONLY no Snowflake. Observe que no Snowflake a palavra-chave ONLY não tem efeito nos resultados e é usada apenas para facilitar a leitura.

Pivô

O Snowflake não é compatível com as seguintes instruções:\ - Rename columns \ - Multiple Columns

Código de entrada:

 select * from schema1.table1
PIVOT(count(*) as count1 FOR (column1, column2) IN (row1 as rowName));
Copy
Código de saída:
 select * from
schema1.table1
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT RENAME COLUMN NOT SUPPORTED ***/!!!
PIVOT (count(*)
                !!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT MULTIPLE COLUMN NOT SUPPORTED ***/!!!
                FOR (column1, column2)
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT RENAME COLUMN NOT SUPPORTED ***/!!!
IN (row1 as rowName));
Copy

Unpivot

O Snowflake não é compatível com as seguintes instruções:\ - INCLUDE / EXCLUDE NULLS

Código de entrada:

select * from schema1.table1 
UNPIVOT INCLUDE NULLS (column1 FOR column2 IN (ANY, ANY)); 
Copy
Código de saída:
 select * from
schema1.table1
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT INCLUDE NULLS NOT SUPPORTED ***/!!!
UNPIVOT ( column1 FOR column2 IN (
ANY,
ANY));
Copy

Transformação de JOIN (+) para sintaxe ANSI

Essa conversão está desativada no momento e serve apenas como referência para conversões feitas com versões anteriores do SnowConvert. Para obter a conversão atual, consulte a seção acima.

O SnowConvert converte a sintaxe de junção externa especial (+) de NON-ANSI para a sintaxe de junção externa de ANSI. Esta subseção mostra alguns exemplos:

Para LEFTOUTERJOIN

Exemplo 1:
Input Code:
 -- Additional Params: --OuterJoinsToOnlyAnsiSyntax
SELECT d.department_name,
       e.employee_name      
FROM   departments d, employees e
WHERE  d.department_id = e.department_id (+) 
AND    d.department_id >= 30;
Copy
Output Code:
 SELECT d.department_name,
       e.employee_name
FROM
       departments d
       LEFT OUTER JOIN
              employees e
              ON d.department_id = e.department_id
WHERE
       d.department_id >= 30;
Copy
Exemplo 2:
Input Code:
 -- Additional Params: --OuterJoinsToOnlyAnsiSyntax
SELECT d.department_name,
       e.employee_name      
FROM   departments d, employees e
WHERE  d.department_id(+)  = e.department_id 
AND    d.department_id >= 30;
Copy
Output Code:
 SELECT d.department_name,
       e.employee_name
FROM
       employees e
       LEFT OUTER JOIN
              departments d
              ON d.department_id = e.department_id
WHERE
       d.department_id >= 30;
Copy

Exemplo 3: Junção múltipla

Input Code:
 -- Additional Params: --OuterJoinsToOnlyAnsiSyntax
SELECT d.department_name,
       e.employee_name      
FROM   departments d, employees e, projects p
WHERE  e.department_id(+) = d.department_id
AND    p.department_id(+) = d.department_id
AND    d.department_id >= 30;
Copy
Output Code:
 SELECT d.department_name,
       e.employee_name
FROM
       departments d
       LEFT OUTER JOIN
              employees e
              ON e.department_id = d.department_id
       LEFT OUTER JOIN
              projects p
              ON p.department_id = d.department_id
WHERE
       d.department_id >= 30;
Copy

Exemplo 4: Junção com outros tipos de condicionais

Input Code:
 -- Additional Params: --OuterJoinsToOnlyAnsiSyntax
SELECT d.department_name,
       e.employee_name      
FROM   departments d, employees e
WHERE  d.department_id(+)  = e.department_id 
AND    d.location(+) IN ('CHICAGO', 'BOSTON', 'NEW YORK')
AND    d.department_id >= 30;
Copy
Output Code:
 SELECT d.department_name,
       e.employee_name
FROM
       employees e
       LEFT OUTER JOIN
              departments d
              ON d.department_id = e.department_id
              AND d.location IN ('CHICAGO', 'BOSTON', 'NEW YORK')
WHERE
       d.department_id >= 30;
Copy

Exemplo 5: Junção com (+) dentro de uma função

Input Code:
 -- Additional Params: --OuterJoinsToOnlyAnsiSyntax
SELECT d.department_name,
       e.employee_name
FROM   departments d, employees e
WHERE SUBSTR(d.department_name, 1, NVL(e.department_id, 1) ) = e.employee_name(+);
Copy
Output Code:
 SELECT d.department_name,
       e.employee_name
FROM
       departments d
       LEFT OUTER JOIN
              employees e
              ON SUBSTR(d.department_name, 1, NVL(e.department_id, 1) ) = e.employee_name;
Copy

Aviso

Observe que alguns dos padrões que foram convertidos para LEFT OUTER JOIN podem recuperar as linhas em uma ordem diferente.

Para CROSSJOIN

Exemplo 6: Caso complexo que requer o uso de CROSS JOIN

Código de entrada:
-- Additional Params: --OuterJoinsToOnlyAnsiSyntax
SELECT d.department_name,
       e.employee_name,
       p.project_name,
       c.course_name 
FROM   departments d, employees e, projects p, courses c
WHERE
e.salary (+) >= 2000 AND
d.department_id = e.department_id (+)
AND p.department_id = e.department_id(+)
AND c.course_id  = e.department_id(+)
AND d.department_id >= 30;
Copy
Código de saída:
 SELECT d.department_name,
       e.employee_name,
       p.project_name,
       c.course_name
FROM
       departments d
       CROSS JOIN projects p
       CROSS JOIN courses c
       LEFT OUTER JOIN
              employees e
              ON
              e.salary >= 2000
              AND
              d.department_id = e.department_id
              AND p.department_id = e.department_id
              AND c.course_id  = e.department_id
WHERE
       d.department_id >= 30;
Copy

Consultas hierárquicas

As consultas hierárquicas no Snowflake permitem organizar e recuperar dados em uma estrutura semelhante a uma árvore, normalmente usando a cláusula CONNECT BY. Essa cláusula une uma tabela a ela mesma para processar dados hierárquicos na tabela.

Código de entrada:

 SELECT employee_ID, manager_ID, title
FROM employees
START WITH manager_ID = 1
CONNECT BY manager_ID = PRIOR employee_id;
Copy
Código de saída:
 SELECT employee_ID, manager_ID, title
FROM
employees
START WITH manager_ID = 1
CONNECT BY
manager_ID = PRIOR employee_id;
Copy

Consulta Select Flashback

select-flashback-query.md

Consulta Select Flashback

Descrição

Oracle

A cláusula de Flashback Query no Oracle recupera dados anteriores de uma tabela, visualização ou exibição materializada. No Oracle, os usos podem incluir:

  • Restaurar dados excluídos ou desfazer um commit incorreto, comparar os dados atuais com os dados correspondentes em um momento anterior, verificar o estado dos dados transacionais em um determinado momento e gerar ferramentas de geração de relatórios para dados passados, entre outros. (Documentação de consulta Flashback Query do Oracle).

Snowflake

O mecanismo equivalente no Snowflake para consultar dados do passado é a consulta AT | BEGIN. Observe que o único equivalente é para as instruções AS OF.

Além disso, o Snowflake tem uma documentação completa de «Time Travel» que permite consultar dados para clonar objetos, como tabelas, exibições e esquemas. Há limitações quanto aos dias de acesso aos dados passados ou excluídos (90 dias antes de passar para o status Fail-safe). Para obter mais informações, consulte a Documentação de Time Travel do Snowflake.

Sintaxe do Oracle

 { VERSIONS BETWEEN
  { SCN | TIMESTAMP }
  { expr | MINVALUE } AND { expr | MAXVALUE }
| AS OF { SCN | TIMESTAMP } expr
}
Copy

Sintaxe do Snowflake

 SELECT ...
FROM ...
  {
   AT( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> | STREAM => '<name>' } ) |
   BEFORE( STATEMENT => <id> )
  }
[ ... ]
Copy

Nota

Observe que a ID da consulta deve fazer referência a uma consulta executada dentro de 14 dias. Se a ID fizer referência a uma consulta com mais de 14 dias, o seguinte erro será retornado: Erro: statement <query_id> not found. Para contornar essa limitação, use o registro de data e hora da consulta referenciada. (Snowflake AT | Antes da documentação)

Amostra de padrões da origem

Os dados a seguir são usados nos exemplos a seguir para gerar os resultados da consulta.

 CREATE TABLE Employee (
    EmployeeID NUMBER PRIMARY KEY,
    FirstName VARCHAR2(50),
    LastName VARCHAR2(50),
    EmailAddress VARCHAR2(100),
    HireDate DATE,
    SalaryAmount NUMBER(10, 2)
);

INSERT INTO Employee VALUES (1, 'Bob', 'SampleNameA', 'sample@example.com', TO_DATE('2023-01-15', 'YYYY-MM-DD'), 11111.00);
INSERT INTO Employee VALUES (2, 'Bob', 'SampleNameB', 'sample@example.com', TO_DATE('2023-01-15', 'YYYY-MM-DD'), 11111.00);
INSERT INTO Employee VALUES (3, 'Bob', 'SampleNameC', 'sample@example.com', TO_DATE('2022-03-10', 'YYYY-MM-DD'), 11111.00);
INSERT INTO Employee VALUES (4, 'Bob', 'SampleNameD', 'sample@example.com', TO_DATE('2022-03-10', 'YYYY-MM-DD'), 11111.00);
INSERT INTO Employee VALUES (5, 'Bob', 'SampleNameE', 'sample@example.com', TO_DATE('2022-03-10', 'YYYY-MM-DD'), 11111.00);
Copy
 CREATE OR REPLACE TABLE Employee (
       EmployeeID NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/ PRIMARY KEY,
       FirstName VARCHAR(50),
       LastName VARCHAR(50),
       EmailAddress VARCHAR(100),
       HireDate TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
       SalaryAmount NUMBER(10, 2) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
   )
   COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
   ;

   INSERT INTO Employee
   VALUES (1, 'Bob', 'SampleNameA', 'sample@example.com', TO_DATE('2023-01-15', 'YYYY-MM-DD'), 11111.00);

   INSERT INTO Employee
   VALUES (2, 'Bob', 'SampleNameB', 'sample@example.com', TO_DATE('2023-01-15', 'YYYY-MM-DD'), 11111.00);

   INSERT INTO Employee
   VALUES (3, 'Bob', 'SampleNameC', 'sample@example.com', TO_DATE('2022-03-10', 'YYYY-MM-DD'), 11111.00);

   INSERT INTO Employee
   VALUES (4, 'Bob', 'SampleNameD', 'sample@example.com', TO_DATE('2022-03-10', 'YYYY-MM-DD'), 11111.00);

   INSERT INTO Employee
   VALUES (5, 'Bob', 'SampleNameE', 'sample@example.com', TO_DATE('2022-03-10', 'YYYY-MM-DD'), 11111.00);
Copy

1. AS OF with TIMESTAMP case

Oracle

 SELECT * FROM employees
AS OF TIMESTAMP
TO_TIMESTAMP('2023-09-27 07:00:00', 'YYYY-MM-DD HH:MI:SS')
WHERE last_name = 'SampleName';
Copy

Snowflake

 SELECT * FROM
employees
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0135 - DATA RETENTION PERIOD MAY PRODUCE NO RESULTS ***/!!!
AT (TIMESTAMP =>
TO_TIMESTAMP('2023-09-27 07:00:00', 'YYYY-MM-DD HH:MI:SS'))
WHERE last_name = 'SampleName';
Copy

2. AS OF with SCN case

Oracle

 SELECT * FROM employees
AS OF SCN
TO_TIMESTAMP('2023-09-27 07:00:00', 'YYYY-MM-DD HH:MI:SS')
WHERE last_name = 'SampleName';
Copy

Snowflake

 SELECT * FROM
employees
!!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
AS OF SCN
TO_TIMESTAMP('2023-09-27 07:00:00', 'YYYY-MM-DD HH:MI:SS')
WHERE last_name = 'SampleName';
Copy

Problemas conhecidos

1. The option when it is using SCN is not supported.

2. The VERSION statement is not supported in Snowflake.

EWIS relacionados

  1. SSC-EWI-0040: Instrução não suportada.

  2. SSC-EWI-OR0135: A cláusula Current of não é compatível com o Snowflake.

  3. SSC-FDM-0006: A coluna de tipo de número pode não se comportar de forma semelhante no Snowflake.

  4. SSC-FDM-OR0042: O tipo de data transformado em carimbo de data/hora tem um comportamento diferente.