UDFs Personalizados do SnowConvert

Descrição

Algumas funções e funcionalidades incorporadas do Oracle podem não estar disponíveis ou podem se comportar de forma diferente no Snowflake. Para minimizar essas diferenças, algumas funções são substituídas por UDFs personalizados do SnowConvert.

Esses UDFs são criados automaticamente durante a migração, na pasta UDF Helper, dentro da pasta Output. Há um arquivo por UDF personalizado.

BFILENAME UDF

Descrição

Essa função usa o nome do diretório e os parâmetros de nome de arquivo do Oracle BFILENAME() como STRING e retorna uma concatenação deles usando \. Como BFILE é convertido para VARCHAR, o resultado de BFILENAME é tratado como texto.

Aviso

O \ deve ser alterado para corresponder ao caractere de concatenação de arquivo do sistema operacional correspondente.

Sobrecargas personalizadas de UDF

BFILENAME_UDF(string, string)

Concatena o caminho do diretório e o nome do arquivo.

Parâmetros

  1. DIRECTORYNAME: Um STRING que representa o caminho do diretório.

  2. FILENAME: Um STRING que representa o nome do arquivo.

      -- UDF
      CREATE OR REPLACE FUNCTION PUBLIC.BFILENAME_UDF (DIRECTORYNAME STRING, FILENAME STRING)
      RETURNS STRING
      LANGUAGE SQL
      IMMUTABLE 
      COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
      AS
      $$
      DIRECTORYNAME || '\\' || FILENAME
      $$;
Copy
Oracle
     --Create Table
     CREATE TABLE bfile_table ( col1 BFILE );
 
     --Insert Bfilename
     INSERT INTO bfile_table VALUES ( BFILENAME('mydirectory', 'myfile.png') );

     --Select
     SELECT * FROM bfile_table;
Copy
-- Result
COL1              |
------------------+
[BFILE:myfile.png]|
Copy
Snowflake
--Create Table
CREATE OR REPLACE TABLE bfile_table ( col1
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0105 - ADDITIONAL WORK IS NEEDED FOR BFILE COLUMN USAGE. BUILD_STAGE_FILE_URL FUNCTION IS A RECOMMENDED WORKAROUND ***/!!!
VARCHAR
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

--Insert Bfilename
INSERT INTO bfile_table
VALUES (PUBLIC.BFILENAME_UDF('mydirectory', 'myfile.png') );

--Select
SELECT * FROM
bfile_table;
Copy
-- Result
COL1                  |
----------------------+
mydirectory\myfile.png|
Copy

Problemas conhecidos

1. No access to the DBMS_LOB built-in package

Como os tipos de dados LOB não são compatíveis com o Snowflake, não há um equivalente para as funções DBMS_LOB e ainda não há soluções alternativas implementadas.

CAST_DATE UDF

Nota

Algumas partes do código de saída foram omitidas por motivos de clareza.

Descrição

Esse UDF personalizado foi adicionado para evitar exceções de tempo de execução causadas por diferenças de formato ao converter strings para DATE, dentro de procedimentos e funções.

Sobrecargas personalizadas de UDF

CAST_DATE_UDF(datestr)

Ele cria um DATE a partir de um STRING.

Parâmetros

  1. DATESTR: Um STRING que representa um DATE com um formato específico.

 CREATE OR REPLACE FUNCTION PUBLIC.CAST_DATE_UDF(DATESTR STRING) 
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
	SELECT TO_DATE(DATESTR,'YYYY-MM-DD"T"HH24:MI:SS.FF')
$$;
Copy
Oracle
 :force: 

 --Create Table
CREATE TABLE jsdateudf_table( col1 DATE );

--Create Procedure
CREATE OR REPLACE PROCEDURE jsdateudf_proc ( par1 DATE )
IS
BEGIN
    INSERT INTO jsdateudf_table VALUES(par1);
END;

--Insert Date
CALL jsdateudf_proc('20-03-1996');

--Select
SELECT * FROM jsdateudf_table;
Copy
COL1                   |
-----------------------+
1996-03-20 00:00:00.000|
Copy
Snowflake
 --Create Table
CREATE OR REPLACE TABLE jsdateudf_table ( col1 TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;

--Create Procedure
CREATE OR REPLACE PROCEDURE jsdateudf_proc (par1 TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/)
RETURNS VARCHAR
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
EXECUTE AS CALLER
AS
$$
    BEGIN
        INSERT INTO jsdateudf_table
        VALUES(:par1);
    END;
$$;

--Insert Date
CALL jsdateudf_proc('20-03-1996');

--Select
SELECT * FROM
    jsdateudf_table;
Copy
COL1      |
----------+
1996-03-20|
Copy

Problemas conhecidos

1. Oracle DATE contains TIMESTAMP

Leve em consideração que o Oracle DATE contém um TIMESTAMP vazio (00:00:00.000), enquanto o Snowflake DATE não contém. O SnowConvert permite transformar DATE em TIMESTAMP com o sinalizador SysdateAsCurrentTimestamp.

EWIs Relacionados

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

DATE_TO_JULIANDAYS_UDF

Descrição

A função DATE_TO_JULIANDAYS_UDF() recebe um DATE e retorna o número de dias desde 1º de janeiro de 4712 BC. Essa função é equivalente a Oracle TO_CHAR(DATE, “J”)

Sobrecargas personalizadas de UDF

DATE_TO_JULIANDAYS_UDF(data)

Parâmetros

  1. INPUT_DATE: O DATE da operação.

 CREATE OR REPLACE FUNCTION PUBLIC.DATE_TO_JULIAN_DAYS_UDF(input_date DATE)
RETURNS NUMBER  
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
    DATEDIFF(DAY,TO_DATE('00000101','YYYYMMDD'),TO_DATE('01/01/4712','DD/MM/YYYY')) +
    DATEDIFF(DAY,TO_DATE('00000101','YYYYMMDD'),input_date) + 38 
    // Note: The 38 on the equation marks the differences in days between calendars and must be updated on the year 2099
$$
;
Copy

Exemplo de uso

Oracle
 --Create Table
CREATE TABLE datetojulian_table (col1 DATE);

INSERT INTO datetojulian_table VALUES (DATE '2020-01-01');
INSERT INTO datetojulian_table VALUES (DATE '1900-12-31');
INSERT INTO datetojulian_table VALUES (DATE '1904-02-29');
INSERT INTO datetojulian_table VALUES (DATE '1903-03-01');
INSERT INTO datetojulian_table VALUES (DATE '2000-12-31');

--Select
SELECT TO_CHAR(col1, 'J') FROM datetojulian_table;
Copy
Snowflake
 --Create Table
CREATE OR REPLACE TABLE datetojulian_table (col1 TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;

INSERT INTO datetojulian_table
VALUES (DATE '2020-01-01');

INSERT INTO datetojulian_table
VALUES (DATE '1900-12-31');

INSERT INTO datetojulian_table
VALUES (DATE '1904-02-29');

INSERT INTO datetojulian_table
VALUES (DATE '1903-03-01');

INSERT INTO datetojulian_table
VALUES (DATE '2000-12-31');

--Select
SELECT
PUBLIC.DATE_TO_JULIAN_DAYS_UDF(col1)
FROM
datetojulian_table;
Copy

Problemas conhecidos

Não foram encontrados problemas.

EWIs relacionados

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

DATEADD UDF

Descrição

Este UDF é usado como modelo para todos os casos em que há uma adição entre um tipo DATE ou TIMESTAMP e um tipo FLOAT.

Sobrecargas personalizadas de UDF

DATEADD_UDF(date, float)

Parâmetros

  1. FIRST_PARAM: O primeiro DATE da operação.

  2. SECOND_PARAM: O FLOAT a ser adicionado.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEADD_UDF(FIRST_PARAM DATE, SECOND_PARAM FLOAT)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
SELECT FIRST_PARAM + SECOND_PARAM::NUMBER
$$;
Copy

DATEADD_UDF(float, date)

Parâmetros

  1. FIRST_PARAM: O FLOAT a ser adicionado.

  2. SECOND_PARAM: O DATE da operação.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEADD_UDF(FIRST_PARAM FLOAT, SECOND_PARAM DATE)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
SELECT FIRST_PARAM::NUMBER + SECOND_PARAM
$$;
Copy

DATEADD_UDF(timestamp, float)

Parâmetros

  1. FIRST_PARAM: O primeiro TIMESTAMP da operação.

  2. SECOND_PARAM: O FLOAT a ser adicionado.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEADD_UDF(FIRST_PARAM TIMESTAMP, SECOND_PARAM FLOAT)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
SELECT DATEADD(day, SECOND_PARAM,FIRST_PARAM)
$$;
Copy

DATEADD_UDF(float, timestamp)

Parâmetros

  1. FIRST_PARAM: OFLOAT da operação.

  2. SECOND_PARAM: OTIMESTAMP da operação.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEADD_UDF(FIRST_PARAM FLOAT, SECOND_PARAM TIMESTAMP)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
SELECT DATEADD(day, FIRST_PARAM,SECOND_PARAM)
$$;
Copy

Exemplo de uso

Oracle
 SELECT
    TO_TIMESTAMP('03/08/2009, 12:47 AM', 'dd/mm/yy, hh:mi AM')+62.40750856543442
FROM DUAL;
Copy
|TO_TIMESTAMP('03/08/2009,12:47AM','DD/MM/YY,HH:MIAM')+62.40750856543442|
|-----------------------------------------------------------------------|
|2009-10-04 10:33:49.000                                                |

Copy
Snowflake
 SELECT
    PUBLIC.DATEADD_UDF(TO_TIMESTAMP('03/08/2009, 12:47 AM', 'dd/mm/yy, hh:mi AM'), 62.40750856543442)
FROM DUAL;
Copy
|PUBLIC.DATEADD_UDF(
TO_TIMESTAMP('03/08/2009, 12:47 AM', 'DD/MM/YY, HH12:MI AM'), 62.40750856543442)|
|-----------------------------------------------------------------------------------------------------|
|2009-10-04 00:47:00.000                                                                              |

Copy

Problemas conhecidos

1. Differences in time precision

Quando há operações entre Dates ou Timestamps e Floats, a hora pode ser diferente da do Oracle. Há um item de ação para corrigir esse problema.

EWIs relacionados

Sem EWIs relacionados

DATEDIFF UDF

Nota

Algumas partes do código de saída foram omitidas por motivos de clareza.

Descrição

Esse UDF é usado como modelo para todos os casos em que há uma subtração entre um DATE, TIMESTAMP e qualquer outro tipo (exceto Intervalos).

Sobrecargas personalizadas de UDF

DATEDIFF_UDF(date, date)

Parâmetros

  1. FIRST_PARAM: O primeiro DATE da operação.

  2. SECOND_PARAM: O DATE a ser subtraído.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(FIRST_PARAM DATE, SECOND_PARAM DATE)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
	FIRST_PARAM - SECOND_PARAM
$$;
Copy

DATEDIFF_UDF(date, timestamp)

Parâmetros

  1. FIRST_PARAM: O primeiro DATE da operação.

  2. SECOND_PARAM: O TIMESTAMP a ser subtraído.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(FIRST_PARAM DATE, SECOND_PARAM TIMESTAMP)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
	FIRST_PARAM - SECOND_PARAM::DATE
$$;
Copy

DATEDIFF_UDF(date, integer)

Parâmetros

  1. FIRST_PARAM: O primeiro DATE da operação.

  2. SECOND_PARAM: O INTEGER a ser subtraído.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(FIRST_PARAM DATE, SECOND_PARAM INTEGER)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
	DATEADD(day,SECOND_PARAM*-1 ,FIRST_PARAM)
$$;
Copy

DATEDIFF_UDF(timestamp, timestamp)

Parâmetros

  1. FIRST_PARAM: O primeiro TIMESTAMP da operação.

  2. SECOND_PARAM: O TIMESTAMP a ser subtraído.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(FIRST_PARAM TIMESTAMP, SECOND_PARAM TIMESTAMP)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
	DATEDIFF(day,SECOND_PARAM ,FIRST_PARAM)
$$;
Copy

DATEDIFF_UDF(timestamp, date)

Parâmetros

  1. FIRST_PARAM: O primeiro TIMESTAMP da operação.

  2. SECOND_PARAM: O DATE a ser subtraído.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(FIRST_PARAM TIMESTAMP, SECOND_PARAM DATE)
RETURNS INTEGER
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
	DATEDIFF(day,SECOND_PARAM ,FIRST_PARAM)
$$;
Copy

DATEDIFF_UDF(timestamp, number)

Parâmetros

  1. FIRST_PARAM: O primeiro TIMESTAMP da operação.

  2. SECOND_PARAM: O NUMBER a ser subtraído.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(FIRST_PARAM TIMESTAMP, SECOND_PARAM NUMBER)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
	DATEADD(day,SECOND_PARAM*-1,FIRST_PARAM)
$$;
Copy

Exemplo de uso

Nota

O desconhecido é uma coluna cujo tipo não pôde ser resolvido, podendo ser um carimbo de data/hora, inteiro de data ou número.

Nota

--disableDateAsTimestamp

Sinalizador para indicar se SYSDATE deve ser transformado em CURRENT_DATE ou CURRENT_TIMESTAMP. Isso também afetará todas as colunas DATE que serão transformadas em TIMESTAMP.

Oracle
 --Create Table
CREATE TABLE times(AsTimeStamp TIMESTAMP, AsDate DATE);

--Subtraction operations
SELECT AsDate - unknown FROM times, unknown_table;
SELECT unknown - AsTimeStamp FROM times;
SELECT AsTimeStamp - unknown FROM times;
SELECT unknown - AsDate FROM times;
Copy
Snowflake
 --Create Table
CREATE OR REPLACE TABLE times (AsTimeStamp TIMESTAMP(6),
AsDate TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;

--Subtraction operations
SELECT
PUBLIC.DATEDIFF_UDF(
                     !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN DATE AND unknown ***/!!!
 AsDate, unknown) FROM
times,
unknown_table;

SELECT
PUBLIC.DATEDIFF_UDF(
                     !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Timestamp ***/!!!
 unknown, AsTimeStamp) FROM
times;

SELECT
PUBLIC.DATEDIFF_UDF(
                     !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN Timestamp AND unknown ***/!!!
 AsTimeStamp, unknown) FROM
times;

SELECT
PUBLIC.DATEDIFF_UDF(
                     !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND DATE ***/!!!
 unknown, AsDate) FROM
times;
Copy

Problemas conhecidos

1. Functional differences for timestamps

Às vezes, o valor do Snowflake retornado pelo UDF pode ser diferente do valor do Oracle devido ao horário. Considere o seguinte exemplo

Oracle
 -- CREATE TABLE UNKNOWN_TABLE(Unknown timestamp);
-- INSERT  INTO UNKNOWN_TABLE VALUES (TO_TIMESTAMP('01/10/09, 12:00 P.M.', 'dd/mm/yy, hh:mi P.M.'));

CREATE TABLE TIMES(AsTimeStamp TIMESTAMP);
INSERT INTO TIMES VALUES (TO_TIMESTAMP('05/11/21, 11:00 A.M.', 'dd/mm/yy, hh:mi A.M.'));

SELECT AsTimeStamp - unknown FROM times, unknown_table;
Copy
|ASTIMESTAMP-UNKNOWN|
|-------------------|
|4417 23:0:0.0      |
Copy
Snowflake
 -- CREATE TABLE UNKNOWN_TABLE(Unknown timestamp);
-- INSERT INTO UNKNOWN_TABLE VALUES (TO_TIMESTAMP('01/10/09, 12:00 P.M.', 'dd/mm/yy, hh:mi P.M.'));
CREATE OR REPLACE TABLE TIMES (AsTimeStamp TIMESTAMP(6)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

INSERT INTO TIMES
VALUES (TO_TIMESTAMP('05/11/21, 11:00 A.M.', 'dd/mm/yy, hh:mi A.M.'));

SELECT
PUBLIC.DATEDIFF_UDF(
    !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN Timestamp AND unknown ***/!!!
    AsTimeStamp,
    unknown
)
FROM
times,
unknown_table;
Copy
PUBLIC.DATEDIFF_UDF( ASTIMESTAMP, UNKNOWN)|
------------------------------------------+
                                      4418|
Copy

EWIs relacionados

  1. SSC-EWI-OR0036: Problemas de resolução de tipos, a operação aritmética pode não se comportar corretamente entre string e date.

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

JSON_VALUE UDF

Nota

Algumas partes do código de saída foram omitidas por motivos de clareza.

Descrição

De acordo com a documentação da Oracle, essa função usa a expressão de caminho SQL/JSON para solicitar informações sobre uma parte de uma instância JSON. O valor retornado é sempre um valor escalar, caso contrário, a função retorna NULL por padrão.

JSON_VALUE
  ( expr [ FORMAT JSON ], [ JSON_basic_path_expression ]
    [ JSON_value_returning_clause ] [ JSON_value_on_error_clause ]
    [ JSON_value_on_empty_clause ][ JSON_value_on_mismatch_clause ]
  )
Copy

O JSON_VALUE_UDF é uma implementação do Snowflake da especificação JSONPath que usa uma versão modificada da implementação JavaScript original desenvolvida por Stefan Goessner.

Amostra de padrões da origem

Dados de configuração

Execute essas consultas para executar consultas na seção Padrões de JSON_VALUE.

Oracle
 CREATE TABLE MY_TAB (
    my_json VARCHAR(5000)
);

INSERT INTO MY_TAB VALUES ('{ 
    "store": {
      "book": [ 
        { "category": "reference",
          "author": "Nigel Rees",
          "title": "Sayings of the Century",
          "price": 8.95
        },
        { "category": "fiction",
          "author": "Evelyn Waugh",
          "title": "Sword of Honour",
          "price": 12.99
        },
        { "category": "fiction",
          "author": "Herman Melville",
          "title": "Moby Dick",
          "isbn": "0-553-21311-3",
          "price": 8.99
        },
        { "category": "fiction",
          "author": "J. R. R. Tolkien",
          "title": "The Lord of the Rings",
          "isbn": "0-395-19395-8",
          "price": 22.99
        }
      ],
      "bicycle": {
        "color": "red",
        "price": 19.95
      }
    }
  }');
Copy
Snowflake
 CREATE OR REPLACE TABLE MY_TAB (
       my_json VARCHAR(5000)
   )
   COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
   ;

   INSERT INTO MY_TAB
   VALUES ('{ 
    "store": {
      "book": [ 
        { "category": "reference",
          "author": "Nigel Rees",
          "title": "Sayings of the Century",
          "price": 8.95
        },
        { "category": "fiction",
          "author": "Evelyn Waugh",
          "title": "Sword of Honour",
          "price": 12.99
        },
        { "category": "fiction",
          "author": "Herman Melville",
          "title": "Moby Dick",
          "isbn": "0-553-21311-3",
          "price": 8.99
        },
        { "category": "fiction",
          "author": "J. R. R. Tolkien",
          "title": "The Lord of the Rings",
          "isbn": "0-395-19395-8",
          "price": 22.99
        }
      ],
      "bicycle": {
        "color": "red",
        "price": 19.95
      }
    }
  }');
Copy

Padrões de JSON_VALUE

Oracle
 -- 'Sayings of the Century'
SELECT JSON_VALUE(MY_JSON, '$..book[0].title') AS VALUE FROM MY_TAB;

-- NULL
-- gets books in positions 0, 1, 2 and 3 but returns null (default behavior) since a non scalar value was returned
SELECT JSON_VALUE(MY_JSON, '$..book[0,1 to 3,3]') AS VALUE FROM MY_TAB;

-- 'Sayings of the Century'
SELECT JSON_VALUE(MY_JSON, '$.store.book[*]?(@.category == "reference").title') AS VALUE FROM MY_TAB;

-- 'MY ERROR MESSAGE'
-- triggers error because the result is a non scalar value (is an object)
SELECT JSON_VALUE(MY_JSON, '$..book[0]' DEFAULT 'MY ERROR MESSAGE' ON ERROR DEFAULT 'MY EMPTY MESSAGE' ON EMPTY) AS VALUE FROM MY_TAB;

-- 'MY EMPTY MESSAGE'
-- triggers the on empty class because does not exists in the first book element
SELECT JSON_VALUE(MY_JSON, '$..book[0].isbn' DEFAULT 'MY ERROR MESSAGE' ON ERROR DEFAULT 'MY EMPTY MESSAGE' ON EMPTY) AS VALUE FROM MY_TAB;

-- Oracle error message: ORA-40462: JSON_VALUE evaluated to no value
-- this is a custom message from the UDF when no match is found and the ON ERROR clause is set to ERROR
SELECT JSON_VALUE(MY_JSON, '$..book[0].isbn' ERROR ON ERROR) AS VALUE FROM MY_TAB;

-- NULL
SELECT JSON_VALUE(MY_JSON, '$..book[0].isbn' NULL ON ERROR) AS VALUE FROM MY_TAB;

-- Oracle error message: ORA-40462: JSON_VALUE evaluated to no value
-- this is a custom message from the UDF when no match is found and the ON EMPTY clause is set to ERROR
SELECT JSON_VALUE(MY_JSON, '$..book[0].isbn' ERROR ON EMPTY) AS VALUE FROM MY_TAB;

-- NULL
SELECT JSON_VALUE(MY_JSON, '$..book[0].isbn' NULL ON EMPTY) AS VALUE FROM MY_TAB;

-- 'Sayings of the Century'
SELECT JSON_VALUE(MY_JSON, '$..book[0].title' RETURNING VARCHAR2) AS VALUE FROM MY_TAB;

-- 'Sayin'
SELECT JSON_VALUE(MY_JSON, '$..book[0].title' RETURNING VARCHAR2(5) TRUNCATE) AS VALUE FROM MY_TAB;

-- 'Sayings of the Century'
SELECT JSON_VALUE(MY_JSON, '$..book[0].title' RETURNING CLOB) AS VALUE FROM MY_TAB;

-- NULL
-- This is because the title field is a string and the function expects a number result type
SELECT JSON_VALUE(MY_JSON, '$..book[0].title' RETURNING NUMBER) AS VALUE FROM MY_TAB;

-- 420
-- This is because the title field is a string and the function expects a number result type
SELECT JSON_VALUE(MY_JSON, '$..book[0].title' RETURNING NUMBER DEFAULT 420 ON ERROR) AS VALUE FROM MY_TAB;

-- Oracle error message: ORA-01858: a non-numeric character was found where a numeric was expected
SELECT JSON_VALUE(MY_JSON, '$..book[0].title' RETURNING DATE ERROR ON ERROR) AS VALUE FROM MY_TAB;

-- ORA-40450: invalid ON ERROR clause
SELECT JSON_VALUE(MY_JSON, '$..book[0].title' ERROR ON MISMATCH) AS VALUE FROM MY_TAB;
Copy
JSON PathQuery result
'$..book[0].title''Sayings of the Century'
'$..book[0,1 to 3,3]'NULL
'$.store.book[*]?(@.category == "reference").title''Sayings of the Century'
'$..book[0]''MY ERROR MESSAGE'
'$..book[0].isbn''MY EMPTY MESSAGE'
'$..book[0].isbn'ORA-40462: JSON_VALUE evaluated to no value
'$..book[0].isbn'NULL
'$..book[0].isbn'ORA-40462: JSON_VALUE evaluated to no value
'$..book[0].isbn'NULL
'$..book[0].title''Sayings of the Century'
'$..book[0].title''Sayin'
'$..book[0].title''Sayings of the Century'
'$..book[0].title'NULL
'$..book[0].title'420
'$..book[0].title'ORA-01858: a non-numeric character was found where a numeric was expected
'$..book[0].title'ORA-40450: invalid ON ERROR clause
Snowflake
-- 'Sayings of the Century'
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].title', NULL, NULL, NULL) AS VALUE FROM
MY_TAB;

-- NULL
-- gets books in positions 0, 1, 2 and 3 but returns null (default behavior) since a non scalar value was returned
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0,1 to 3,3]', NULL, NULL, NULL) AS VALUE FROM
MY_TAB;

-- 'Sayings of the Century'
SELECT
JSON_VALUE_UDF(MY_JSON, '$.store.book[*]?(@.category == "reference").title', NULL, NULL, NULL) AS VALUE FROM
MY_TAB;

-- 'MY ERROR MESSAGE'
-- triggers error because the result is a non scalar value (is an object)
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0]', NULL, 'MY ERROR MESSAGE' :: VARIANT, 'MY EMPTY MESSAGE' :: VARIANT) AS VALUE FROM
MY_TAB;

-- 'MY EMPTY MESSAGE'
-- triggers the on empty class because does not exists in the first book element
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].isbn', NULL, 'MY ERROR MESSAGE' :: VARIANT, 'MY EMPTY MESSAGE' :: VARIANT) AS VALUE FROM
MY_TAB;

-- Oracle error message: ORA-40462: JSON_VALUE evaluated to no value
-- this is a custom message from the UDF when no match is found and the ON ERROR clause is set to ERROR
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].isbn', NULL, 'SSC_ERROR_ON_ERROR' :: VARIANT, NULL) AS VALUE FROM
MY_TAB;

-- NULL
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].isbn', NULL, 'SSC_NULL_ON_ERROR' :: VARIANT, NULL) AS VALUE FROM
MY_TAB;

-- Oracle error message: ORA-40462: JSON_VALUE evaluated to no value
-- this is a custom message from the UDF when no match is found and the ON EMPTY clause is set to ERROR
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].isbn', NULL, NULL, 'SSC_ERROR_ON_EMPTY' :: VARIANT) AS VALUE FROM
MY_TAB;

-- NULL
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].isbn', NULL, NULL, 'SSC_NULL_ON_EMPTY' :: VARIANT) AS VALUE FROM
MY_TAB;

-- 'Sayings of the Century'
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].title', 'string', NULL, NULL) AS VALUE FROM
MY_TAB;

-- 'Sayin'
SELECT
LEFT(JSON_VALUE_UDF(MY_JSON, '$..book[0].title', 'string', NULL, NULL), 5) AS VALUE FROM
MY_TAB;

-- 'Sayings of the Century'
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].title', 'string', NULL, NULL) AS VALUE FROM
MY_TAB;

-- NULL
-- This is because the title field is a string and the function expects a number result type
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].title', 'number', NULL, NULL) AS VALUE FROM
MY_TAB;

-- 420
-- This is because the title field is a string and the function expects a number result type
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].title', 'number', 420 :: VARIANT, NULL) AS VALUE FROM
MY_TAB;

-- Oracle error message: ORA-01858: a non-numeric character was found where a numeric was expected
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-0021 - RETURNING CLAUSE NOT SUPPORTED IN SNOWFLAKE ***/!!!
JSON_VALUE_UDF(MY_JSON, '$..book[0].title', NULL, 'SSC_ERROR_ON_ERROR' :: VARIANT, NULL) AS VALUE FROM
MY_TAB;

-- ORA-40450: invalid ON ERROR clause
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-0021 - ON MISMATCH CLAUSE NOT SUPPORTED IN SNOWFLAKE ***/!!!
JSON_VALUE_UDF(MY_JSON, '$..book[0].title', NULL, NULL, NULL) AS VALUE FROM
MY_TAB;
Copy
JSON PathQuery result
'$..book[0].title''Sayings of the Century'
'$..book[0,1 to 3,3]'NULL
'$.store.book[*]?(@.category == "reference").title''Sayings of the Century'
'$..book[0]''MY ERROR MESSAGE'
'$..book[0].isbn''MY EMPTY MESSAGE'
'$..book[0].isbn'"SSC_CUSTOM_ERROR - NO MATCH FOUND"
'$..book[0].isbn'NULL
'$..book[0].isbn'"SSC_CUSTOM_ERROR - NO MATCH FOUND"
'$..book[0].isbn'NULL
'$..book[0].title''Sayings of the Century'
'$..book[0].title''Sayin'
'$..book[0].title''Sayings of the Century'
'$..book[0].title'NULL
'$..book[0].title'420
'$..book[0].title'NOT SUPPORTED
'$..book[0].title'NOT SUPPORTED

Problemas conhecidos

1. Returning Type Clause is not fully supported

Agora, os únicos tipos compatíveis ao converter a funcionalidade da cláusula RETURNING TYPE são VARCHAR2, CLOB e NUMBER.

Para todos os outros tipos compatíveis com a função original JSON_VALUE, a função JSON_VALUE_UDF se comportará como se nenhuma cláusula RETURNING TYPE tivesse sido especificada.

Tipos não suportados:

  • DATE

  • TIMESTAMP [WITH TIME ZONE]

  • SDO_GEOMETRY

  • CUSTOM TYPE

2. ON MISMATCH Clause is not supported

Agora, a cláusula ON MISMATCH não é suportada e, em seu lugar, é colocado um aviso EWI. Assim, o código convertido se comportará como se nenhuma cláusula ON MISMATCH tivesse sido especificada originalmente.

3. Complex filters are not supported

Filtros complexos com mais de uma expressão retornarão nulo, pois não são suportados.

Por exemplo, com os mesmos dados de antes, este caminho JSON $.store.book[*]?(@.category == "reference").title é compatível e retornará 'Sayings of the Century'.

No entanto, $.store.book[*]?(@.category == "reference" && @.price < 10).title retornará null, pois mais de uma expressão é usada no filtro.

EWIs relacionados

  1. SSC-EWI-0021: Não é compatível com o Snowflake.

JULIAN TO GREGORIAN DATE UDF

Descrição

Essa função definida pelo usuário (UDF) é usada para transformar ou converter o formato de data juliana em um formato de data gregoriana. As datas julianas podem ser recebidas em três formatos diferentes, como JD Edwards World, astronômico ou formato comum.

Sobrecargas personalizadas de UDF

JULIAN_TO_GREGORIAN_DATE_UDF(julianDate, formatSelected)

Ele retorna uma cadeia de caracteres com o formato de data gregoriana YYYY-MM-DD.

Parâmetros:

JulianDate: A data juliana a ser convertida. Pode ser CYYDDD (onde C é o século) ou YYYYDDD.

formatSelected: Representa o formato no qual a data juliana deve ser processada. Além disso, ele é um CHAR e pode aceitar os seguintes formatos:

Format availableLetter representation in CHARDescription
Astronomy standardized'J'It is the default format. The cast is based in the expected conversion of the Astronomical Applications Department of the US. The Julian Date format for this is YYYYDDD.
JD Edwards World'E'The expected Julian date to be received in this case should be CYYDDD (where C represents the century and is operationalized to be added 19 to the corresponding number).
Ordinal dates'R'The ordinal dates are an arrangement of numbers which represent a concisely date. The format is YYYYDDD and can be easily read because the year part is not mutable.
 CREATE OR REPLACE FUNCTION PUBLIC.JULIAN_TO_GREGORIAN_DATE_UDF(JULIAN_DATE CHAR(7), FORMAT_SELECTED CHAR(1))
RETURNS variant
LANGUAGE JAVASCRIPT
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
    const CONST_FOR_MODIFIED_JULIAN_DATE = 0.5;
    const BEGINNING_OF_GREG_CALENTAR = 2299161;
    const CONST_AFTER_GREG_VALUE = 1867216.25;
    const DIVIDENT_TO_GET_CENTURY = 36524.25;
    const LEAP_YEAR_CONSTANT = 4;
    const CONST_TO_GET_DAY_OF_MONTH = 30.6001;

    //Functions definitions

    function julianToGregorian(julianDate){
        const JD = julianDate + CONST_FOR_MODIFIED_JULIAN_DATE; //setting modified julian date 
        const Z = Math.floor(JD); //setting fractional part of julian day
        const F = JD - Z; //fractional part of the julian date
        let A, alpha, B, C, D, E, year, month, day;
        
        //verification for the beginning of gregorian calendar
        if(Z < BEGINNING_OF_GREG_CALENTAR){ 
            A=Z; 
        } else {
            //alpha is for dates after the beginning of gregorian calendar
            alpha = Math.floor((Z-CONST_AFTER_GREG_VALUE) / DIVIDENT_TO_GET_CENTURY);
            A=Z+1+alpha - Math.floor(alpha/LEAP_YEAR_CONSTANT);
        }

        B = A + 1524;
        C = Math.floor((B-122.1)/365.25);
        D = Math.floor(365.25*C);
        E = Math.floor((B-D)/CONST_TO_GET_DAY_OF_MONTH);

        day= Math.floor(B-D-Math.floor(CONST_TO_GET_DAY_OF_MONTH*E)+F);
        month=(E<14)? E -1: E-13;
        year=(month>2)? C-4716: C-4715;

        return new Date(year, month-1, day);
    }

function cyydddToGregorian(julianDate){
        var c=Math.floor(julianDate/1000);
        var yy=(c<80)? c+2000: c+1900;
        var ddd=julianDate%1000;
        var date= new Date(yy, 0);
        date.setDate(ddd);
        return date;
    }

function ordinalDate(ordinalDate){
    const year = parseInt(ordinalDate.toString().substring(0,4));
    const dayOfYear = parseInt(ordinalDate.toString().substring(4));
    const date = new Date(year, 0); //Set date to the first day of year
    date.setDate(dayOfYear);
    return date;
}

function formatDate(toFormatDate){
    toFormatDate = toFormatDate.toDateString();
    let year = toFormatDate.split(" ")[3];
    let month = toFormatDate.split(" ")[1];
    let day = toFormatDate.split(" ")[2];
    return new Date(month + day + ", " + Math.abs(year)).toISOString().split('T')[0]
}

    switch(FORMAT_SELECTED){
        case 'E': 
            //JD Edwards World formar, century added  - CYYDDD
            var result = formatDate(cyydddToGregorian(parseInt(JULIAN_DATE)));
            return result;
        break; 
        case 'J':
            //astronomical format YYYYDDD
            return formatDate(julianToGregorian(parseInt(JULIAN_DATE)));
        break;
        case 'R':
            //ordinal date format YYYYDDD
            return formatDate(ordinalDate(parseInt(JULIAN_DATE)));
        break;
        default: return null;
    }
    
$$
;
Copy

Exemplo de uso

Oracle

 select to_date('2020001', 'J') from dual;
Copy

| TO\_DATE('2020001', 'J') |
| ------------------------ |
| 18-JUN-18                |



| TO\_CHAR(TO\_DATE('2020001', 'J'), 'YYYY-MON-DD') |
| ------------------------------------------------- |
| 0818-JUN-18                                       |
Copy
  • _Nota: A data deve ser formatada para que você possa visualizar todos os dígitos do ano

Snowflake

 select
PUBLIC.JULIAN_TO_GREGORIAN_DATE_UDF('2020001', 'J')
from dual;
Copy

| JULIAN\_TO\_GREGORIAN\_DATE\_UDF('2020001', 'J') |
| ------------------------------------------------ |
| "0818-06-18"                                     |
Copy

Problemas conhecidos

  1. Qualquer outro formato: Se a data juliana for formatada em qualquer outro formato não suportado, haverá diferenças na saída.

  2. Os intervalos de datas de B.C. podem representar inconsistências devido a funções não suportadas do Snowflake para datas.

EWIs relacionados

Sem EWIs relacionados

MONTHS BETWEEN UDF [DEPRECATED]

Este UDF foi descontinuado. A transformação atual para Oracle MONTHS_BETWEEN() é Snowflake MONTHS_BETWEEN().

Description

MONTHS_BETWEEN retorna o número de meses entre as datas date1 e date2. (Referência da linguagem Oracle MONTHS_BETWEEN SQL)

MONTHS_BETWEEN(date1, date2)
Copy

As funções Oracle MONTHS_BETWEEN e Snowflake MONTHS_BETWEEN têm algumas diferenças funcionais. Para minimizar essas diferenças e replicar melhor a função Oracle MONTHS_BETWEEN, adicionamos um UDF personalizado.

Custom UDF overloads

MONTHS_BETWEEN_UDF(timestamp_ltz, timestamp_ltz)

Parâmetros

  1. FIRST_DATE: O primeiro TIMESTAMP_LTZ da operação.

  2. SECOND_DATE: O segundo TIMESTAMP_LTZ da operação.

 CREATE OR REPLACE FUNCTION MONTHS_BETWEEN_UDF(FIRST_DATE TIMESTAMP_LTZ, SECOND_DATE TIMESTAMP_LTZ)
RETURNS NUMBER
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
ROUND(MONTHS_BETWEEN(FIRST_DATE, SECOND_DATE))
$$
;
Copy
Oracle
 SELECT 
	MONTHS_BETWEEN('2000-03-20 22:01:11', '1996-03-20 10:01:11'),
	MONTHS_BETWEEN('1996-03-20 22:01:11', '2000-03-20 10:01:11'),
	MONTHS_BETWEEN('1982-05-11 22:31:19', '1900-01-25 15:21:15'),
	MONTHS_BETWEEN('1999-12-25 01:15:16', '1900-12-11 02:05:16')
FROM DUAL;
Copy
MONTHS_BETWEEN('2000-03-2022:01:11','1996-03-2010:01:11')|MONTHS_BETWEEN('1996-03-2022:01:11','2000-03-2010:01:11')|MONTHS_BETWEEN('1982-05-1122:31:19','1900-01-2515:21:15')|MONTHS_BETWEEN('1999-12-2501:15:16','1900-12-1102:05:16')|
---------------------------------------------------------+---------------------------------------------------------+---------------------------------------------------------+---------------------------------------------------------+
                                                       48|                                                      -48|                 987.558021206690561529271206690561529271|                1188.450492831541218637992831541218637993|
Copy
Snowflake
 SELECT
	MONTHS_BETWEEN('2000-03-20 22:01:11', '1996-03-20 10:01:11'),
	MONTHS_BETWEEN('1996-03-20 22:01:11', '2000-03-20 10:01:11'),
	MONTHS_BETWEEN('1982-05-11 22:31:19', '1900-01-25 15:21:15'),
	MONTHS_BETWEEN('1999-12-25 01:15:16', '1900-12-11 02:05:16')
FROM DUAL;
Copy
MONTHS_BETWEEN_UDF('2000-03-20 22:01:11', '1996-03-20 10:01:11')|MONTHS_BETWEEN_UDF('1996-03-20 22:01:11', '2000-03-20 10:01:11')|MONTHS_BETWEEN_UDF('1982-05-11 22:31:19', '1900-01-25 15:21:15')|MONTHS_BETWEEN_UDF('1999-12-25 01:15:16', '1900-12-11 02:05:16')|
----------------------------------------------------------------+----------------------------------------------------------------+----------------------------------------------------------------+----------------------------------------------------------------+
                                                       48.000000|                                                      -48.000000|                                                      987.558024|                                                     1188.450497|
Copy

Known Issues

1. Precision may differ from Oracle

Alguns resultados podem diferir no número de dígitos decimais.

Related EWIs

Sem EWIs relacionados.

REGEXP LIKE UDF

Nota

Algumas partes do código de saída foram omitidas por motivos de clareza.

Descrição

REGEXP_LIKE realiza a correspondência de expressões regulares. Essa condição avalia as cadeias de caracteres usando caracteres definidos pelo conjunto de caracteres de entrada. (Condição REGEXP_LIKE da referência de linguagem Oracle)

 REGEXP_LIKE(source_char, pattern [, match_param ])
Copy

A condição do Oracle REGEXP_LIKE e do Snowflake REGEXP_LIKE tem algumas diferenças funcionais. Para minimizar essas diferenças e replicar melhor a função do Oracle REGEXP_LIKE, adicionamos um UDF personalizado. A ideia principal é escapar o símbolo de barra invertida da expressão regular onde ele é necessário. Estes são os caracteres especiais que precisam ser escapados quando vêm com uma barra invertida: 'd', 'D', 'w', 'W', 's', 'S', 'A', 'Z', 'n'. Além disso, a expressão de referência inversa (corresponde ao mesmo texto que foi correspondido mais recentemente pelo grupo de captura «number specified») precisa ser escapada.

Sobrecargas personalizadas de UDF

REGEXP_LIKE_UDF(string, string)

Parâmetros
  1. COL: é a expressão de caractere que serve como valor de pesquisa.

  2. PATTERN: é a expressão regular.

 CREATE OR REPLACE FUNCTION REGEXP_LIKE_UDF(COL STRING, PATTERN STRING) 
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
return COL.match(new RegExp(PATTERN));
$$;
Copy
Oracle
Snowflake

REGEXP_LIKE_UDF(string, string, string)

Parâmetros
  1. COL: é a expressão de caractere que serve como valor de pesquisa.

  2. PATTERN: é a expressão regular.

  3. MATCHPARAM: é uma expressão de caractere que permite alterar o comportamento de correspondência padrão da condição. Na tabela a seguir, há os caracteres Oracle com sua descrição e seu equivalente no UDF.

Match ParameterDescriptionUDF Equivalent
'i'Specifies case-insensitive matching, even if the determined collation of the condition is case-sensitive.'i'
'c'Specifies case-sensitive and accent-sensitive matching, even if the determined collation of the condition is case-insensitive or accent-insensitive.Does not have an equivalent. It is being removed from the parameter..
'n'Allows the period (.), which is the match-any-character wildcard character, to match the newline character. If you omit this parameter, then the period does not match the newline character.'s'
'm'Treats the source string as multiple lines. Oracle interprets ^ and $ as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. If you omit this parameter, then Oracle treats the source string as a single line.'m'
'x'Ignores whitespace characters. By default, whitespace characters match themselves.Does not have an equivalent. It is being removed from the parameter.
 CREATE OR REPLACE FUNCTION REGEXP_LIKE_UDF(COL STRING, PATTERN STRING, MATCHPARAM STRING) 
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
return COL.match(new RegExp(PATTERN, MATCHPARAM));
$$;
Copy
Oracle
Snowflake

Problemas conhecidos

1. UDF match parameter may not behave as expected

Como todos os caracteres disponíveis no parâmetro de correspondência do Oracle não têm seu equivalente na função definida pelo usuário, o resultado da consulta pode ter algumas diferenças funcionais em comparação com o Oracle.

2. UDF pattern parameter does not allow only “\” as a regular expression

Se, como parâmetro de padrão, a expressão regular usada for apenas “\”, será lançada uma exceção como esta: JavaScript execution error: Uncaught SyntaxError: Invalid regular expression: //: \ no final do padrão em REGEXP_LIKE_UDF em “return COL.match(new RegExp(PATTERN));” position 17 stackstrace: REGEXP_LIKE_UDF

TIMESTAMP DIFF UDF

Descrição

O Snowflake não suporta a operação de adição entre os tipos de dados TIMESTAMP com o operando -. Para replicar essa funcionalidade, adicionamos um UDF personalizado.

Sobrecargas personalizadas de UDF

TIMESTAMP_DIFF_UDF(timestamp, timestamp)

Parâmetros

  1. LEFT_TS: O primeiro TIMESTAMP da operação.

  2. RIGHT_TS: O TIMESTAMP a ser adicionado.

 CREATE OR REPLACE FUNCTION TIMESTAMP_DIFF_UDF(LEFT_TS TIMESTAMP, RIGHT_TS TIMESTAMP )
RETURNS VARCHAR
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH RESULTS(days,hours,min,sec,millisecond,sign) AS
(
  SELECT
  abs(TRUNC(x/1000/3600/24)) days,
  abs(TRUNC(x/1000/60 / 60)-trunc(x/1000/3600/24)*24) hours,
  abs(TRUNC(MOD(x/1000,3600)/60)) min,
  abs(TRUNC(MOD(x/1000,60))) sec,
  abs(TRUNC(MOD(x,1000))) millisecond,
  SIGN(x)
  FROM (SELECT TIMESTAMPDIFF(millisecond, RIGHT_TS, LEFT_TS) x ,SIGN(TIMESTAMPDIFF(millisecond, RIGHT_TS, LEFT_TS)) sign))
  SELECT
  IFF(SIGN>0,'+','-') || TRIM(TO_CHAR(days,'000000000')) || ' ' || TO_CHAR(hours,'00') || ':' || TRIM(TO_CHAR(min,'00')) || ':' || TRIM(TO_CHAR(sec,'00')) || '.' || TRIM(TO_CHAR(millisecond,'00000000'))
  from RESULTS
$$;
Copy
Oracle
 --Create Table
CREATE TABLE timestampdiff_table (col1 TIMESTAMP, col2 TIMESTAMP);

--Insert data
INSERT INTO timestampdiff_table VALUES ('2000-03-20 22:01:11', '1996-03-20 10:01:11');
INSERT INTO timestampdiff_table VALUES ('1996-03-20 22:01:11', '2000-03-20 10:01:11');
INSERT INTO timestampdiff_table VALUES ('1982-05-11 22:31:19', '1900-01-25 15:21:15');
INSERT INTO timestampdiff_table VALUES ('1999-12-25 01:15:16', '1900-12-11 02:05:16');

--Select
SELECT col1 - col2 FROM timestampdiff_table;
Copy
COL1-COL2      |
---------------+
1461 12:0:0.0  |
-1460 12:0:0.0 |
30056 7:10:4.0 |
36172 23:10:0.0|
Copy
Snowflake
 --Create Table
CREATE OR REPLACE TABLE timestampdiff_table (col1 TIMESTAMP(6),
col2 TIMESTAMP(6)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
;

--Insert data
INSERT INTO timestampdiff_table
VALUES ('2000-03-20 22:01:11', '1996-03-20 10:01:11');

INSERT INTO timestampdiff_table
VALUES ('1996-03-20 22:01:11', '2000-03-20 10:01:11');

INSERT INTO timestampdiff_table
VALUES ('1982-05-11 22:31:19', '1900-01-25 15:21:15');

INSERT INTO timestampdiff_table
VALUES ('1999-12-25 01:15:16', '1900-12-11 02:05:16');

--Select
SELECT
PUBLIC.TIMESTAMP_DIFF_UDF( col1, col2) FROM
timestampdiff_table;
Copy
TIMESTAMP_DIFF_UDF( COL1, COL2)|
-------------------------------+
+000001461  12:00:00.00000000  |
-000001460  12:00:00.00000000  |
+000030056  07:10:04.00000000  |
+000036172  23:10:00.00000000  |
Copy

Problemas conhecidos

1. TIMESTAMP format may differ from Oracle

O formato TIMESTAMP pode ser diferente do Oracle; considere a configuração TIMESTAMP_OUTPUT_FORMAT ao trabalhar com tipos de dados TIMESTAMP.

EWIs relacionados

Sem EWIs relacionados.

TRUNC (date) UDF

Descrição

A função TRUNC (date) retorna date com a parte da hora do dia truncada na unidade especificada pelo modelo de formato fmt. (Referência da linguagem SQL Oracle TRUNC(date))

TRUNC(date [, fmt ])
Copy

As funções Oracle TRUNC e Snowflake TRUNC com argumentos de data têm algumas diferenças funcionais.

TRUNC_UDF será adicionado para lidar com os seguintes casos:

1. O formato não é compatível com o Snowflake.

2. O formato existe no Snowflake, mas funciona de forma diferente.

3. A ferramenta não pode determinar o tipo de dados do primeiro argumento.

4. O formato é fornecido como uma coluna ou expressão e não como um literal.

Sobrecargas personalizadas de UDF

TRUNC_UDF(date)

Aplica uma conversão DATE explícita ao Timestamp de entrada.

Parâmetros

  1. INPUT: O registro de data e hora com fuso horário (TIMESTAMP_LTZ) que precisa ser truncado.

Aviso

O parâmetro padrão do UDF é TIMESTAMP_LTZ. Talvez seja necessário alterá-lo para TIMESTAMP_TZ ou TIMESTAMP_NTZ para corresponder ao TIMESTAMP padrão usado pelo usuário.

 CREATE OR REPLACE FUNCTION PUBLIC.TRUNC_UDF(INPUT TIMESTAMP_LTZ)
RETURNS DATE
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
    INPUT::DATE
$$;
Copy
Oracle
 SELECT
TRUNC(
	TO_TIMESTAMP ( '20-Mar-1996 21:01:11 ', 'DD-Mon-YYYY HH24:MI:SS' )
	)
"Date" FROM DUAL;
Copy
Date                   |
-----------------------+
1996-03-20 00:00:00.000|
Copy
Snowflake
 SELECT
TRUNC(
	TO_TIMESTAMP ( '20-Mar-1996 21:01:11 ', 'DD-Mon-YYYY HH24:MI:SS' ), 'DD'
	)
"Date" FROM DUAL;
Copy
DATE      |
----------+
1996-03-20|
Copy

TRUNC_UDF(date, fmt)

Cria manualmente uma nova data usando a função DATE_FROM_PARTS(), dependendo da categoria de formato usada.

Parâmetros

  1. DATE_TO_TRUNC: O Timestamp com Time Zone (TIMESTAMP_LTZ) que precisa ser truncado.

  2. DATE_FMT: O formato de data como VARCHAR. Os mesmos formatos que são compatíveis com o Oracle.

Aviso

O parâmetro padrão do UDF é TIMESTAMP_LTZ. Talvez seja necessário alterá-lo para TIMESTAMP_TZ ou TIMESTAMP_NTZ para corresponder ao TIMESTAMP padrão usado pelo usuário.

 CREATE OR REPLACE FUNCTION PUBLIC.TRUNC_UDF(DATE_TO_TRUNC TIMESTAMP_LTZ, DATE_FMT VARCHAR(5))
RETURNS DATE
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
CAST(CASE 
WHEN UPPER(DATE_FMT) IN ('CC','SCC') THEN DATE_FROM_PARTS(CAST(LEFT(CAST(YEAR(DATE_TO_TRUNC) as CHAR(4)),2) || '01' as INTEGER),1,1)
WHEN UPPER(DATE_FMT) IN ('SYYYY','YYYY','YEAR','SYEAR','YYY','YY','Y') THEN DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1)
WHEN UPPER(DATE_FMT) IN ('IYYY','IYY','IY','I') THEN 
    CASE DAYOFWEEK(DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
         WHEN 0 THEN DATEADD(DAY, 1, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
         WHEN 1 THEN DATEADD(DAY, 0, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
         WHEN 2 THEN DATEADD(DAY, -1, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
         WHEN 3 THEN DATEADD(DAY, -2, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
         WHEN 4 THEN DATEADD(DAY, -3, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
         WHEN 5 THEN DATEADD(DAY, 3, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
         WHEN 6 THEN DATEADD(DAY, 2, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
    END        
WHEN UPPER(DATE_FMT) IN ('MONTH','MON','MM','RM') THEN DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),MONTH(DATE_TO_TRUNC),1)
WHEN UPPER(DATE_FMT)IN ('Q') THEN DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),(QUARTER(DATE_TO_TRUNC)-1)*3+1,1)
WHEN UPPER(DATE_FMT) IN ('WW') THEN DATEADD(DAY, 0-MOD(TIMESTAMPDIFF(DAY,DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1),DATE_TO_TRUNC),7), DATE_TO_TRUNC)
WHEN UPPER(DATE_FMT) IN ('IW') THEN DATEADD(DAY, 0-MOD(TIMESTAMPDIFF(DAY,(CASE DAYOFWEEK(DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
                                                                 WHEN 0 THEN DATEADD(DAY, 1, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
                                                                 WHEN 1 THEN DATEADD(DAY, 0, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
                                                                 WHEN 2 THEN DATEADD(DAY, -1, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
                                                                 WHEN 3 THEN DATEADD(DAY, -2, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
                                                                 WHEN 4 THEN DATEADD(DAY, -3, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
                                                                 WHEN 5 THEN DATEADD(DAY, 3, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
                                                                 WHEN 6 THEN DATEADD(DAY, 2, DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),1,1))
                                                               END),      DATE_TO_TRUNC),7), DATE_TO_TRUNC)
WHEN UPPER(DATE_FMT) IN ('W') THEN DATEADD(DAY, 0-MOD(TIMESTAMPDIFF(DAY,DATE_FROM_PARTS(YEAR(DATE_TO_TRUNC),MONTH(DATE_TO_TRUNC),1),DATE_TO_TRUNC),7), DATE_TO_TRUNC)                                                             
WHEN UPPER(DATE_FMT) IN ('DDD', 'DD','J') THEN DATE_TO_TRUNC
WHEN UPPER(DATE_FMT) IN ('DAY', 'DY','D') THEN DATEADD(DAY, 0-DAYOFWEEK(DATE_TO_TRUNC), DATE_TO_TRUNC)
WHEN UPPER(DATE_FMT) IN ('HH', 'HH12','HH24') THEN DATE_TO_TRUNC
WHEN UPPER(DATE_FMT) IN ('MI') THEN DATE_TO_TRUNC
END AS DATE)
$$
;
Copy

Cenários de formato TRUNC

Aviso

O formato dos resultados depende dos formatos de saída do DateTime configurados para o banco de dados.

1. Natively supported formats

Oracle
 SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YYYY') FROM DUAL UNION ALL 
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YEAR') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YYY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'Y') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'Q') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'MONTH') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'MON') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'MM') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'DD') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'HH') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'MI') FROM DUAL;
Copy
+───────────────────────────────────────────────────────────────────────+
| "TRUNC(TO_DATE('20/04/202213:21:10','DD/MM/YYYYHH24:MI:SS'),'YYYY')"  |
+───────────────────────────────────────────────────────────────────────+
| 01-JAN-22                                                             |
| 01-JAN-22                                                             |
| 01-JAN-22                                                             |
| 01-JAN-22                                                             |
| 01-JAN-22                                                             |
| 01-APR-22                                                             |
| 01-APR-22                                                             |
| 01-APR-22                                                             |
| 01-APR-22                                                             |
| 20-APR-22                                                             |
| 20-APR-22                                                             |
| 20-APR-22                                                             |
+───────────────────────────────────────────────────────────────────────+
Copy
Snowflake
 SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YYYY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YEAR') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YYY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'Y') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'Q') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'MONTH') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'MON') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'MM') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'DD') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'HH') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'MI') FROM DUAL;
Copy
+─────────────────────────────────────────────────────────────────────────+
| "TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'YYYY')"  |
+─────────────────────────────────────────────────────────────────────────+
| 2022-01-01                                                              |
| 2022-01-01                                                              |
| 2022-01-01                                                              |
| 2022-01-01                                                              |
| 2022-01-01                                                              |
| 2022-04-01                                                              |
| 2022-04-01                                                              |
| 2022-04-01                                                              |
| 2022-04-01                                                              |
| 2022-04-20                                                              |
| 2022-04-20                                                              |
| 2022-04-20                                                              |
+─────────────────────────────────────────────────────────────────────────+
Copy

2. Formats mapped to another format

Oracle
 SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS')) FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'SYYYY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'SYEAR') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'RM') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'IW') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'DDD') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'J') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'HH12') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'HH24') FROM DUAL;
Copy
+────────────────────────────────────────────────────────────────+
| "TRUNC(TO_DATE('20/04/202213:21:10','DD/MM/YYYYHH24:MI:SS'))"  |
+────────────────────────────────────────────────────────────────+
| 20-APR-22                                                      |
| 01-JAN-22                                                      |
| 01-JAN-22                                                      |
| 01-APR-22                                                      |
| 18-APR-22                                                      |
| 20-APR-22                                                      |
| 20-APR-22                                                      |
| 20-APR-22                                                      |
| 20-APR-22                                                      |
+────────────────────────────────────────────────────────────────+
Copy
Snowflake
 SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'DD') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'YYYY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'YEAR') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'MM') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'WK') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'DD') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'D') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'HH') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'HH') FROM DUAL;
Copy
+────────────────────────────────────────────────────────────────────────+
| "TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'), 'DD')"  |
+────────────────────────────────────────────────────────────────────────+
| 2022-04-20                                                             |
| 2022-01-01                                                             |
| 2022-01-01                                                             |
| 2022-04-01                                                             |
| 2022-04-18                                                             |
| 2022-04-20                                                             |
| 2022-04-20                                                             |
| 2022-04-20                                                             |
| 2022-04-20                                                             |
+────────────────────────────────────────────────────────────────────────+
Copy

3. Day formats

Oracle
 SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'DAY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'DY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'D') FROM DUAL;
Copy
+──────────────────────────────────────────────────────────────────────+
| "TRUNC(TO_DATE('20/04/202213:21:10','DD/MM/YYYYHH24:MI:SS'),'DAY')"  |
+──────────────────────────────────────────────────────────────────────+
| 17-APR-22                                                            |
| 17-APR-22                                                            |
| 17-APR-22                                                            |
+──────────────────────────────────────────────────────────────────────+
Copy
Snowflake
 SELECT
TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'DAY') FROM DUAL UNION ALL
SELECT
TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'DY') FROM DUAL UNION ALL
SELECT
TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'D') FROM DUAL;
Copy
+────────────────────────────────────────────────────────────────────────────+
| "TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'DAY')"  |
+────────────────────────────────────────────────────────────────────────────+
| 2022-04-17                                                                 |
| 2022-04-17                                                                 |
| 2022-04-17                                                                 |
+────────────────────────────────────────────────────────────────────────────+
Copy

4. Unsupported formats

Oracle
 SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'CC') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'SCC') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'IYYY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'IY') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'I') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'WW') FROM DUAL UNION ALL
SELECT TRUNC(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'W') FROM DUAL;
Copy
+─────────────────────────────────────────────────────────────────────+
| "TRUNC(TO_DATE('20/04/202213:21:10','DD/MM/YYYYHH24:MI:SS'),'CC')"  |
+─────────────────────────────────────────────────────────────────────+
| 01-JAN-01                                                           |
| 01-JAN-01                                                           |
| 03-JAN-22                                                           |
| 03-JAN-22                                                           |
| 03-JAN-22                                                           |
| 16-APR-22                                                           |
| 15-APR-22                                                           |
+─────────────────────────────────────────────────────────────────────+
Copy
Snowflake
 SELECT
TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'CC') FROM DUAL UNION ALL
SELECT
TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'SCC') FROM DUAL UNION ALL
SELECT
TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'IYYY') FROM DUAL UNION ALL
SELECT
TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'IY') FROM DUAL UNION ALL
SELECT
TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'I') FROM DUAL UNION ALL
SELECT
TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'WW') FROM DUAL UNION ALL
SELECT
TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'W') FROM DUAL;
Copy
+───────────────────────────────────────────────────────────────────────────+
| "TRUNC_UDF(TO_DATE('20/04/2022 13:21:10','DD/MM/YYYY HH24:MI:SS'),'CC')"  |
+───────────────────────────────────────────────────────────────────────────+
| 2001-01-01                                                                |
| 2001-01-01                                                                |
| 2022-01-03                                                                |
| 2022-01-03                                                                |
| 2022-01-03                                                                |
| 2022-04-16                                                                |
| 2022-04-15                                                                |
+───────────────────────────────────────────────────────────────────────────+
Copy

Nota

Quando a função TRUNC é usada com um formato não compatível ou com um parâmetro que não pode ser tratado pelo SnowConvert. Para evitar problemas, o formato é substituído por um formato válido ou TRUNC_UDF é adicionado.

Problemas conhecidos

1. Oracle DATE contains TIMESTAMP

Leve em consideração que o Oracle DATE contém um TIMESTAMP vazio (00:00:00.000), enquanto o Snowflake DATE não contém. O SnowConvert permite transformar DATE em TIMESTAMP com o sinalizador SysdateAsCurrentTimestamp.

EWIs Relacionados

Sem EWIs relacionados.

TRUNC (number) UDF

Descrição

A função TRUNC (number) retorna n1 truncado para n2 casas decimais. Se n2 for omitido, então n1 é truncado em 0 casas. n2 pode ser negativo para truncar (tornar zero) n2 dígitos à esquerda do ponto decimal. (Referência da linguagem SQL Oracle TRUNC(number))

TRUNC(n1 [, n2 ])
Copy

TRUNC_UDF para valores numéricos será adicionado para lidar com casos em que a primeira coluna tem um tipo de dados não reconhecido.

Exemplo:

 SELECT TRUNC(column1) FROM DUAL;
Copy

Se a definição de column1 não foi fornecida à ferramenta. Em seguida, o TRUNC_UDF será adicionado e, em tempo de execução, a sobrecarga do TRUNC_UDF tratará o caso se for um tipo numérico ou de data.

Consulte a seção TRUNC (DATE).

As seções a seguir fornecem a prova de que TRUNC_UDF tratará perfeitamente os valores numéricos.

Sobrecargas personalizadas de UDF

TRUNC_UDF(n1)

Ele chama a função Snowflake TRUNC com o número de entrada. Essa sobrecarga existe para lidar com os diferentes tipos de cenários de parâmetros, caso essas informações não estejam disponíveis durante a migração.

Parâmetros

  1. INPUT: O NUMBER que precisa ser truncado.

 CREATE OR REPLACE FUNCTION PUBLIC.TRUNC_UDF(INPUT NUMBER)
RETURNS INT
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
    TRUNC(INPUT)
$$;
Copy
Oracle
 --TRUNC(NUMBER)
SELECT
	TRUNC ( 1.000001 ),
	TRUNC ( 15.79 ),
	TRUNC ( -975.975 ),
	TRUNC ( 135.135 )
FROM DUAL;
Copy
TRUNC(1.000001)|TRUNC(15.79)|TRUNC(-975.975)|TRUNC(135.135)|
---------------+------------+---------------+--------------+
              1|          15|           -975|           135|
Copy
Snowflake
 --TRUNC(NUMBER)
SELECT
	TRUNC ( 1.000001 ),
	TRUNC ( 15.79 ),
	TRUNC ( -975.975 ),
	TRUNC ( 135.135 )
FROM DUAL;
Copy
TRUNC_UDF(1.000001)|TRUNC_UDF(15.79)|TRUNC_UDF(-975.975)|TRUNC_UDF(135.135)|
-------------------+----------------+-------------------+------------------+
                  1|              15|               -975|               135|
Copy

TRUNC_UDF(n1, n2)

Ele chama a função Snowflake TRUNC com o número de entrada e a escala. Essa sobrecarga existe para lidar com os diferentes tipos de cenários de parâmetros, caso essas informações não estejam disponíveis durante a migração.

Parâmetros

  1. INPUT: O NUMBER que precisa ser truncado.

  2. SCALE: Representa o número de dígitos que a saída incluirá após o ponto decimal.

 CREATE OR REPLACE FUNCTION PUBLIC.TRUNC_UDF(INPUT NUMBER, SCALE NUMBER)
RETURNS INT
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
    TRUNC(INPUT, SCALE)
$$;
Copy
Oracle
 --TRUNC(NUMBER, SCALE)
SELECT
	TRUNC ( 1.000001, -2 ),
	TRUNC ( 1.000001, -1 ),
	TRUNC ( 1.000001, 0 ),
	TRUNC ( 1.000001, 1 ),
	TRUNC ( 1.000001, 2 ),
	TRUNC ( 15.79, -2),
	TRUNC ( 15.79, -1),
	TRUNC ( 15.79, 0),
	TRUNC ( 15.79, 1 ),
	TRUNC ( 15.79, 50 ),
	TRUNC ( -9.6, -2 ),
	TRUNC ( -9.6, -1 ),
	TRUNC ( -9.6, 0 ),
	TRUNC ( -9.6, 1 ),
	TRUNC ( -9.6, 2 ),
	TRUNC ( -975.975, -3 ),
	TRUNC ( -975.975, -2 ),
	TRUNC ( -975.975, -1 ),
	TRUNC ( -975.975, 0 ),
	TRUNC ( -975.975, 1 ),
	TRUNC ( -975.975, 2 ),
	TRUNC ( -975.975, 3 ),
	TRUNC ( -975.975, 5 ),
	TRUNC ( 135.135, -10 ),
	TRUNC ( 135.135, -2 ),
	TRUNC ( 135.135, 0 ),
	TRUNC ( 135.135, 1 ),
	TRUNC ( 135.135, 2 ),
	TRUNC ( 135.135, 3 ),
	TRUNC ( 135.135, 5 )
FROM DUAL;
Copy
TRUNC(1.000001,-2)|TRUNC(1.000001,-1)|TRUNC(1.000001,0)|TRUNC(1.000001,1)|TRUNC(1.000001,2)|TRUNC(15.79,-2)|TRUNC(15.79,-1)|TRUNC(15.79,0)|TRUNC(15.79,1)|TRUNC(15.79,50)|TRUNC(-9.6,-2)|TRUNC(-9.6,-1)|TRUNC(-9.6,0)|TRUNC(-9.6,1)|TRUNC(-9.6,2)|TRUNC(-975.975,-3)|TRUNC(-975.975,-2)|TRUNC(-975.975,-1)|TRUNC(-975.975,0)|TRUNC(-975.975,1)|TRUNC(-975.975,2)|TRUNC(-975.975,3)|TRUNC(-975.975,5)|TRUNC(135.135,-10)|TRUNC(135.135,-2)|TRUNC(135.135,0)|TRUNC(135.135,1)|TRUNC(135.135,2)|TRUNC(135.135,3)|TRUNC(135.135,5)|
------------------+------------------+-----------------+-----------------+-----------------+---------------+---------------+--------------+--------------+---------------+--------------+--------------+-------------+-------------+-------------+------------------+------------------+------------------+-----------------+-----------------+-----------------+-----------------+-----------------+------------------+-----------------+----------------+----------------+----------------+----------------+----------------+
                 0|                 0|                1|                1|                1|              0|             10|            15|          15.7|          15.79|             0|             0|           -9|         -9.6|         -9.6|                 0|              -900|              -970|             -975|           -975.9|          -975.97|         -975.975|         -975.975|                 0|              100|             135|           135.1|          135.13|         135.135|         135.135|
Copy
Snowflake
 --TRUNC(NUMBER, SCALE)
SELECT
	TRUNC ( 1.000001, -2 ),
	TRUNC ( 1.000001, -1 ),
	TRUNC ( 1.000001, 0 ),
	TRUNC ( 1.000001, 1 ),
	TRUNC ( 1.000001, 2 ),
	TRUNC ( 15.79, -2),
	TRUNC ( 15.79, -1),
	TRUNC ( 15.79, 0),
	TRUNC ( 15.79, 1 ),
	TRUNC ( 15.79, 50 ),
	TRUNC ( -9.6, -2 ),
	TRUNC ( -9.6, -1 ),
	TRUNC ( -9.6, 0 ),
	TRUNC ( -9.6, 1 ),
	TRUNC ( -9.6, 2 ),
	TRUNC ( -975.975, -3 ),
	TRUNC ( -975.975, -2 ),
	TRUNC ( -975.975, -1 ),
	TRUNC ( -975.975, 0 ),
	TRUNC ( -975.975, 1 ),
	TRUNC ( -975.975, 2 ),
	TRUNC ( -975.975, 3 ),
	TRUNC ( -975.975, 5 ),
	TRUNC ( 135.135, -10 ),
	TRUNC ( 135.135, -2 ),
	TRUNC ( 135.135, 0 ),
	TRUNC ( 135.135, 1 ),
	TRUNC ( 135.135, 2 ),
	TRUNC ( 135.135, 3 ),
	TRUNC ( 135.135, 5 )
FROM DUAL;
Copy
TRUNC_UDF ( 1.000001, -2 )|TRUNC_UDF ( 1.000001, -1 )|TRUNC_UDF ( 1.000001, 0 )|TRUNC_UDF ( 1.000001, 1 )|TRUNC_UDF ( 1.000001, 2 )|TRUNC_UDF ( 15.79, -2)|TRUNC_UDF ( 15.79, -1)|TRUNC_UDF ( 15.79, 0)|TRUNC_UDF ( 15.79, 1 )|TRUNC_UDF ( 15.79, 50 )|TRUNC_UDF ( -9.6, -2 )|TRUNC_UDF ( -9.6, -1 )|TRUNC_UDF ( -9.6, 0 )|TRUNC_UDF ( -9.6, 1 )|TRUNC_UDF ( -9.6, 2 )|TRUNC_UDF ( -975.975, -3 )|TRUNC_UDF ( -975.975, -2 )|TRUNC_UDF ( -975.975, -1 )|TRUNC_UDF ( -975.975, 0 )|TRUNC_UDF ( -975.975, 1 )|TRUNC_UDF ( -975.975, 2 )|TRUNC_UDF ( -975.975, 3 )|TRUNC_UDF ( -975.975, 5 )|TRUNC_UDF ( 135.135, -10 )|TRUNC_UDF ( 135.135, -2 )|TRUNC_UDF ( 135.135, 0 )|TRUNC_UDF ( 135.135, 1 )|TRUNC_UDF ( 135.135, 2 )|TRUNC_UDF ( 135.135, 3 )|TRUNC_UDF ( 135.135, 5 )|
--------------------------+--------------------------+-------------------------+-------------------------+-------------------------+----------------------+----------------------+---------------------+----------------------+-----------------------+----------------------+----------------------+---------------------+---------------------+---------------------+--------------------------+--------------------------+--------------------------+-------------------------+-------------------------+-------------------------+-------------------------+-------------------------+--------------------------+-------------------------+------------------------+------------------------+------------------------+------------------------+------------------------+
                         0|                         0|                        1|                      1.0|                     1.00|                     0|                    10|                   15|                  15.7|                  15.79|                     0|                     0|                   -9|                 -9.6|                 -9.6|                         0|                      -900|                      -970|                     -975|                   -975.9|                  -975.97|                 -975.975|                 -975.975|                         0|                      100|                     135|                   135.1|                  135.13|                 135.135|                 135.135|s
Copy

Problemas conhecidos

Não foram encontrados problemas.

EWIs Relacionados

Sem EWIs relacionados.

DATEADD UDF INTERVAL

Nota

Algumas partes do código de saída foram omitidas por motivos de clareza.

Descrição

Este UDF é usado para resolver operações com intervalos como:

  • INTERVAL + DATE

  • INTERVAL + TIMESTAMP

  • DATE + INTERVAL

  • DATE + TIMESTAMP

  • INTERVAL + UNKNOWN

  • UNKNOWN + INTERVAL

Nota

Um tipo UNKNOWN é uma coluna ou expressão cujo tipo não pôde ser resolvido pelo SnowConvert. Isso costuma acontecer quando o DDLs das tabelas não está incluído na migração ou quando há uma expressão ou subconsulta que pode retornar diferentes tipos de dados.

Sobrecargas personalizadas de UDF

DATEADD_UDF(string, date)

Parâmetros

  1. INTERVAL_VALUE: O intervalo String da operação.

  2. D: O DATE onde o intervalo será adicionado.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEADD_UDF(INTERVAL_VALUE STRING,D DATE)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH VARS(INPUT_VALUE, INPUT_PART) AS (
SELECT SUBSTR(INTERVAL_VALUE,11,POSITION('''',INTERVAL_VALUE,11)-11),
       TRIM(SUBSTR(INTERVAL_VALUE,POSITION('''',INTERVAL_VALUE,11)+1)))
SELECT 

    CASE WHEN INPUT_PART='YEAR(2) TO MONTH' OR INPUT_PART='YEAR(4) TO MONTH' THEN
        DATEADD(MONTHS,PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,TO_NUMBER(INPUT_VALUE),D)      
    ELSE
        DATEADD(MICROSECONDS,1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)::DATE
    END CASE    
FROM VARS    
$$;
Copy

DATEADD_UDF(date, string)

Parâmetros

  1. D: O DATE onde o intervalo será adicionado.

  2. INTERVAL_VALUE: O intervalo String da operação.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEADD_UDF(D DATE, INTERVAL_VALUE STRING)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH VARS(INPUT_VALUE, INPUT_PART) AS (
SELECT SUBSTR(INTERVAL_VALUE,11,POSITION('''',INTERVAL_VALUE,11)-11),
       TRIM(SUBSTR(INTERVAL_VALUE,POSITION('''',INTERVAL_VALUE,11)+1)))
SELECT 

    CASE WHEN INPUT_PART='YEAR(2) TO MONTH' OR INPUT_PART='YEAR(4) TO MONTH' THEN
        DATEADD(MONTHS,PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,TO_NUMBER(INPUT_VALUE),D)      
    ELSE
        DATEADD(MICROSECONDS,1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)::DATE
    END CASE    
FROM VARS    
$$;
Copy

DATEADD_UDF(string, timestamp)

Parâmetros

  1. INTERVAL_VALUE: O intervalo String da operação.

  2. D: O TIMESTAMP onde o intervalo será adicionado.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEADD_UDF(INTERVAL_VALUE STRING,D TIMESTAMP)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH VARS(INPUT_VALUE, INPUT_PART) AS (
SELECT SUBSTR(INTERVAL_VALUE,11,POSITION('''',INTERVAL_VALUE,11)-11),
       TRIM(SUBSTR(INTERVAL_VALUE,POSITION('''',INTERVAL_VALUE,11)+1)))
SELECT 

    CASE WHEN INPUT_PART='YEAR(2) TO MONTH' OR INPUT_PART='YEAR(4) TO MONTH' THEN
        DATEADD(MONTHS,PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,TO_NUMBER(INPUT_VALUE),D)      
    ELSE
        DATEADD(MICROSECONDS,1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)
    END CASE    
FROM VARS    
$$;
Copy

DATEADD_UDF(timestamp, string)

Parâmetros

  1. D: O TIMESTAMP onde o intervalo será adicionado.

  2. INTERVAL_VALUE: O intervalo String da operação.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEADD_UDF(D TIMESTAMP, INTERVAL_VALUE STRING)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH VARS(INPUT_VALUE, INPUT_PART) AS (
SELECT SUBSTR(INTERVAL_VALUE,11,POSITION('''',INTERVAL_VALUE,11)-11),
       TRIM(SUBSTR(INTERVAL_VALUE,POSITION('''',INTERVAL_VALUE,11)+1)))
SELECT 

    CASE WHEN INPUT_PART='YEAR(2) TO MONTH' OR INPUT_PART='YEAR(4) TO MONTH' THEN
        DATEADD(MONTHS,PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,TO_NUMBER(INPUT_VALUE),D)      
    ELSE
        DATEADD(MICROSECONDS,1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)
    END CASE    
FROM VARS    
$$;
Copy

Exemplo de uso

Nota

--disableDateAsTimestamp

Sinalizador para indicar se SYSDATE deve ser transformado em CURRENT_DATE ou CURRENT_TIMESTAMP. Isso também afetará todas as colunas DATE que serão transformadas em TIMESTAMP.

Oracle
 -- DROP TABLE UNKNOWN_TABLE;
-- CREATE TABLE UNKNOWN_TABLE(Unknown timestamp);
-- INSERT  INTO UNKNOWN_TABLE VALUES (TO_TIMESTAMP('01/10/09, 12:00 P.M.', 'dd/mm/yy, hh:mi P.M.'));

CREATE TABLE TIMES(
AsTimeStamp TIMESTAMP,
AsTimestampTwo TIMESTAMP,
AsDate DATE,
AsDateTwo DATE
);

INSERT INTO TIMES VALUES (
TO_TIMESTAMP('05/11/21, 11:00 A.M.', 'dd/mm/yy, hh:mi A.M.'), 
TO_TIMESTAMP('05/11/21, 10:00 A.M.', 'dd/mm/yy, hh:mi A.M.'), 
TO_DATE('06/11/21', 'dd/mm/yy'), 
TO_DATE('05/11/21', 'dd/mm/yy'));

SELECT 
 AsTimeStamp+INTERVAL '1-1' YEAR(2) TO MONTH,
 AsTimeStamp+INTERVAL '2-1' YEAR(4) TO MONTH,
 AsTimeStamp+INTERVAL '1' MONTH,
 AsTimeStamp+INTERVAL '2' MONTH,
 AsDate+INTERVAL '1-1' YEAR(2) TO MONTH,
 AsDate+INTERVAL '2-1' YEAR(4) TO MONTH,
 AsDate+INTERVAL '1' MONTH,
 AsDate+INTERVAL '2' MONTH,
 Unknown+INTERVAL '1 01:00:00.222' DAY TO SECOND(3),
 Unknown+INTERVAL '1 01:10' DAY TO MINUTE,
 Unknown+INTERVAL '1 1' DAY TO HOUR,
 INTERVAL '1' MONTH+AsTimeStamp,
 INTERVAL '1' MONTH+AsDate,
 INTERVAL '1' MONTH+Unknown,
 INTERVAL '2' MONTH+AsTimeStamp,
 INTERVAL '2' MONTH+AsDate,
 INTERVAL '2' MONTH+Unknown
FROM TIMES, UNKNOWN_TABLE;
Copy
|ASTIMESTAMP+INTERVAL'1-1'YEAR(2)TOMONTH|ASTIMESTAMP+INTERVAL'2-1'YEAR(4)TOMONTH|ASTIMESTAMP+INTERVAL'1'MONTH|ASTIMESTAMP+INTERVAL'2'MONTH|ASDATE+INTERVAL'1-1'YEAR(2)TOMONTH|ASDATE+INTERVAL'2-1'YEAR(4)TOMONTH|ASDATE+INTERVAL'1'MONTH|ASDATE+INTERVAL'2'MONTH|UNKNOWN+INTERVAL'101:00:00.222'DAYTOSECOND(3)|UNKNOWN+INTERVAL'101:10'DAYTOMINUTE|UNKNOWN+INTERVAL'11'DAYTOHOUR|INTERVAL'1'MONTH+ASTIMESTAMP|INTERVAL'1'MONTH+ASDATE|INTERVAL'1'MONTH+UNKNOWN|INTERVAL'2'MONTH+ASTIMESTAMP|INTERVAL'2'MONTH+ASDATE|INTERVAL'2'MONTH+UNKNOWN|
|---------------------------------------|---------------------------------------|----------------------------|----------------------------|----------------------------------|----------------------------------|-----------------------|-----------------------|---------------------------------------------|-----------------------------------|-----------------------------|----------------------------|-----------------------|------------------------|----------------------------|-----------------------|------------------------|
|2022-12-05 11:00:00.000                |2023-12-05 11:00:00.000                |2021-12-05 11:00:00.000     |2022-01-05 11:00:00.000     |2022-12-06 00:00:00.000           |2023-12-06 00:00:00.000           |2021-12-06 00:00:00.000|2022-01-06 00:00:00.000|2009-10-02 13:00:00.222                      |2009-10-02 13:10:00.000            |2009-10-02 13:00:00.000      |2021-12-05 11:00:00.000     |2021-12-06 00:00:00.000|2009-11-01 12:00:00.000 |2022-01-05 11:00:00.000     |2022-01-06 00:00:00.000|2009-12-01 12:00:00.000 |

Copy
Snowflake

Nota

Essa configuração foi usada no Snowflake

 ALTER SESSION SET TIMESTAMP_NTZ_OUTPUT_FORMAT= 'DD-MON-YY HH.MI.SS.FF6 AM';
ALTER SESSION SET DATE_OUTPUT_FORMAT= 'DD-MON-YY';
Copy
 -- DROP TABLE UNKNOWN_TABLE;
-- CREATE TABLE UNKNOWN_TABLE(Unknown timestamp);
-- INSERT  INTO UNKNOWN_TABLE VALUES (TO_TIMESTAMP('01/10/09, 12:00 P.M.', 'dd/mm/yy, hh:mi P.M.'));
CREATE OR REPLACE TABLE TIMES (
 AsTimeStamp TIMESTAMP(6),
 AsTimestampTwo TIMESTAMP(6),
 AsDate TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
 AsDateTwo TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/
 )
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;

INSERT INTO TIMES
VALUES (
TO_TIMESTAMP('05/11/21, 11:00 A.M.', 'dd/mm/yy, hh:mi A.M.'),
TO_TIMESTAMP('05/11/21, 10:00 A.M.', 'dd/mm/yy, hh:mi A.M.'),
TO_DATE('06/11/21', 'dd/mm/yy'),
TO_DATE('05/11/21', 'dd/mm/yy'));

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "UNKNOWN_TABLE" **

SELECT
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!!
 AsTimeStamp + INTERVAL '1y, 1mm',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!!
 AsTimeStamp + INTERVAL '2y, 1mm',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!!
 AsTimeStamp + INTERVAL '1 month',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!!
 AsTimeStamp + INTERVAL '2 month',
 AsDate+ INTERVAL '1y, 1mm',
 AsDate+ INTERVAL '2y, 1mm',
 AsDate+ INTERVAL '1 month',
 AsDate+ INTERVAL '2 month',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!!
 Unknown + INTERVAL '1d, 01h, 00m, 00s, 222ms',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!!
 Unknown + INTERVAL '1d, 01h, 10m',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!!
 Unknown + INTERVAL '1d, 1h',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!!
 AsTimeStamp + INTERVAL '1 month',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!!
 AsDate + INTERVAL '1 month',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!!
 Unknown + INTERVAL '1 month',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!!
 AsTimeStamp + INTERVAL '2 month',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!!
 AsDate + INTERVAL '2 month',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!!
 Unknown + INTERVAL '2 month'
FROM
 TIMES,
 UNKNOWN_TABLE;
Copy
|DATEADD_UDF(ASTIMESTAMP,'INTERVAL ''1-1'' YEAR(2) TO MONTH')|DATEADD_UDF(ASTIMESTAMP,'INTERVAL ''2-1'' YEAR(4) TO MONTH')|DATEADD_UDF(ASTIMESTAMP,'INTERVAL ''1'' MONTH')|DATEADD_UDF(ASTIMESTAMP,'INTERVAL ''2'' MONTH')|DATEADD_UDF(ASDATE,'INTERVAL ''1-1'' YEAR(2) TO MONTH')|DATEADD_UDF(ASDATE,'INTERVAL ''2-1'' YEAR(4) TO MONTH')|DATEADD_UDF(ASDATE,'INTERVAL ''1'' MONTH')|DATEADD_UDF(ASDATE,'INTERVAL ''2'' MONTH')|DATEADD_UDF(UNKNOWN,'INTERVAL ''1 01:00:00.222'' DAY TO SECOND(3)')|DATEADD_UDF(UNKNOWN,'INTERVAL ''1 01:10'' DAY TO MINUTE')|DATEADD_UDF(UNKNOWN,'INTERVAL ''1 1'' DAY TO HOUR')|DATEADD_UDF('INTERVAL ''1'' MONTH',ASTIMESTAMP)|DATEADD_UDF('INTERVAL ''1'' MONTH',ASDATE)|DATEADD_UDF('INTERVAL ''1'' MONTH',UNKNOWN)|DATEADD_UDF('INTERVAL ''2'' MONTH',ASTIMESTAMP)|DATEADD_UDF('INTERVAL ''2'' MONTH',ASDATE)|DATEADD_UDF('INTERVAL ''2'' MONTH',UNKNOWN)|
|------------------------------------------------------------|------------------------------------------------------------|-----------------------------------------------|-----------------------------------------------|-------------------------------------------------------|-------------------------------------------------------|------------------------------------------|------------------------------------------|-------------------------------------------------------------------|---------------------------------------------------------|---------------------------------------------------|-----------------------------------------------|------------------------------------------|-------------------------------------------|-----------------------------------------------|------------------------------------------|-------------------------------------------|
|2022-12-05 11:00:00.000                                     |2023-12-05 11:00:00.000                                     |2021-12-05 11:00:00.000                        |2022-01-05 11:00:00.000                        |2022-12-06                                             |2023-12-06                                             |2021-12-06                                |2022-01-06                                |2009-10-02 13:00:00.222                                            |2009-10-02 13:10:00.000                                  |2009-10-02 13:00:00.000                            |2021-12-05 11:00:00.000                        |2021-12-06                                |2009-11-01 12:00:00.000                    |2022-01-05 11:00:00.000                        |2022-01-06                                |2009-12-01 12:00:00.000                    |

Copy

Problemas conhecidos

1. INTERVAL + INTERVAL Operation is not supported

O Snowflake não é compatível com as operações INTERVAL + INTERVAL.

EWIs relacionados

  1. SSC-EWI-OR0036: Problemas de resolução de tipos, a operação aritmética pode não se comportar corretamente entre string e date.

  2. SSC-EWI-OR0095: Operação entre tipo de intervalo e tipo de data não suportada.

  3. SSC-FDM-0007: Elemento com dependências ausentes.

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

DATEDIFF UDF INTERVAL

Nota

Algumas partes do código de saída foram omitidas por motivos de clareza.

Descrição

Este UDF é usado para resolver operações com intervalos como:

  • INTERVAL - UNKNOWN

  • UNKNOWN - INTERVAL

  • DATE - INTERVAL

  • TIMESTAMP - INTERVAL

Nota

Um tipo UNKNOWN é uma coluna ou expressão cujo tipo não pôde ser resolvido pelo SnowConvert. Isso costuma acontecer quando o DDLs das tabelas não está incluído na migração ou quando há uma expressão ou subconsulta que pode retornar diferentes tipos de dados.

Sobrecargas personalizadas de UDF

DATEADD_DDIF(string, date)

Parâmetros

  1. INTERVAL_VALUE: O intervalo String da operação.

  2. D: O DATE onde o intervalo será subtraído.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(INTERVAL_VALUE STRING,D DATE)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH VARS(INPUT_VALUE, INPUT_PART) AS (
SELECT SUBSTR(INTERVAL_VALUE,11,POSITION('''',INTERVAL_VALUE,11)-11),
       TRIM(SUBSTR(INTERVAL_VALUE,POSITION('''',INTERVAL_VALUE,11)+1)))
SELECT 
    CASE WHEN INPUT_PART='YEAR(2) TO MONTH' OR INPUT_PART='YEAR(4) TO MONTH' THEN
        DATEADD(MONTHS,-1*PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,-1*TO_NUMBER(INPUT_VALUE),D)    
    ELSE
        DATEADD(MICROSECONDS,-1*1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)::DATE
    END CASE    
FROM VARS    
$$;
Copy

DATEADD_DIFF(date, string)

Parâmetros

  1. D: O DATE onde o intervalo será subtraído.

  2. INTERVAL_VALUE: O intervalo String da operação.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(D DATE, INTERVAL_VALUE STRING)
RETURNS DATE
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH VARS(INPUT_VALUE, INPUT_PART) AS (
SELECT SUBSTR(INTERVAL_VALUE,11,POSITION('''',INTERVAL_VALUE,11)-11),
       TRIM(SUBSTR(INTERVAL_VALUE,POSITION('''',INTERVAL_VALUE,11)+1)))
SELECT 
    CASE WHEN INPUT_PART='YEAR(2) TO MONTH' OR INPUT_PART='YEAR(4) TO MONTH' THEN
        DATEADD(MONTHS,-1*PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,-1*TO_NUMBER(INPUT_VALUE),D)    
    ELSE
        DATEADD(MICROSECONDS,-1*1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)::DATE
    END CASE    
FROM VARS    
$$;
Copy

DATEADD_DIFF(string, timestamp)

Parâmetros

  1. INTERVAL_VALUE: O intervalo String da operação.

  2. D: O TIMESTAMP onde o intervalo será subtraído.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(INTERVAL_VALUE STRING,D TIMESTAMP)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH VARS(INPUT_VALUE, INPUT_PART) AS (
SELECT SUBSTR(INTERVAL_VALUE,11,POSITION('''',INTERVAL_VALUE,11)-11),
       TRIM(SUBSTR(INTERVAL_VALUE,POSITION('''',INTERVAL_VALUE,11)+1)))
SELECT 
    CASE WHEN INPUT_PART='YEAR(2) TO MONTH' OR INPUT_PART='YEAR(4) TO MONTH' THEN
        DATEADD(MONTHS,-1*PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,-1*TO_NUMBER(INPUT_VALUE),D)    
    ELSE
        DATEADD(MICROSECONDS,-1*1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)
    END CASE    
FROM VARS    
$$;
Copy

DATEADD_DIFF(timestamp, string)

Parâmetros

  1. D: O TIMESTAMP onde o intervalo será subtraído.

  2. INTERVAL_VALUE: O intervalo String da operação.

 CREATE OR REPLACE FUNCTION PUBLIC.DATEDIFF_UDF(D TIMESTAMP, INTERVAL_VALUE STRING)
RETURNS TIMESTAMP
LANGUAGE SQL
IMMUTABLE
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
WITH VARS(INPUT_VALUE, INPUT_PART) AS (
SELECT SUBSTR(INTERVAL_VALUE,11,POSITION('''',INTERVAL_VALUE,11)-11),
       TRIM(SUBSTR(INTERVAL_VALUE,POSITION('''',INTERVAL_VALUE,11)+1)))
SELECT 
    CASE WHEN INPUT_PART='YEAR(2) TO MONTH' OR INPUT_PART='YEAR(4) TO MONTH' THEN
        DATEADD(MONTHS,-1*PUBLIC.INTERVAL_TO_MONTHS_UDF(INPUT_VALUE),D) 
    WHEN INPUT_PART='MONTH' THEN
        DATEADD(MONTHS,-1*TO_NUMBER(INPUT_VALUE),D)    
    ELSE
        DATEADD(MICROSECONDS,-1*1000000*PUBLIC.INTERVAL_TO_SECONDS_UDF(INPUT_PART, INPUT_VALUE),D)
    END CASE    
FROM VARS    
$$;
Copy

Exemplo de uso

Nota

--disableDateAsTimestamp

Sinalizador para indicar se SYSDATE deve ser transformado em CURRENT_DATE ou CURRENT_TIMESTAMP. Isso também afetará todas as colunas DATE que serão transformadas em TIMESTAMP.

Oracle
 -- DROP TABLE UNKNOWN_TABLE;
-- CREATE TABLE UNKNOWN_TABLE(Unknown timestamp);
-- INSERT  INTO UNKNOWN_TABLE VALUES (TO_TIMESTAMP('01/10/09, 12:00 P.M.', 'dd/mm/yy, hh:mi P.M.'));

CREATE TABLE TIMES(
AsTimeStamp TIMESTAMP,
AsTimestampTwo TIMESTAMP,
AsDate DATE,
AsDateTwo DATE
);

INSERT INTO TIMES VALUES (
TO_TIMESTAMP('05/11/21, 11:00 A.M.', 'dd/mm/yy, hh:mi A.M.'), 
TO_TIMESTAMP('05/11/21, 10:00 A.M.', 'dd/mm/yy, hh:mi A.M.'), 
TO_DATE('06/11/21', 'dd/mm/yy'), 
TO_DATE('05/11/21', 'dd/mm/yy'));

SELECT 
 AsTimeStamp-INTERVAL '1-1' YEAR(2) TO MONTH,
 AsTimeStamp-INTERVAL '2-1' YEAR(4) TO MONTH,
 AsTimeStamp-INTERVAL '1' MONTH,
 AsTimeStamp-INTERVAL '2' MONTH,
 AsDate-INTERVAL '1-1' YEAR(2) TO MONTH,
 AsDate-INTERVAL '2-1' YEAR(4) TO MONTH,
 AsDate-INTERVAL '1' MONTH,
 AsDate-INTERVAL '2' MONTH,
 Unknown-INTERVAL '1 01:00:00.222' DAY TO SECOND(3),
 Unknown-INTERVAL '1 01:10' DAY TO MINUTE,
 Unknown-INTERVAL '1 1' DAY TO HOUR
FROM TIMES, UNKNOWN_TABLE;
Copy
|ASTIMESTAMP-INTERVAL'1-1'YEAR(2)TOMONTH|ASTIMESTAMP-INTERVAL'2-1'YEAR(4)TOMONTH|ASTIMESTAMP-INTERVAL'1'MONTH|ASTIMESTAMP-INTERVAL'2'MONTH|ASDATE-INTERVAL'1-1'YEAR(2)TOMONTH|ASDATE-INTERVAL'2-1'YEAR(4)TOMONTH|ASDATE-INTERVAL'1'MONTH|ASDATE-INTERVAL'2'MONTH|UNKNOWN-INTERVAL'101:00:00.222'DAYTOSECOND(3)|UNKNOWN-INTERVAL'101:10'DAYTOMINUTE|UNKNOWN-INTERVAL'11'DAYTOHOUR|
|---------------------------------------|---------------------------------------|----------------------------|----------------------------|----------------------------------|----------------------------------|-----------------------|-----------------------|---------------------------------------------|-----------------------------------|-----------------------------|
|2020-10-05 11:00:00.000                |2019-10-05 11:00:00.000                |2021-10-05 11:00:00.000     |2021-09-05 11:00:00.000     |2020-10-06 00:00:00.000           |2019-10-06 00:00:00.000           |2021-10-06 00:00:00.000|2021-09-06 00:00:00.000|2009-09-30 10:59:59.778                      |2009-09-30 10:50:00.000            |2009-09-30 11:00:00.000      |

Copy
Snowflake

Nota

Essa configuração foi usada no Snowflake

 ALTER SESSION SET TIMESTAMP_NTZ_OUTPUT_FORMAT= 'DD-MON-YY HH.MI.SS.FF6 AM';
ALTER SESSION SET DATE_OUTPUT_FORMAT= 'DD-MON-YY';
Copy
 -- DROP TABLE UNKNOWN_TABLE;
-- CREATE TABLE UNKNOWN_TABLE(Unknown timestamp);
-- INSERT  INTO UNKNOWN_TABLE VALUES (TO_TIMESTAMP('01/10/09, 12:00 P.M.', 'dd/mm/yy, hh:mi P.M.'));
CREATE OR REPLACE TABLE TIMES (
 AsTimeStamp TIMESTAMP(6),
 AsTimestampTwo TIMESTAMP(6),
 AsDate TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
 AsDateTwo TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/
 )
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;

INSERT INTO TIMES
VALUES (
TO_TIMESTAMP('05/11/21, 11:00 A.M.', 'dd/mm/yy, hh:mi A.M.'),
TO_TIMESTAMP('05/11/21, 10:00 A.M.', 'dd/mm/yy, hh:mi A.M.'),
TO_DATE('06/11/21', 'dd/mm/yy'),
TO_DATE('05/11/21', 'dd/mm/yy'));

--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "UNKNOWN_TABLE" **

SELECT
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!!
 AsTimeStamp - INTERVAL '1y, 1mm',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!!
 AsTimeStamp - INTERVAL '2y, 1mm',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!!
 AsTimeStamp - INTERVAL '1 month',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0095 - OPERATION BETWEEN INTERVAL TYPE AND DATE TYPE NOT SUPPORTED ***/!!!
 AsTimeStamp - INTERVAL '2 month',
 AsDate- INTERVAL '1y, 1mm',
 AsDate- INTERVAL '2y, 1mm',
 AsDate- INTERVAL '1 month',
 AsDate- INTERVAL '2 month',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!!
 Unknown - INTERVAL '1d, 01h, 00m, 00s, 222ms',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!!
 Unknown - INTERVAL '1d, 01h, 10m',
 !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN Unknown AND Interval ***/!!!
 Unknown - INTERVAL '1d, 1h'
FROM
 TIMES,
 UNKNOWN_TABLE;
Copy
|DATEDIFF_UDF(ASTIMESTAMP,'INTERVAL ''1-1'' YEAR(2) TO MONTH')|DATEDIFF_UDF(ASTIMESTAMP,'INTERVAL ''2-1'' YEAR(4) TO MONTH')|DATEDIFF_UDF(ASTIMESTAMP,'INTERVAL ''1'' MONTH')|DATEDIFF_UDF(ASTIMESTAMP,'INTERVAL ''2'' MONTH')|DATEDIFF_UDF(ASDATE,'INTERVAL ''1-1'' YEAR(2) TO MONTH')|DATEDIFF_UDF(ASDATE,'INTERVAL ''2-1'' YEAR(4) TO MONTH')|DATEDIFF_UDF(ASDATE,'INTERVAL ''1'' MONTH')|DATEDIFF_UDF(ASDATE,'INTERVAL ''2'' MONTH')|DATEDIFF_UDF(UNKNOWN,'INTERVAL ''1 01:00:00.222'' DAY TO SECOND(3)')|DATEDIFF_UDF(UNKNOWN,'INTERVAL ''1 01:10'' DAY TO MINUTE')|DATEDIFF_UDF(UNKNOWN,'INTERVAL ''1 1'' DAY TO HOUR')|
|-------------------------------------------------------------|-------------------------------------------------------------|------------------------------------------------|------------------------------------------------|--------------------------------------------------------|--------------------------------------------------------|-------------------------------------------|-------------------------------------------|--------------------------------------------------------------------|----------------------------------------------------------|----------------------------------------------------|
|2020-10-05 11:00:00.000                                      |2019-10-05 11:00:00.000                                      |2021-10-05 11:00:00.000                         |2021-09-05 11:00:00.000                         |2020-10-06                                              |2019-10-06                                              |2021-10-06                                 |2021-09-06                                 |2009-09-30 10:59:59.778                                             |2009-09-30 10:50:00.000                                   |2009-09-30 11:00:00.000                             |
Copy

Problemas conhecidos

1. INTERVAL - INTERVAL Operation is not supported

O Snowflake não é compatível com as operações INTERVAL - INTERVAL.

EWIs relacionados

  1. SSC-EWI-OR0036: Problemas de resolução de tipos, a operação aritmética pode não se comportar corretamente entre string e date.

  2. SSC-EWI-OR0095: Operação entre tipo de intervalo e tipo de data não suportada.

  3. SSC-FDM-0007: Elemento com dependências ausentes.

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