SnowConvert 사용자 지정 UDFs

설명

일부 Oracle 기본 제공 함수 및 함수는 Snowflake에서 사용 가능하지 않거나 다르게 작동할 수 있습니다. 이러한 차이를 최소화하기 위해 일부 함수는 SnowConvert 사용자 정의 UDFs 로 대체됩니다.

이러한 UDFs 는 마이그레이션 중에 UDF Helper 폴더의 Outpu 폴더 안에 자동으로 생성됩니다. 사용자 정의 UDF 당 1개의 파일이 있습니다.

BFILENAME UDF

설명

이 함수는 Oracle BFILENAME() 의 디렉터리 이름과 파일 이름 매개 변수를 STRING 으로 가져와 \ 를 사용하여 연결한 값을 반환합니다. BFILEVARCHAR 로 변환되므로 BFILENAME 결과는 텍스트로 처리됩니다.

경고

\ 을 해당 운영 체제 파일 연결 문자와 일치하도록 변경해야 합니다.

사용자 정의 UDF 오버로드

BFILENAME_UDF(string, string)

디렉터리 경로와 파일 이름을 연결합니다.

매개 변수

  1. DIRECTORYNAME: 디렉터리 경로를 나타내는 STRING.

  2. FILENAME: 파일 이름을 나타내는 STRING.

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

Known Issues

1. No access to the DBMS_LOB built-in package

LOB 데이터 타입은 Snowflake에서 지원되지 않으므로 DBMS_LOB 함수에 상응하는 기능이 없으며 아직 구현된 해결 방법이 없습니다.

CAST_DATE UDF

참고

출력 코드의 일부 부분은 명확성을 위해 생략되었습니다.

설명

이 사용자 정의 UDF 는 프로시저 및 함수 내부에서 DATE 로 문자열을 형 변환할 때 형식 차이로 인한 런타임 예외를 방지하기 위해 추가되었습니다.

사용자 정의 UDF 오버로드

CAST_DATE_UDF(datestr)

STRING 에서 DATE 를 생성합니다.

매개 변수

  1. DATESTR: 특정 형식의 DATE 를 나타내는 STRING.

 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

Known Issues

1. Oracle DATE contains TIMESTAMP

Oracle DATE 에는 빈 TIMESTAMP (00:00:00.000)이 포함되어 있는 반면, Snowflake DATE 에는 포함되어 있지 않다는 점에 유의하십시오. SnowConvert 를 사용하면 SysdateAsCurrentTimestamp 플래그를 통해 DATETIMESTAMP 로 변환할 수 있습니다.

관련 EWIs

  1. SSC-FDM-OR0042: 타임스탬프로 변환된 날짜 유형이 다른 동작을 합니다.

DATE_TO_JULIANDAYS_UDF

설명

DATE_TO_JULIANDAYS_UDF() 함수는 DATE 를 받고 BC 4712년 1월 1일 이후 일수를 반환합니다. 이 함수는 Oracle TO_CHAR(DATE,’J’)와 동일합니다

사용자 정의 UDF 오버로드

DATE_TO_JULIANDAYS_UDF(date)

매개 변수

  1. INPUT_DATE: 작업의 DATE.

 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

사용법 예

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

Known Issues

문제가 발견되지 않았습니다.

관련 EWIs

  • SSC-FDM-OR0042: 타임스탬프로 변환된 날짜 유형이 다른 동작을 합니다.

DATEADD UDF

설명

DATE 또는 TIMESTAMP 유형과 FLOAT 유형 사이에 추가되는 모든 경우의 템플릿으로 이 UDF 가 사용됩니다.

사용자 정의 UDF 오버로드

DATEADD_UDF(date, float)

매개 변수

  1. FIRST_PARAM: 작업의 첫 번째 DATE.

  2. SECOND_PARAM: 추가할 FLOAT.

 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)

매개 변수

  1. FIRST_PARAM: 추가할 FLOAT.

  2. SECOND_PARAM: 작업의 DATE.

 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)

매개 변수

  1. FIRST_PARAM: 작업의 첫 번째 TIMESTAMP.

  2. SECOND_PARAM: 추가할 FLOAT.

 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)

매개 변수

  1. FIRST_PARAM: 작업의 FLOAT.

  2. SECOND_PARAM: 작업의 TIMESTAMP.

 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

사용법 예

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

Known Issues

1. Differences in time precision

날짜 또는 타임스탬프와 부동 소수점 사이에 연산자가 있는 경우 시간은 Oracle과 다를 수 있습니다. 이 문제를 수정하기 위한 작업 항목이 있습니다.

관련 EWIs

EWIs 관련 없음.

DATEDIFF UDF

참고

출력 코드의 일부 부분은 명확성을 위해 생략되었습니다.

설명

이 UDF 는 DATE, TIMESTAMP, 및 다른 유형(간격 제외) 사이에 빼기가 있는 모든 경우의 템플릿으로 사용됩니다.

사용자 정의 UDF 오버로드

DATEDIFF_UDF(date, date)

매개 변수

  1. FIRST_PARAM: 작업의 첫 번째 DATE.

  2. SECOND_PARAM: 빼야 할 DATE.

 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)

매개 변수

  1. FIRST_PARAM: 작업의 첫 번째 DATE.

  2. SECOND_PARAM: 빼야 할 TIMESTAMP.

 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)

매개 변수

  1. FIRST_PARAM: 작업의 첫 번째 DATE.

  2. SECOND_PARAM: 빼야 할 INTEGER.

 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)

매개 변수

  1. FIRST_PARAM: 작업의 첫 번째 TIMESTAMP.

  2. SECOND_PARAM: 빼야 할 TIMESTAMP.

 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)

매개 변수

  1. FIRST_PARAM: 작업의 첫 번째 TIMESTAMP.

  2. SECOND_PARAM: 빼야 할 DATE.

 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)

매개 변수

  1. FIRST_PARAM: 작업의 첫 번째 TIMESTAMP.

  2. SECOND_PARAM: 빼야 할 NUMBER.

 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

사용법 예

참고

알 수 없음은 유형을 확인할 수 없는 열로, 타임스탬프, 날짜 정수 또는 숫자 등이 될 수 있습니다.

참고

--disableDateAsTimestamp

SYSDATECURRENT_DATE 또는 CURRENT_TIMESTAMP 로 변환할지 여부를 나타내는 플래그입니다. 이는 TIMESTAMP 로 변환되는 모든 DATE 열에도 영향을 미칩니다.

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

Known Issues

1. Functional differences for timestamps

일부 경우 UDF 에서 반환되는 Snowflake 값은 시간으로 인해 Oracle 값과 다를 수 있습니다. 다음 예를 고려해 보겠습니다.

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

  1. SSC-EWI-OR0036: 유형 확인 문제, 문자열과 날짜 사이에서 산술 작업이 올바르게 작동하지 않을 수 있습니다.

  2. SSC-FDM-OR0042: 타임스탬프로 변환된 날짜 유형은 동작이 다릅니다.

JSON_VALUE UDF

참고

출력 코드의 일부 부분은 명확성을 위해 생략되었습니다.

설명

Oracle 설명서에 따르면 이 함수는SQL/JSON 경로 식 을 사용하여 JSON 인스턴스의 일부에 대한 정보를 요청합니다. 반환 값은 항상 스칼라 값이며, 그렇지 않으면 함수는 기본적으로 NULL 을 반환합니다.

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

JSON_VALUE_UDF 는 Stefan Goessner 가 개발한 원래 JavaScript 구현의 수정된 버전을 사용하는 JSONPath 사양의 Snowflake 구현입니다.

샘플 소스 패턴

설정 데이터

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

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

Known Issues

1. Returning Type Clause is not fully supported

이제 RETURNING TYPE 절의 기능을 변환할 때 지원되는 유일한 유형은 VARCHAR2, CLOBNUMBER 입니다.

원래 JSON_VALUE 함수가 지원하는 다른 모든 유형의 경우 JSON_VALUE_UDF 는 RETURNING TYPE 절을 지정하지 않은 것처럼 작동합니다.

지원되지 않는 유형:

  • DATE

  • TIMESTAMP [WITH TIME ZONE]

  • SDO_GEOMETRY

  • CUSTOM TYPE

2. ON MISMATCH Clause is not supported

이제 ON MISMATCH 절은 지원되지 않으며 대신 EWI 경고가 표시됩니다. 따라서 변환된 코드는 원래 ON MISMATCH 절은 지정되지 않은 것처럼 작동합니다.

3. Complex filters are not supported

식이 2개 이상인 복잡한 필터는 지원되지 않으므로 null을 반환합니다.

예를 들어, 이전과 동일한 데이터로 JSON 경로 $.store.book[*]?(@.category == "reference").title 이 지원되며 'Sayings of the Century' 를 반환합니다.

그러나 $.store.book[*]?(@.category == "reference" && @.price < 10).title 은 필터에 2개 이상의 식이 사용되었으므로 null 을 반환합니다.

관련 EWIs

  1. SSC-EWI-0021: Snowflake에서는 지원되지 않습니다.

JULIAN TO GREGORIAN DATE UDF

설명

이 사용자 정의 함수(UDF)는 율리우스력 날짜 형식을 그레고리력 날짜 형식으로 변환하거나 형 변환하는 데 사용됩니다. 율리우스력 날짜는 JD Edwards World, 천문학 또는 일반 형식 등 세 가지 형식으로 받을 수 있습니다.

사용자 정의 UDF 오버로드

JULIAN_TO_GREGORIAN_DATE_UDF(julianDate, formatSelected)

그레고리력 날짜 형식 YYYY-MM-DD 의 문자열을 반환합니다.

매개 변수:

JulianDate: 형 변환할 율리우스력 날짜. CYYDDD (여기서 C는 세기) 또는 YYYYDDD 일 수 있습니다.

formatSelected: 율리우스력 날짜를 처리할 형식을 나타냅니다. 또한 이는 CHAR 이며 다음 형식을 사용할 수 있습니다.

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

사용법 예

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
  • 참고: 날짜는 연도의 모든 자릿수를 시각화하기 위해 형식이 지정되어야 합니다

Snowflake

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

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

문제 파악

  1. 기타 형식: 율리우스력 날짜가 지원되지 않는 다른 형식으로 지정되면 출력에 차이가 발생합니다.

  2. 날짜에 대해 지원되지 않는 Snowflake 함수로 인해 기원전 날짜의 범위가 불일치할 수 있습니다.

관련 EWIs

EWIs 관련 없음.

MONTHS BETWEEN UDF [DEPRECATED]

이 UDF 는 더 이상 사용되지 않습니다. Oracle MONTHS_BETWEEN()의 현재 변환은 Snowflake MONTHS_BETWEEN()입니다.

Description

MONTHS_BETWEEN 은 날짜 date1date2 사이의 개월 수를 반환합니다. (Oracle MONTHS_BETWEEN SQL Language Reference)

MONTHS_BETWEEN(date1, date2)
Copy

Oracle MONTHS_BETWEEN 과 Snowflake MONTHS_BETWEEN 함수에는 몇 가지 기능적 차이가 있으며, 이러한 차이를 최소화하고 Oracle MONTHS_BETWEEN 함수를 더 잘 복제하기 위해 사용자 정의 UDF 를 추가했습니다.

Custom UDF overloads

MONTHS_BETWEEN_UDF(timestamp_ltz, timestamp_ltz)

매개 변수

  1. FIRST_DATE: 작업의 첫 번째 TIMESTAMP_LTZ.

  2. SECOND_DATE: 연산자의 두 번째 TIMESTAMP_LTZ.

 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

일부 결과는 소수점 자릿수가 다를 수 있습니다.

Related EWIs

관련 EWIs 없음.

REGEXP LIKE UDF

참고

출력 코드의 일부 부분은 명확성을 위해 생략되었습니다.

설명

REGEXP_LIKE 는 정규식 매칭을 수행합니다. 이 조건은 입력 문자 세트에 정의된 문자를 사용하여 문자열을 평가합니다. (Oracle Language Regerence REGEXP_LIKE 조건)

 REGEXP_LIKE(source_char, pattern [, match_param ])
Copy

Oracle REGEXP_LIKE 과 Snowflake REGEXP_LIKE 조건에는 몇 가지 기능적 차이가 있으며, 이러한 차이를 최소화하고 Oracle REGEXP_LIKE 함수를 더 잘 복제하기 위해 사용자 정의 UDF 를 추가했습니다. 주요 아이디어는 백슬래시 기호가 필수 정규식에서 백슬래시 기호를 이스케이프하는 것입니다. 백슬래시와 함께 올 때 이스케이프해야 하는 특수 문자는 'd', 'D', 'w', 'W', 's', 'S', 'A', 'Z', 'n' 입니다. 또한 역참조 식 (‘번호 지정’ 캡처 그룹에서 가장 최근에 일치시킨 텍스트와 동일한 텍스트와 일치)은 이스케이프 처리해야 합니다.

사용자 정의 UDF 오버로드

REGEXP_LIKE_UDF(string, string)

매개 변수
  1. COL 은 검색 값으로 사용되는 문자 식입니다.

  2. PATTERN 은 정규식입니다.

 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)

매개 변수
  1. COL 은 검색 값으로 사용되는 문자 식입니다.

  2. PATTERN 은 정규식입니다.

  3. MATCHPARAM 은 조건의 기본 일치 동작을 변경할 수 있는 문자 식입니다. 다음 테이블에는 설명과 함께 UDF 에 있는 Oracle 문자와 이에 상응하는 문자가 나와 있습니다.

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

Known Issues

1. UDF match parameter may not behave as expected

Oracle 일치 매개 변수에서 사용 가능한 모든 문자가 사용자 정의 함수에는 해당 문자가 없기 때문에 쿼리 결과는 Oracle과 비교하여 일부 기능적 차이가 있을 수 있습니다.

2. UDF pattern parameter does not allow only ‘\’ as a regular expression

패턴 매개 변수로 사용된 정규 식이 ‘\’ 뿐인 경우, 다음과 같은 예외 발생: 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

설명

Snowflake는 - 피연산자가 있는 TIMESTAMP 데이터 타입 간의 덧셈 작업을 지원하지 않습니다. 이 기능을 복제하기 위해 사용자 정의 UDF 를 추가했습니다.

사용자 정의 UDF 오버로드

TIMESTAMP_DIFF_UDF(timestamp, timestamp)

매개 변수

  1. LEFT_TS: 작업의 첫 번째 TIMESTAMP.

  2. RIGHT_TS: 추가할 TIMESTAMP.

 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

Known Issues

1. TIMESTAMP format may differ from Oracle

TIMESTAMP 형식은 Oracle과 다를 수 있으므로 TIMESTAMP 데이터 타입으로 작업할 때는 TIMESTAMP_OUTPUT_FORMAT 설정 을 사용하는 것이 좋습니다.

관련 EWIs

관련 EWIs 없음.

TRUNC (날짜) UDF

설명

TRUNC (date) 함수는 형식 모델 fmt 가 지정된 단위로 잘린 하루의 시간 부분이 포함된 date 를 반환합니다. (Oracle TRUNC(date) SQL Language Reference)

TRUNC(date [, fmt ])
Copy

날짜 인자가 있는 Oracle TRUNC 및 Snowflake TRUNC 함수에는 몇 가지 기능적 차이가 있습니다.

TRUNC_UDF 도우미가 추가되어 다음과 같은 경우를 처리할 수 있습니다.

1. 이 형식은 Snowflake에서 지원되지 않습니다.

2. 형식은 Snowflake에 존재하지만 작동 방식은 다릅니다.

3. 이 도구는 첫 번째 인자의 데이터 타입을 확인할 수 없습니다.

4. 형식은 리터럴이 아닌 열 또는 식으로 제공됩니다.

사용자 정의 UDF 오버로드

TRUNC_UDF(date)

입력된 타임스탬프에 명시적으로 DATE형변환을적용합니다.

매개 변수

  1. INPUT: 잘라야 하는 타임존(TIMESTAMP_LTZ) 이 있는 타임스탬프입니다.

경고

UDF 의 기본 매개 변수는 TIMESTAMP_LTZ 입니다. 사용자가 사용하는 기본값 TIMESTAMP 와 일치하도록 TIMESTAMP_TZ 또는 TIMESTAMP_NTZ 로 변경해야 할 수 있습니다.

 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)

사용된 형식 범주에 따라 DATE_FROM_PARTS() 함수 를 사용하여 새 날짜를 수동으로 생성합니다.

매개 변수

  1. DATE_TO_TRUNC: 잘라야 하는 타임존(TIMESTAMP_LTZ) 이 있는 타임스탬프입니다.

  2. DATE_FMT: VARCHAR 과 같은 날짜 형식입니다. Oracle에서 지원되는 형식 과 동일합니다.

경고

UDF 의 기본 매개 변수는 TIMESTAMP_LTZ 입니다. 사용자가 사용하는 기본값 TIMESTAMP 와 일치하도록 TIMESTAMP_TZ 또는 TIMESTAMP_NTZ 로 변경해야 할 수 있습니다.

 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

TRUNC 형식 시나리오

경고

결과 형식은 데이터베이스에 구성된 DateTime 출력 형식에 따라 달라집니다.

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

참고

TRUNC 함수가 지원되지 않는 형식 또는 SnowConvert 에서 처리할 수 없는 매개 변수와 함께 사용되는 경우. 문제를 방지하기 위해 형식을 유효한 형식으로 바꾸거나 TRUNC_UDF 를 추가합니다.

Known Issues

1. Oracle DATE contains TIMESTAMP

Oracle DATE 에는 빈 TIMESTAMP (00:00:00.000)이 포함되어 있는 반면, Snowflake DATE 에는 포함되어 있지 않다는 점에 유의하십시오. SnowConvert 를 사용하면 SysdateAsCurrentTimestamp 플래그를 통해 DATETIMESTAMP 로 변환할 수 있습니다.

관련 EWIs

관련 EWIs 없음.

TRUNC (숫자) UDF

설명

TRUNC (숫자) 함수는 n1n2 자리수로 반올림한 소수점을 반환합니다. n2 를 생략하면 n1 이 0자리로 잘립니다. n2 는 음수로 설정하여 소수점 왼쪽의 n2 자릿수를 잘라내어 0으로 만들 수 있습니다. (Oracle TRUNC(number) SQL Language Reference)

TRUNC(n1 [, n2 ])
Copy

첫 번째 열의 데이터 타입이 인식되지 않는 경우를 처리하기 위해 숫자 값에 TRUNC_UDF 가 추가됩니다.

예:

 SELECT TRUNC(column1) FROM DUAL;
Copy

column1 의 정의가 도구에 제공되지 않은 경우. 그런 다음 TRUNC_UDF 가 추가되고 실행 시간에 TRUNC_UDF 의 오버로드가 숫자 또는 날짜 유형인 경우 케이스를 처리합니다.

TRUNC (DATE) 섹션을 참조하십시오.

다음 섹션에서는 TRUNC_UDF 가 숫자 값을 완벽하게 처리함을 증명합니다.

사용자 정의 UDF 오버로드

TRUNC_UDF(n1)

입력 번호로 Snowflake TRUNC 함수 를 호출합니다. 이 오버로드는 마이그레이션 중에 정보를 사용할 수 없는 경우를 대비하여 다양한 유형의 매개 변수 시나리오를 처리하기 위해 존재합니다.

매개 변수

  1. INPUT: 잘라내야 하는 NUMBER.

 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)

입력 숫자와 스케일을 사용하여 Snowflake TRUNC 함수 를 호출합니다. 이 오버로드는 마이그레이션 중에 정보를 사용할 수 없는 경우를 대비하여 다양한 유형의 매개 변수 시나리오를 처리하기 위해 존재합니다.

매개 변수

  1. INPUT: 잘라내야 하는 NUMBER.

  2. SCALE: 출력에 소수점 뒤에 포함될 자릿수를 나타냅니다.

 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

Known Issues

문제가 발견되지 않았습니다.

관련 EWIs

관련 EWIs 없음.

DATEADD UDF INTERVAL

참고

출력 코드의 일부 부분은 명확성을 위해 생략되었습니다.

설명

이 UDF 는 다음과 같은 간격으로 작업을 해결하는 데 사용됩니다.

  • INTERVAL + DATE

  • INTERVAL + TIMESTAMP

  • DATE + INTERVAL

  • DATE + TIMESTAMP

  • INTERVAL + UNKNOWN

  • UNKNOWN + INTERVAL

참고

UNKNOWN 유형은 Snow Convert에서 유형을 확인할 수 없는 열 또는 표현식으로, 테이블에 대한 DDLs 이 마이그레이션에 포함되지 않았거나 다른 데이터 타입을 반환할 수 있는 식 또는 하위 쿼리가 있을 때 발생합니다.

사용자 정의 UDF 오버로드

DATEADD_UDF(string, date)

매개 변수

  1. INTERVAL_VALUE: 작업의 문자열 간격입니다.

  2. D: 간격이 추가될 DATE 입니다.

 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)

매개 변수

  1. D: 간격이 추가될 DATE 입니다.

  2. INTERVAL_VALUE: 작업의 문자열 간격입니다.

 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)

매개 변수

  1. INTERVAL_VALUE: 작업의 문자열 간격입니다.

  2. D: 간격이 추가될 TIMESTAMP 입니다.

 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)

매개 변수

  1. D: 간격이 추가될 TIMESTAMP 입니다.

  2. INTERVAL_VALUE: 작업의 문자열 간격입니다.

 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

사용법 예

참고

--disableDateAsTimestamp

SYSDATECURRENT_DATE 또는 CURRENT_TIMESTAMP 로 변환할지 여부를 나타내는 플래그입니다. 이는 TIMESTAMP 로 변환되는 모든 DATE 열에도 영향을 미칩니다.

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

참고

이 구성은 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

Known Issues

1. INTERVAL + INTERVAL Operation is not supported

Snowflake는 INTERVAL + INTERVAL 연산을 지원하지 않습니다.

관련 EWIs

  1. SSC-EWI-OR0036: 유형 확인 문제, 문자열과 날짜 사이에서 산술 작업이 올바르게 작동하지 않을 수 있습니다.

  2. SSC-EWI-OR0095: 간격 유형과 날짜 유형 간의 연산자는 지원되지 않습니다.

  3. SSC-FDM-0007: 종속성이 누락된 요소.

  4. SSC-FDM-OR0042: 타임스탬프로 변환된 날짜 유형은 동작이 다릅니다.

DATEDIFF UDF INTERVAL

참고

출력 코드의 일부 부분은 명확성을 위해 생략되었습니다.

설명

이 UDF 는 다음과 같은 간격으로 작업을 해결하는 데 사용됩니다.

  • INTERVAL - UNKNOWN

  • UNKNOWN - INTERVAL

  • DATE - INTERVAL

  • TIMESTAMP - INTERVAL

참고

UNKNOWN 유형은 Snow Convert에서 유형을 확인할 수 없는 열 또는 표현식으로, 테이블에 대한 DDLs 이 마이그레이션에 포함되지 않았거나 다른 데이터 타입을 반환할 수 있는 식 또는 하위 쿼리가 있을 때 발생합니다.

사용자 정의 UDF 오버로드

DATEADD_DDIF(string, date)

매개 변수

  1. INTERVAL_VALUE: 작업의 문자열 간격입니다.

  2. D: DATE, 여기서 간격을 뺍니다.

 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)

매개 변수

  1. D: DATE, 여기서 간격을 뺍니다.

  2. INTERVAL_VALUE: 작업의 문자열 간격입니다.

 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)

매개 변수

  1. INTERVAL_VALUE: 작업의 문자열 간격입니다.

  2. D: TIMESTAMP, 여기서 간격을 뺍니다.

 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)

매개 변수

  1. D: TIMESTAMP, 여기서 간격을 뺍니다.

  2. INTERVAL_VALUE: 작업의 문자열 간격입니다.

 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

사용법 예

참고

--disableDateAsTimestamp

SYSDATECURRENT_DATE 또는 CURRENT_TIMESTAMP 로 변환할지 여부를 나타내는 플래그입니다. 이는 TIMESTAMP 로 변환되는 모든 DATE 열에도 영향을 미칩니다.

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

참고

이 구성은 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

Known Issues

1. INTERVAL - INTERVAL Operation is not supported

Snowflake는 INTERVAL - INTERVAL 연산을 지원하지 않습니다.

관련 EWIs

  1. SSC-EWI-OR0036: 유형 확인 문제, 문자열과 날짜 사이에서 산술 작업이 올바르게 작동하지 않을 수 있습니다.

  2. SSC-EWI-OR0095: 간격 유형과 날짜 유형 간의 연산자는 지원되지 않습니다.

  3. SSC-FDM-0007: 종속성이 누락된 요소.

  4. SSC-FDM-OR0042: 타임스탬프로 변환된 날짜 유형은 동작이 다릅니다.