SnowConvert: Teradata Built-in Functions¶

 SELECT '"'||cast(cast(12 as BYTEINT) as varchar(10))||'"';

 SELECT
'"'|| LEFT(TO_VARCHAR(cast(12 as BYTEINT), 'TM'), 10) ||'"';

 SELECT '"'||cast(cast(123 as SMALLINT) as varchar(10))||'"';

 SELECT
'"'|| LEFT(TO_VARCHAR(CAST(123 AS SMALLINT), 'TM'), 10) ||'"';

 SELECT '"'||cast(cast(12345 as INTEGER) as varchar(10))||'"';

 SELECT
'"'|| LEFT(TO_VARCHAR(CAST(12345 AS INTEGER), 'TM'), 10) ||'"';

 SELECT '"'||cast(cast(12345 as BIGINT) as varchar(10))||'"';

 SELECT
       '"'|| LEFT(TO_VARCHAR(CAST(12345 AS BIGINT), 'TM'), 10) ||'"';

 SELECT '"'||cast(cast(12345 as DECIMAL) as varchar(10))||'"',
       '"'||cast(cast(12345 as DECIMAL(12, 2)) as varchar(10))||'"';

 SELECT
'"'|| LEFT(TO_VARCHAR(CAST(12345 AS DECIMAL), 'TM.'), 10) ||'"',
'"'|| LEFT(TO_VARCHAR(CAST(12345 AS DECIMAL(12, 2)), 'TM'), 10) ||'"';

'"'|| LEFT(TO_VARCHAR(CAST(12345 AS DECIMAL), 'TM.'), 10) ||'"'	'"'|| LEFT(TO_VARCHAR(CAST(12345 AS DECIMAL(12, 2)), 'TM'), 10) ||'"'
"12345."	"12345.00"    

 SELECT
'"'|| LEFT(TO_VARCHAR(CAST(-0.1 AS DECIMAL(12, 2)), 'TM'), 10) ||'"' AS column1,
'"'|| LEFT(TO_VARCHAR(CAST(0.1 AS DECIMAL(12, 2)), 'TM'), 10) ||'"' AS column2;

 SELECT TIMESTAMP '2022-10-15 10:30:00' + CAST ('12:34:56.78' AS INTERVAL HOUR(2) TO SECOND(2)) AS VARCHAR_TO_INTERVAL,
TIMESTAMP '2022-10-15 10:30:00' + CAST(-5 AS INTERVAL YEAR(4)) AS NUMBER_TO_INTERVAL,
CAST('07:00' AS INTERVAL HOUR(2) TO MINUTE) AS OUTSIDE_DATETIME_OPERATION;

 SELECT
TIMESTAMP '2022-10-15 10:30:00' + INTERVAL '12 HOUR, 34 MINUTE, 56 SECOND, 780000 MICROSECOND' AS VARCHAR_TO_INTERVAL,
TIMESTAMP '2022-10-15 10:30:00' + INTERVAL '-5 YEAR' AS NUMBER_TO_INTERVAL,
'07:00' AS OUTSIDE_DATETIME_OPERATION;

 SELECT TIMESTAMP '2022-10-15 10:30:00' + CAST('20 ' || '10' AS INTERVAL DAY TO HOUR) AS DATETIME_OPERATION,
CAST('20 ' || '10' AS INTERVAL DAY TO HOUR) AS OUTSIDE_DATETIME_OPERATION;

 SELECT
PUBLIC.DATETIMEINTERVALADD_UDF(TIMESTAMP '2022-10-15 10:30:00', CAST('20 ' || '10' AS VARCHAR(21)), 'DAY', '+') AS DATETIME_OPERATION,
CAST('20 ' || '10' AS VARCHAR(21)) AS OUTSIDE_DATETIME_OPERATION;

 SELECT
TIMESTAMP '2022-10-15 10:30:00' + CAST(INTERVAL '5999' MINUTE AS INTERVAL DAY TO HOUR) AS DATETIME_OPERATION,
CAST(INTERVAL '5999' MINUTE AS INTERVAL DAY TO HOUR) AS OUTSIDE_DATETIME_OPERATION;

 SELECT
PUBLIC.DATETIMEINTERVALADD_UDF(
TIMESTAMP '2022-10-15 10:30:00', PUBLIC.INTERVALTOINTERVAL_UDF('5999', 'MINUTE', 'MINUTE', 'DAY', 'HOUR'), 'DAY', '+') AS DATETIME_OPERATION,
PUBLIC.INTERVALTOINTERVAL_UDF('5999', 'MINUTE', 'MINUTE', 'DAY', 'HOUR') AS OUTSIDE_DATETIME_OPERATION;

  SELECT COALESCE(125, 'hello', cast(850 as format '-999'));

 SELECT
 COALESCE(TO_VARCHAR(125), 'hello', TO_VARCHAR(850, '9000'));

 SELECT COALESCE(cast(TIMESTAMP '2021-09-14 10:14:59' as format 'HH:MI:SSBDD-MM-YYYY'), current_date);

 SELECT
 COALESCE(TO_DATE(TIMESTAMP '2021-09-14 10:14:59' !!!RESOLVE EWI!!! /*** SSC-EWI-TD0025 - OUTPUT FORMAT 'HH:MI:SSBDD-MM-YYYY' NOT SUPPORTED. ***/!!!), CURRENT_DATE());

 SELECT current_timestamp(4) at local;

 SELECT
current_timestamp(4);

 SELECT
    DAYOFMONTH(DATE'2022-12-22'),
    DAYOFMONTH(DATE'2022-12-22'),
    DAYOFMONTH(DATE'2022-12-22'),
    DAYOFMONTH(DATE'2022-12-22');

 SELECT DAYNUMBER_OF_MONTH (DATE'2022-12-22', 'ISO');

 SELECT
PUBLIC.DAYNUMBER_OF_MONTH_UDF(DATE'2022-12-22');

 SELECT
--returns '23067'
TO_NUMBER('5A1B', 'XXXX'),
--returns 'Z\ESC '
!!!RESOLVE EWI!!! /*** SSC-EWI-0031 - FROM_BYTES FUNCTION NOT SUPPORTED ***/!!!
FROM_BYTES(TO_BINARY('5A3F'), 'ASCII'),
TO_BINARY('5A1B', 'HEX'); -- returns '5A1B'

 SET QUERY_BAND = 'hola=hello;adios=bye;' FOR SESSION;

 ALTER SESSION SET QUERY_TAG = 'hola=hello;adios=bye;';

 SELECT
GETQUERYBANDVALUE('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 0, 'account') as Example1,
GETQUERYBANDVALUE('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 1, 'account') as Example2,
GETQUERYBANDVALUE('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 2, 'account') as Example3,
GETQUERYBANDVALUE('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 3, 'account') as Example4,
GETQUERYBANDVALUE('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 0, 'role') as Example5,
GETQUERYBANDVALUE('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 1, 'role') as Example6;

 SELECT
GETQUERYBANDVALUE_UDF('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 0, 'account') as Example1,
GETQUERYBANDVALUE_UDF('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 1, 'account') as Example2,
GETQUERYBANDVALUE_UDF('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 2, 'account') as Example3,
GETQUERYBANDVALUE_UDF('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 3, 'account') as Example4,
GETQUERYBANDVALUE_UDF('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 0, 'role') as Example5,
GETQUERYBANDVALUE_UDF('=T> user=Mark;account=Mark200; =S> user=Sara;account=SaraDB;role=DbAdmin =P> user=Peter;account=Peter3;', 1, 'role') as Example6;

 SELECT
GETQUERYBANDVALUE(2, 'hola') as Example1,
GETQUERYBANDVALUE(2, 'adios') as Example2;

 SELECT
GETQUERYBANDVALUE_UDF('hola') as Example1,
GETQUERYBANDVALUE_UDF('adios') as Example2;

 SELECT JSON_CHECK('{"key": "value"}');

 SELECT
IFNULL(CHECK_JSON('{"key": "value"}'), 'OK');

 SELECT CASE WHEN JSON_CHECK('{}') = 'OK' then 'OKK' ELSE 'NOT OK' END;

 SELECT
CASE
WHEN IFNULL(CHECK_JSON('{}'), 'OK') = 'OK'
THEN 'OKK' ELSE 'NOT OK'
END;

 SELECT
    Store.JSONExtract('$..author') as AllAuthors,
    Store.JSONExtractValue('$..book[2].title') as ThirdBookTitle,
    Store.JSONExtractLargeValue('$..book[2].price') as ThirdBookPrice
FROM BookStores;

 SELECT
    JSON_EXTRACT_UDF(Store, '$..author', FALSE) as AllAuthors,
    JSON_EXTRACT_UDF(Store, '$..book[2].title', TRUE) as ThirdBookTitle,
    JSON_EXTRACT_UDF(Store, '$..book[2].price', TRUE) as ThirdBookPrice
    FROM
    BookStores;

 create table myJsonTable(
 col1 integer,
 col2 JSON(1000)
 );
 
 
insert into myJsonTable values(1, 
new json('{
"name": "Matt",
"age" : 30,
"songs" : [
	{"name" : "Late night", "genre" : "Jazz"},
	{"name" : "Wake up", "genre" : "Rock"},
	{"name" : "Who am I", "genre" : "Rock"},
	{"name" : "Raining", "genre" : "Blues"}
]
}'));

 CREATE OR REPLACE TABLE myJsonTable (
 col1 integer,
 col2 VARIANT
 )
 COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

INSERT INTO myJsonTable
VALUES (1, TO_JSON(PARSE_JSON('{
"name": "Matt",
"age" : 30,
"songs" : [
	{"name" : "Late night", "genre" : "Jazz"},
	{"name" : "Wake up", "genre" : "Rock"},
	{"name" : "Who am I", "genre" : "Rock"},
	{"name" : "Raining", "genre" : "Blues"}
]
}')));

 SELECT * FROM
JSON_TABLE(ON (SELECT COL1, COL2 FROM myJsonTable WHERE col1 = 1)
USING rowexpr('$.songs[*]')
colexpr('[ {"jsonpath" : "$.name",
            "type" : "CHAR(20)"},
            {"jsonpath" : "$.genre",
             "type" : "VARCHAR(20)"}]')) AS JT(ID, "Song name", Genre);

 SELECT
* FROM
(
SELECT
COL1 AS ID,
rowexpr.value:name :: CHAR(20) AS "Song name",
rowexpr.value:genre :: VARCHAR(20) AS Genre
FROM
myJsonTable,
TABLE(FLATTEN(INPUT => COL2:songs)) rowexpr
WHERE col1 = 1
) JT;

 SELECT NEW JSON ('{"name" : "cameron", "age" : 24}'),
NEW JSON ('{"name" : "cameron", "age" : 24}', LATIN);

 SELECT
TO_JSON(PARSE_JSON('{"name" : "cameron", "age" : 24}')),
!!!RESOLVE EWI!!! /*** SSC-EWI-TD0039 - INPUT FORMAT 'LATIN' NOT SUPPORTED ***/!!!
TO_JSON(PARSE_JSON('{"name" : "cameron", "age" : 24}'));

 SELECT
NVP('entree=-orange chicken&entree+.honey salmon', 'entree', '&', '=- +.', 1),
NVP('Hello=bye|name=Lucas|Hello=world!', 'Hello', '|', '=', 2),
NVP('Player=Mario$Game&Tenis%Player/Susana$Game=Chess', 'Player', '% $', '= & /', 2);

 SELECT
PUBLIC.NVP_UDF('entree=-orange chicken&entree+.honey salmon', 'entree', '&', '=- +.', 1),
PUBLIC.NVP_UDF('Hello=bye|name=Lucas|Hello=world!', 'Hello', '|', '=', 2),
PUBLIC.NVP_UDF('Player=Mario$Game&Tenis%Player/Susana$Game=Chess', 'Player', '% $', '= & /', 2);

 SELECT
NVP('City=Los Angeles&Color=Green&Color=Blue&City=San Jose', 'Color'),
NVP('City=Los Angeles&Color=Green&Color=Blue&City=San Jose', 'Color', 2),
NVP('City=Los Angeles#Color=Green#Color=Blue#City=San Jose', 'City', '#', '=');

 SELECT
    PUBLIC.NVP_UDF('City=Los Angeles&Color=Green&Color=Blue&City=San Jose', 'Color', '&', '=', 1),
    PUBLIC.NVP_UDF('City=Los Angeles&Color=Green&Color=Blue&City=San Jose', 'Color', '&', '=', 2),
    PUBLIC.NVP_UDF('City=Los Angeles#Color=Green#Color=Blue#City=San Jose', 'City', '#', '=', 1);

 SELECT
NVP('store = whole foods&&store: ?Bristol farms','store', '&&', '\ =\  :\ ?', 2),
NVP('Hello = bye|name = Lucas|Hello = world!', 'Hello', '|', '\ =\ ', 2);

 SELECT
PUBLIC.NVP_UDF('store = whole foods&&store: ?Bristol farms', 'store', '&&', '\\ =\\  :\\ ?', 2),
PUBLIC.NVP_UDF('Hello = bye|name = Lucas|Hello = world!', 'Hello', '|', '\\ =\\ ', 2);

 COLUMN1       | COLUMN2 |
--------------+---------+
Bristol farms | world!  |

 SELECT NVP('store = whole foods&&store: ?Bristol farms','store', '&&', valueDelimiter, 2);

 SELECT
PUBLIC.NVP_UDF('store = whole foods&&store: ?Bristol farms', 'store', '&&', valueDelimiter, 2) /*** SSC-FDM-TD0008 - WHEN NVP_UDF FOURTH PARAMETER IS NON-LITERAL AND IT CONTAINS A BACKSLASH, THAT BACKSLASH NEEDS TO BE ESCAPED ***/;

 CREATE TABLE star1(
	country VARCHAR(20),
	state VARCHAR(10), 
	yr INTEGER,
	qtr VARCHAR(3),
	sales INTEGER,
	cogs INTEGER
);

insert into star1 values ('USA', 'CA', 2001, 'Q1', 30, 15);
insert into star1 values ('Canada', 'ON', 2001, 'Q2', 10, 0);
insert into star1 values ('Canada', 'BC', 2001, 'Q3', 10, 0);
insert into star1 values ('USA', 'NY', 2001, 'Q1', 45, 25);
insert into star1 values ('USA', 'CA', 2001, 'Q2', 50, 20);

 CREATE OR REPLACE TABLE star1 (
	country VARCHAR(20),
	state VARCHAR(10),
	yr INTEGER,
	qtr VARCHAR(3),
	sales INTEGER,
	cogs INTEGER
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

INSERT INTO star1
VALUES ('USA', 'CA', 2001, 'Q1', 30, 15);

INSERT INTO star1
VALUES ('Canada', 'ON', 2001, 'Q2', 10, 0);

INSERT INTO star1
VALUES ('Canada', 'BC', 2001, 'Q3', 10, 0);

INSERT INTO star1
VALUES ('USA', 'NY', 2001, 'Q1', 45, 25);

INSERT INTO star1
VALUES ('USA', 'CA', 2001, 'Q2', 50, 20);

 SELECT *
FROM star1 PIVOT (
	SUM(sales) FOR qtr                                                                                               
    IN ('Q1',                                                                                                     
    	'Q2', 
        'Q3')
)Tmp;

 SELECT
	*
FROM
	star1 PIVOT(
	SUM(sales) FOR qtr IN ('Q1',
	   	'Q2',
	       'Q3'))Tmp;

 SELECT *
FROM star1 PIVOT (
	SUM(sales) as ss1 FOR qtr                                                                                               
    IN ('Q1' AS Quarter1,                                                                                                     
    	'Q2' AS Quarter2, 
        'Q3' AS Quarter3)
)Tmp;

 SELECT
	*
FROM
	!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT RENAME COLUMN NOT SUPPORTED ***/!!!
	star1 PIVOT(
	SUM(sales) FOR qtr IN (
	                       !!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT RENAME COLUMN NOT SUPPORTED ***/!!!
	                       'Q1',
	!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT RENAME COLUMN NOT SUPPORTED ***/!!!
	   	'Q2',
	!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT RENAME COLUMN NOT SUPPORTED ***/!!!
	       'Q3'))Tmp;

 Country | State | yr   | cogs | Quarter1_ss1 | Quarter2_ss1 | Quarter3_ss1 |
--------+-------+------+------+--------------+--------------+--------------+
Canada	| BC	| 2001 | 0    | null 	     | null         | 10           |
--------+-------+------+------+--------------+--------------+--------------+
USA 	| NY	| 2001 | 25   | 45 	     | null 	    | null         |
--------+-------+------+------+--------------+--------------+--------------+
Canada 	| ON 	| 2001 | 0    | null 	     | 10 	    | null 	   |
--------+-------+------+------+--------------+--------------+--------------+
USA 	| CA 	| 2001 | 20   | null         | 50           | null         |
--------+-------+------+------+--------------+--------------+--------------+
USA 	| CA 	| 2001 | 15   | 30           | null         | null         |
--------+-------+------+------+--------------+--------------+--------------+

 CREATE TABLE Sales (
  Product VARCHAR(255),
  Sales INT
);

INSERT INTO Sales (Product, Sales) VALUES ('A', 100);
INSERT INTO Sales (Product, Sales) VALUES ('B', 150);
INSERT INTO Sales (Product, Sales) VALUES ('C', 200);
INSERT INTO Sales (Product, Sales) VALUES ('D', 150);
INSERT INTO Sales (Product, Sales) VALUES ('E', 120);
INSERT INTO Sales (Product, Sales) VALUES ('F', NULL);

 CREATE OR REPLACE TABLE Sales (
  Product VARCHAR(255),
  Sales INT
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

INSERT INTO Sales (Product, Sales)
VALUES ('A', 100);

INSERT INTO Sales (Product, Sales)
VALUES ('B', 150);

INSERT INTO Sales (Product, Sales)
VALUES ('C', 200);

INSERT INTO Sales (Product, Sales)
VALUES ('D', 150);

INSERT INTO Sales (Product, Sales)
VALUES ('E', 120);

INSERT INTO Sales (Product, Sales)
VALUES ('F', NULL);

 SELECT
  Sales,
  RANK(Sales ASC) AS SalesAsc,
  RANK(Sales DESC) AS SalesDesc,
  RANK(Sales) AS SalesDefault
FROM
  Sales;

 SELECT
  Sales,
  RANK() OVER (
  ORDER BY
    Sales ASC) AS SalesAsc,
    RANK() OVER (
    ORDER BY
    Sales DESC NULLS LAST) AS SalesDesc,
    RANK() OVER (
    ORDER BY
    Sales DESC NULLS LAST) AS SalesDefault
    FROM
    Sales;