UDFs personnalisées SnowConvert¶
Description¶
Certaines fonctions et fonctionnalités intégrées d’Oracle peuvent ne pas être disponibles ou se comporter différemment dans Snowflake. Pour minimiser ces différences, certaines fonctions sont remplacées par des UDFs personnalisées SnowConvert.
Ces UDFs sont automatiquement créées lors de la migration, dans le dossier UDF Helper
, à l’intérieur du dossier Output
. Il y a un fichier par UDF personnalisée.
BFILENAME UDF¶
Description¶
Cette fonction prend le nom du répertoire et les paramètres du nom de fichier de Oracle BFILENAME()
comme STRING
et renvoie une concaténation de ceux-ci à l’aide de \
. Comme BFILE
est traduit en VARCHAR
, le résultat BFILENAME
est traité comme du texte.
Avertissement
\
doit être modifié pour correspondre au caractère de concaténation du fichier correspondant du système d’opérateur.
Surcharges UDF personnalisées¶
BFILENAME_UDF(string, string)¶
Concatène le chemin du répertoire et le nom du fichier.
Paramètres
DIRECTORYNAME : Une
STRING
qui représente le chemin du répertoire.FILENAME : Une
STRING
qui représente le nom du fichier.
-- 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
$$;
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;
-- Result
COL1 |
------------------+
[BFILE:myfile.png]|
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;
-- Result
COL1 |
----------------------+
mydirectory\myfile.png|
Problèmes connus¶
1. No access to the DBMS_LOB built-in package¶
Les types de données LOB n’étant pas pris en charge dans Snowflake, il n’existe pas d’équivalent pour les fonctions DBMS_LOB
et aucune solution de contournement n’a encore été mise en œuvre.
CAST_DATE UDF¶
Note
Certaines parties du code de sortie sont omises pour des raisons de clarté.
Description¶
Cette UDF personnalisée est ajoutée pour éviter les exceptions d’exécution causées par des différences de format lors de la conversion de chaînes en DATE
, à l’intérieur de procédures et de fonctions.
Surcharges UDF personnalisées¶
CAST_DATE_UDF(datestr)¶
Crée une DATE
à partir d’une STRING
.
Paramètres
DATESTR : Une
STRING
qui représente uneDATE
avec un format spécifique.
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')
$$;
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;
COL1 |
-----------------------+
1996-03-20 00:00:00.000|
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;
COL1 |
----------+
1996-03-20|
Problèmes connus¶
1. Oracle DATE contains TIMESTAMP¶
Tenez compte du fait que Oracle DATE
contient un TIMESTAMP
vide (00:00:00.000), alors que Snowflake DATE
n’en contient pas. SnowConvert permet de transformer DATE
en TIMESTAMP
avec l’indicateur SysdateAsCurrentTimestamp.
EWIs connexes ¶
SSC-FDM-OR0042 : Le type de date transformé en horodatage a un comportement différent
DATE_TO_JULIANDAYS_UDF¶
Description¶
La fonction DATE_TO_JULIANDAYS_UDF() prend une DATE et renvoie le nombre de jours depuis le 1er janvier, 4712 BC. Cette fonction est équivalente à Oracle TO_CHAR(DATE, “J”)
Surcharges UDF personnalisées¶
DATE_TO_JULIANDAYS_UDF(date)¶
Paramètres
INPUT\DATE :
DATE
de l’opération.
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
$$
;
Exemple d’utilisation¶
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;
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;
Problèmes connus¶
Aucun problème n’a été constaté.
EWIs connexes¶
SSC-FDM-OR0042 : Le type de date transformé en horodatage a un comportement différent
DATEADD UDF¶
Description¶
Cette UDF est utilisée comme modèle pour tous les cas où il y a un ajout entre un type DATE
ou TIMESTAMP
et un type FLOAT
.
Surcharges UDF personnalisées¶
DATEADD_UDF(date, float)¶
Paramètres
FIRST\PARAM : Première
DATE
de l’opération.SECOND\PARAM : Valeur
FLOAT
à ajouter.
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
$$;
DATEADD_UDF(float, date)¶
Paramètres
FIRST\PARAM : Valeur
FLOAT
à ajouter.SECOND\PARAM :
DATE
de l’opération.
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
$$;
DATEADD_UDF(timestamp, float)¶
Paramètres
FIRST\PARAM : Premier
TIMESTAMP
de l’opération.SECOND\PARAM : Valeur
FLOAT
à ajouter.
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)
$$;
DATEADD_UDF(float, timestamp)¶
Paramètres
FIRST\PARAM :
FLOAT
de l’opération.SECOND\PARAM :
TIMESTAMP
de l’opération.
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)
$$;
Exemple d’utilisation¶
Oracle¶
SELECT
TO_TIMESTAMP('03/08/2009, 12:47 AM', 'dd/mm/yy, hh:mi AM')+62.40750856543442
FROM DUAL;
|TO_TIMESTAMP('03/08/2009,12:47AM','DD/MM/YY,HH:MIAM')+62.40750856543442|
|-----------------------------------------------------------------------|
|2009-10-04 10:33:49.000 |
Snowflake¶
SELECT
PUBLIC.DATEADD_UDF(TO_TIMESTAMP('03/08/2009, 12:47 AM', 'dd/mm/yy, hh:mi AM'), 62.40750856543442)
FROM DUAL;
|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 |
Problèmes connus¶
1. Differences in time precision¶
Lorsqu’il existe des opérations entre des dates ou des horodatages et des valeurs flottantes, l’heure peut différer de celle d’Oracle. Une action a été lancée pour corriger ce problème.
EWIs connexes¶
Pas d’EWIs connexes.
DATEDIFF UDF¶
Note
Certaines parties du code de sortie sont omises pour des raisons de clarté.
Description¶
Cette UDF est utilisée comme modèle dans tous les cas où il y a soustraction entre DATE,
TIMESTAMP,
et tout autre type (sauf Intervalles).
Surcharges UDF personnalisées¶
DATEDIFF_UDF(date, date)¶
Paramètres
FIRST\PARAM : Première
DATE
de l’opération.SECOND_PARAM :
DATE
à soustraire.
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
$$;
DATEDIFF_UDF(date, timestamp)¶
Paramètres
FIRST\PARAM : Première
DATE
de l’opération.SECOND_PARAM :
TIMESTAMP
à soustraire.
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
$$;
DATEDIFF_UDF(date, integer)¶
Paramètres
FIRST\PARAM : Première
DATE
de l’opération.SECOND_PARAM :
INTEGER
à soustraire.
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)
$$;
DATEDIFF_UDF(timestamp, timestamp)¶
Paramètres
FIRST\PARAM : Premier
TIMESTAMP
de l’opération.SECOND_PARAM :
TIMESTAMP
à soustraire.
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)
$$;
DATEDIFF_UDF(timestamp, date)¶
Paramètres
FIRST\PARAM : Premier
TIMESTAMP
de l’opération.SECOND_PARAM :
DATE
à soustraire.
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)
$$;
DATEDIFF_UDF(timestamp, number)¶
Paramètres
FIRST\PARAM : Premier
TIMESTAMP
de l’opération.SECOND_PARAM :
NUMBER
à soustraire.
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)
$$;
Exemple d’utilisation¶
Note
L’inconnu est une colonne dont le type n’a pas pu être résolu, il peut s’agir d’un horodatage, d’une date entière ou d’un nombre.
Note
--disableDateAsTimestamp
Indicateur spécifiant si SYSDATE
doit être transformé en CURRENT_DATE
ou CURRENT_TIMESTAMP
. Cela affectera également toutes les colonnes DATE
qui seront transformées en 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;
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;
Problèmes connus¶
1. Functional differences for timestamps¶
Il arrive que la valeur de Snowflake renvoyée par l’UDF diffère de celle d’Oracle en raison de l’heure. Considérez l’exemple suivant
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;
|ASTIMESTAMP-UNKNOWN|
|-------------------|
|4417 23:0:0.0 |
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;
PUBLIC.DATEDIFF_UDF( ASTIMESTAMP, UNKNOWN)|
------------------------------------------+
4418|
EWIs connexes¶
SSC-EWI-OR0036 : Problèmes de résolution de types, l’opération arithmétique peut ne pas se comporter correctement entre une chaîne et une date.
SSC-FDM-OR0042 : Le type de date transformé en horodatage a un comportement différent.
JSON_VALUE UDF¶
Note
Certaines parties du code de sortie sont omises pour des raisons de clarté.
Description¶
Conformément à la documentation d’Oracle, cette fonction utilise l’expression de chemin SQL/JSON pour requérir des informations sur une partie d’une instance JSON. La valeur de retour est toujours une valeur scalaire, sinon la fonction renvoie NULL
par défaut.
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 ]
)
Le site JSON_VALUE_UDF est une implémentation Snowflake de la spécification JSONPath qui utilise une version modifiée de l’implémentation originale JavaScript développée par Stefan Goessner.
Modèles d’échantillons de sources¶
Données de configuration¶
Exécutez ces requêtes pour exécuter les requêtes de la section Modèles 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
}
}
}');
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
}
}
}');
Modèles 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;
JSON Path | Query 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;
JSON Path | Query 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 |
Problèmes connus¶
1. Returning Type Clause is not fully supported¶
Désormais, les seuls types pris en charge lors de la traduction de la fonctionnalité de la clause RETURNING TYPE sont VARCHAR2
, CLOB
et NUMBER
.
Pour tous les autres types pris en charge par la fonction originale JSON_VALUE, la fonction JSON_VALUE_UDF se comportera comme si aucune clause RETURNING TYPE n’avait été spécifiée.
Types non pris en charge :
DATE
TIMESTAMP [WITH TIME ZONE]
SDO_GEOMETRY
CUSTOM TYPE
2. ON MISMATCH Clause is not supported¶
Désormais, la clause ON MISMATCH n’est pas prise en charge et un avertissement EWI est placé à la place. Ainsi, le code traduit se comportera comme si aucune clause ON MISMATCH n’avait été spécifiée à l’origine.
3. Complex filters are not supported¶
Les filtres complexes comportant plus d’une expression renverront un résultat null, car ils ne sont pas pris en charge.
Par exemple, avec les mêmes données que précédemment, ce chemin JSON $.store.book[*]?(@.category == "reference").title
est pris en charge et renverra 'Sayings of the Century'
.
Cependant, $.store.book[*]?(@.category == "reference" && @.price < 10).title
renverra null
puisque plus d’une expression est utilisée dans le filtre.
EWIs connexes¶
SSC-EWI-0021 : Non pris en charge dans Snowflake.
JULIAN TO GREGORIAN DATE UDF¶
Description¶
Cette fonction définie par l’utilisateur (UDF) est utilisée pour transformer ou convertir le format de date julien en format de date grégorien. Les dates juliennes peuvent être reçues dans trois formats différents tels que JD Edwards World, astronomie ou le format ordinaire.
Surcharges UDF personnalisées¶
JULIAN_TO_GREGORIAN_DATE_UDF(julianDate, formatSelected)¶
Renvoie une chaîne avec le format de date grégorien YYYY-MM-DD.
Paramètres :¶
JulianDate : La date julienne qui doit être transmise. Il peut s’agir de CYYDDD (où C est le siècle) ou de YYYYDDD.
formatSelected : Représente le format dans lequel la date julienne doit être traitée. En outre, il s’agit d’un CHAR qui peut accepter les formats suivants :
Format available | Letter representation in CHAR | Description |
---|---|---|
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;
}
$$
;
Exemple d’utilisation¶
Oracle¶
select to_date('2020001', 'J') from dual;
| TO\_DATE('2020001', 'J') |
| ------------------------ |
| 18-JUN-18 |
| TO\_CHAR(TO\_DATE('2020001', 'J'), 'YYYY-MON-DD') |
| ------------------------------------------------- |
| 0818-JUN-18 |
_Remarque : La date doit être formatée afin de visualiser tous les chiffres de l’année
Snowflake¶
select
PUBLIC.JULIAN_TO_GREGORIAN_DATE_UDF('2020001', 'J')
from dual;
| JULIAN\_TO\_GREGORIAN\_DATE\_UDF('2020001', 'J') |
| ------------------------------------------------ |
| "0818-06-18" |
Problèmes connus¶
Tout autre format : Si la date julienne est formatée dans un autre format non pris en charge, il y aura des différences dans la sortie.
Les plages de dates de B.C. peuvent présenter des incohérences dues à des fonctions Snowflake non prises en charge pour les dates.
EWIs connexes¶
Pas d’EWIs connexes.
MONTHS BETWEEN UDF [DEPRECATED]¶
Cette UDF a été supprimée. La fonction actuelle pour Oracle MONTHS_BETWEEN() est Snowflake MONTHS_BETWEEN().
Description
MONTHS_BETWEEN
renvoie le nombre de mois entre les dates date1
and date2
. (Oracle MONTHS_BETWEEN Référence linguistique SQL)
MONTHS_BETWEEN(date1, date2)
Les fonctions Oracle MONTHS_BETWEEN
et Snowflake MONTHS_BETWEEN
présentent quelques différences fonctionnelles. Afin de minimiser ces différences et de mieux répliquer la fonction Oracle MONTHS_BETWEEN
, nous avons ajouté une UDF personnalisée.
Custom UDF overloads
MONTHS_BETWEEN_UDF(timestamp_ltz, timestamp_ltz)
Paramètres
FIRST\DATE : Premier
TIMESTAMP_LTZ
de l’opération.SECOND\DATE : Le deuxième
TIMESTAMP_LTZ
de l’opération.
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))
$$
;
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;
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|
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;
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|
Known Issues
1. Precision may differ from Oracle
Certains résultats peuvent différer quant au nombre de chiffres décimaux.
Related EWIs
Pas d’EWIs connexes.
REGEXP LIKE UDF
Note
Certaines parties du code de sortie sont omises pour des raisons de clarté.
Description¶
REGEXP_LIKE
permet de faire correspondre des expressions régulières. Cette condition évalue les chaînes à l’aide des caractères définis par l’ensemble de caractères d’entrée.(Oracle Language Reference REGEXP_LIKE Condition)
REGEXP_LIKE(source_char, pattern [, match_param ])
Oracle REGEXP_LIKE
et Snowflake REGEXP_LIKE
présentent quelques différences de fonctionnalité. Pour minimiser ces différences et mieux répliquer la fonction Oracle REGEXP_LIKE
, nous avons ajouté une UDF personnalisée. L’idée principale est d’échapper le symbole de la barre oblique inverse dans l’expression régulière lorsqu’il est exigé. Voici les caractères spéciaux qui doivent être échappés lorsqu’ils sont accompagnés d’une barre oblique inverse : 'd', 'D', 'w', 'W', 's', 'S', 'A', 'Z', 'n'
. En outre, l’expression de rétro-référence (correspond au même texte que celui qui a été trouvé le plus récemment par le groupe de capture « numéro spécifié ») doit être échappée.
Surcharges UDF personnalisées¶
REGEXP_LIKE_UDF(string, string)¶
Paramètres¶
COL : est l’expression de caractère qui sert de valeur de recherche.
PATTERN : est l’expression régulière.
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));
$$;
Oracle¶
Snowflake¶
REGEXP_LIKE_UDF(string, string, string)¶
Paramètres¶
COL : est l’expression de caractère qui sert de valeur de recherche.
PATTERN : est l’expression régulière.
MATCHPARAM : est une expression de caractère qui permet de modifier le comportement par défaut de la condition. Dans la table suivante, vous trouverez les caractères Oracle avec leur description et leur équivalent dans l’UDF.
Match Parameter | Description | UDF 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));
$$;
Oracle¶
Snowflake¶
Problèmes connus¶
1. UDF match parameter may not behave as expected¶
Tous les caractères disponibles dans le paramètre de correspondance d’Oracle n’ayant pas leur équivalent dans la fonction définie par l’utilisateur, le résultat de la requête peut présenter certaines différences fonctionnelles par rapport à Oracle.
2. UDF pattern parameter does not allow only “\” as a regular expression¶
Si le paramètre du modèle de l’expression régulière utilisée est uniquement « \ », une exception sera levée comme suit : JavaScript execution error: Uncaught SyntaxError: Invalid regular expression: //: \ at end of pattern in REGEXP_LIKE_UDF at “return COL.match(new RegExp(PATTERN));” position 17 stackstrace: REGEXP_LIKE_UDF
TIMESTAMP DIFF UDF¶
Description¶
Snowflake ne prend pas en charge l’opération d’addition entre les types de données TIMESTAMP
avec l’opérateur -
. Afin de répliquer cette fonctionnalité, nous avons ajouté une UDF personnalisée.
Surcharges UDF personnalisées¶
TIMESTAMP_DIFF_UDF(timestamp, timestamp)¶
Paramètres
LEFT\TS : Premier
TIMESTAMP
de l’opération.RIGHT\TS : Valeur
TIMESTAMP
à ajouter.
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
$$;
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;
COL1-COL2 |
---------------+
1461 12:0:0.0 |
-1460 12:0:0.0 |
30056 7:10:4.0 |
36172 23:10:0.0|
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;
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 |
Problèmes connus¶
1. TIMESTAMP format may differ from Oracle¶
Le format TIMESTAMP
peut différer de celui d’Oracle, veuillez tenir compte du paramètre TIMESTAMP_OUTPUT_FORMAT
lorsque vous travaillez avec des types de données TIMESTAMP
.
EWIs connexes¶
Pas d’EWIs connexes.
TRUNC (date) UDF¶
Description¶
La fonction TRUNC
(date) renvoie la date `` avec l’heure tronquée à l’unité spécifiée par le modèle de format fmt
. (Oracle TRUNC(date) Référence linguistique SQL)
TRUNC(date [, fmt ])
Les fonctions Oracle TRUNC
et Snowflake TRUNC
avec des arguments de type date présentent quelques différences fonctionnelles.
L’assistant TRUNC_UDF
sera ajouté pour traiter les cas suivants :
1. Le format n’est pas pris en charge par Snowflake.
2. Le format existe dans Snowflake mais fonctionne différemment.
3. L’outil ne peut pas déterminer le type de données du premier argument.
4. Le format est fourni sous la forme d’une colonne ou d’une expression et non d’un littéral.
Surcharges UDF personnalisées¶
TRUNC\UDF(date)¶
Applique une conversion DATE
explicite à l’horodatage d’entrée.
Paramètres
INPUT : L’horodatage avec le fuseau horaire (TIMESTAMP_LTZ) qui doit être tronqué.
Avertissement
Le paramètre par défaut de l’UDF est TIMESTAMP_LTZ
. Il peut être nécessaire de le remplacer par TIMESTAMP_TZ
ou TIMESTAMP_NTZ
pour correspondre à la valeur par défaut TIMESTAMP
utilisée par l’utilisateur.
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
$$;
Oracle¶
SELECT
TRUNC(
TO_TIMESTAMP ( '20-Mar-1996 21:01:11 ', 'DD-Mon-YYYY HH24:MI:SS' )
)
"Date" FROM DUAL;
Date |
-----------------------+
1996-03-20 00:00:00.000|
Snowflake¶
SELECT
TRUNC(
TO_TIMESTAMP ( '20-Mar-1996 21:01:11 ', 'DD-Mon-YYYY HH24:MI:SS' ), 'DD'
)
"Date" FROM DUAL;
DATE |
----------+
1996-03-20|
TRUNC\UDF(date, fmt)¶
Crée manuellement une nouvelle date à l’aide de la fonction DATE_FROM_PARTS()
, en fonction de la catégorie de format utilisée.
Paramètres
DATE_TO_TRUNC : L’horodatage avec le fuseau horaire (TIMESTAMP_LTZ) qui doit être tronqué.
DATE_FMT : Le format de la date en tant que VARCHAR. Les mêmes formats que ceux pris en charge par Oracle.
Avertissement
Le paramètre par défaut de l’UDF est TIMESTAMP_LTZ
. Il peut être nécessaire de le remplacer par TIMESTAMP_TZ
ou TIMESTAMP_NTZ
pour correspondre à la valeur par défaut TIMESTAMP
utilisée par l’utilisateur.
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)
$$
;
Scénarios de format TRUNC¶
Avertissement
Le format des résultats dépend des formats de sortie de DateTime configurés pour la base de données.
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;
+───────────────────────────────────────────────────────────────────────+
| "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 |
+───────────────────────────────────────────────────────────────────────+
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;
+─────────────────────────────────────────────────────────────────────────+
| "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 |
+─────────────────────────────────────────────────────────────────────────+
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;
+────────────────────────────────────────────────────────────────+
| "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 |
+────────────────────────────────────────────────────────────────+
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;
+────────────────────────────────────────────────────────────────────────+
| "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 |
+────────────────────────────────────────────────────────────────────────+
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;
+──────────────────────────────────────────────────────────────────────+
| "TRUNC(TO_DATE('20/04/202213:21:10','DD/MM/YYYYHH24:MI:SS'),'DAY')" |
+──────────────────────────────────────────────────────────────────────+
| 17-APR-22 |
| 17-APR-22 |
| 17-APR-22 |
+──────────────────────────────────────────────────────────────────────+
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;
+────────────────────────────────────────────────────────────────────────────+
| "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 |
+────────────────────────────────────────────────────────────────────────────+
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;
+─────────────────────────────────────────────────────────────────────+
| "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 |
+─────────────────────────────────────────────────────────────────────+
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;
+───────────────────────────────────────────────────────────────────────────+
| "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 |
+───────────────────────────────────────────────────────────────────────────+
Note
Lorsque la fonction TRUNC
est utilisée avec un format non pris en charge ou un paramètre qui ne peut être traité par SnowConvert. Pour éviter tout problème, le format est remplacé par un format valide ou TRUNC_UDF
est ajouté.
Problèmes connus¶
1. Oracle DATE contains TIMESTAMP¶
Tenez compte du fait que Oracle DATE
contient un TIMESTAMP
vide (00:00:00.000), alors que Snowflake DATE
n’en contient pas. SnowConvert permet de transformer DATE
en TIMESTAMP
avec l’indicateur SysdateAsCurrentTimestamp.
EWIs connexes ¶
Pas d’EWIs connexes.
TRUNC (number) UDF¶
Description¶
La fonction TRUNC
(number) renvoie n1
tronqué à n2
décimales. Si n2
est omis, n1
est tronqué à 0 chiffre. n2
peut être négatif pour tronquer (rendre nul) n2
chiffres à gauche de la virgule décimale. (Oracle TRUNC(number) Référence linguistique SQL)
TRUNC(n1 [, n2 ])
TRUNC_UDF pour les valeurs numériques sera ajouté pour traiter les cas où la première colonne a un type de données non reconnu.
Exemple :
SELECT TRUNC(column1) FROM DUAL;
Si la définition de column1
n’a pas été fournie à l’outil. Ensuite, TRUNC_UDF
sera ajouté et, lors de l’exécution, la surcharge de TRUNC_UDF
permettra de gérer les cas où il s’agit d’un type numérique ou d’un type de date.
Consultez la section TRUNC (DATE).
Les sections suivantes apportent la preuve que TRUNC_UDF
traitera des valeurs parfaitement numériques.
Surcharges UDF personnalisées¶
TRUNC_UDF(n1)¶
Appelle la fonction TRUNC
de Snowflake avec le numéro d’entrée. Cette surcharge permet de traiter les différents types de paramètres, au cas où les informations ne seraient pas disponibles lors de la migration.
Paramètres
INPUT :
NUMBER
qui doit être tronqué.
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)
$$;
Oracle¶
--TRUNC(NUMBER)
SELECT
TRUNC ( 1.000001 ),
TRUNC ( 15.79 ),
TRUNC ( -975.975 ),
TRUNC ( 135.135 )
FROM DUAL;
TRUNC(1.000001)|TRUNC(15.79)|TRUNC(-975.975)|TRUNC(135.135)|
---------------+------------+---------------+--------------+
1| 15| -975| 135|
Snowflake¶
--TRUNC(NUMBER)
SELECT
TRUNC ( 1.000001 ),
TRUNC ( 15.79 ),
TRUNC ( -975.975 ),
TRUNC ( 135.135 )
FROM DUAL;
TRUNC_UDF(1.000001)|TRUNC_UDF(15.79)|TRUNC_UDF(-975.975)|TRUNC_UDF(135.135)|
-------------------+----------------+-------------------+------------------+
1| 15| -975| 135|
TRUNC_UDF(n1, n2)¶
Appelle la fonction TRUNC
de Snowflake avec le nombre d’entrée et l’échelle. Cette surcharge permet de traiter les différents types de paramètres, au cas où les informations ne seraient pas disponibles lors de la migration.
Paramètres
INPUT :
NUMBER
qui doit être tronqué.SCALE : Représente le nombre de chiffres que la sortie inclura après la virgule décimale.
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)
$$;
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;
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|
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;
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
Problèmes connus¶
Aucun problème n’a été constaté.
EWIs connexes ¶
Pas d’EWIs connexes.
DATEADD UDF INTERVAL¶
Note
Certaines parties du code de sortie sont omises pour des raisons de clarté.
Description¶
Cette UDF est utilisée pour résoudre les opérations avec des intervalles comme :
INTERVAL + DATE
INTERVAL + TIMESTAMP
DATE + INTERVAL
DATE + TIMESTAMP
INTERVAL + UNKNOWN
UNKNOWN + INTERVAL
Note
Un type UNKNOWN est une colonne ou une expression dont le type n’a pas pu être résolu par Snow Convert. Cela arrive souvent lorsque les DDLs des tables ne sont pas incluses dans la migration ou lorsqu’il y a une expression ou une sous-requête qui peut renvoyer différents types de données.
Surcharges UDF personnalisées¶
DATEADD_UDF(string, date)¶
Paramètres
INTERVAL_VALUE : L’intervalle
String
de l’opération.D :
DATE
où l’intervalle sera ajouté.
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
$$;
DATEADD_UDF(date, string)¶
Paramètres
D :
DATE
où l’intervalle sera ajouté.INTERVAL_VALUE : L’intervalle
String
de l’opération.
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
$$;
DATEADD_UDF(string, timestamp)¶
Paramètres
INTERVAL_VALUE : L’intervalle
String
de l’opération.D :
TIMESTAMP
où l’intervalle sera ajouté.
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
$$;
DATEADD_UDF(timestamp, string)¶
Paramètres
D :
TIMESTAMP
où l’intervalle sera ajouté.INTERVAL_VALUE : L’intervalle
String
de l’opération.
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
$$;
Exemple d’utilisation¶
Note
--disableDateAsTimestamp
Indicateur spécifiant si SYSDATE
doit être transformé en CURRENT_DATE
ou CURRENT_TIMESTAMP
. Cela affectera également toutes les colonnes DATE
qui seront transformées en 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;
|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 |
Snowflake¶
Note
Cette configuration a été utilisée dans 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';
-- 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;
|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 |
Problèmes connus¶
1. INTERVAL + INTERVAL Operation is not supported¶
Snowflake ne prend pas en charge les opérations INTERVAL + INTERVAL.
EWIs connexes¶
SSC-EWI-OR0036 : Problèmes de résolution de types, l’opération arithmétique peut ne pas se comporter correctement entre une chaîne et une date.
SSC-EWI-OR0095 : L’opération entre le type d’intervalle et le type de date n’est pas prise en charge.
SSC-FDM-0007 : Élément avec des dépendances manquantes.
SSC-FDM-OR0042 : Le type de date transformé en horodatage a un comportement différent.
DATEDIFF UDF INTERVAL¶
Note
Certaines parties du code de sortie sont omises pour des raisons de clarté.
Description¶
Cette UDF est utilisée pour résoudre les opérations avec des intervalles comme :
INTERVAL - UNKNOWN
UNKNOWN - INTERVAL
DATE - INTERVAL
TIMESTAMP - INTERVAL
Note
Un type UNKNOWN est une colonne ou une expression dont le type n’a pas pu être résolu par Snow Convert. Cela arrive souvent lorsque les DDLs des tables ne sont pas incluses dans la migration ou lorsqu’il y a une expression ou une sous-requête qui peut renvoyer différents types de données.
Surcharges UDF personnalisées¶
DATEADD_DDIF(string, date)¶
Paramètres
INTERVAL_VALUE : L’intervalle
String
de l’opération.D :
DATE
où l’intervalle sera soustrait.
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
$$;
DATEADD_DIFF(date, string)¶
Paramètres
D :
DATE
où l’intervalle sera soustrait.INTERVAL_VALUE : L’intervalle
String
de l’opération.
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
$$;
DATEADD_DIFF(string, timestamp)¶
Paramètres
INTERVAL_VALUE : L’intervalle
String
de l’opération.D :
TIMESTAMP
où l’intervalle sera soustrait.
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
$$;
DATEADD_DIFF(timestamp, string)¶
Paramètres
D :
TIMESTAMP
où l’intervalle sera soustrait.INTERVAL_VALUE : L’intervalle
String
de l’opération.
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
$$;
Exemple d’utilisation¶
Note
--disableDateAsTimestamp
Indicateur spécifiant si SYSDATE
doit être transformé en CURRENT_DATE
ou CURRENT_TIMESTAMP
. Cela affectera également toutes les colonnes DATE
qui seront transformées en 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;
|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 |
Snowflake¶
Note
Cette configuration a été utilisée dans 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';
-- 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;
|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 |
Problèmes connus¶
1. INTERVAL - INTERVAL Operation is not supported¶
Snowflake ne prend pas en charge les opérations INTERVAL - INTERVAL.
EWIs connexes¶
SSC-EWI-OR0036 : Problèmes de résolution de types, l’opération arithmétique peut ne pas se comporter correctement entre une chaîne et une date.
SSC-EWI-OR0095 : L’opération entre le type d’intervalle et le type de date n’est pas prise en charge.
SSC-FDM-0007 : Élément avec des dépendances manquantes.
SSC-FDM-OR0042 : Le type de date transformé en horodatage a un comportement différent.