Specifying Input Values

Teradata Vantageā„¢ DATASET Data Type

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1198-162K

The first input value specified via the ON clause must be character based (for example, a CHAR/ VARCHAR/CLOB or a DATASET STORAGE FORMAT CSV value), and it must be composed of data that conforms to the specified format. That is, it uses the specified field/record delimiters, or defaults if not specified. Any subsequent input values do not affect the result of conversion to Avro or JSON, but are passed through the table operator and given as additional output columns. This allows the resulting Avro or JSON documents to be associated with the source CSV data, which is very important when converting multiple CSV inputs.

CT dr181746_table(id int, csvData VARCHAR(500));
INSERT INTO dr181746_table(1, 'a,b,c,d,e,f\1,2,3,4,5,6\7,8,9,10,11,12');
INSERT INTO dr181746_table(2, 'a,b,c,d,e,f\13,14,15,16,17,18
\19,20,21,22,23,24');
INSERT INTO dr181746_table(3, 'a,b,c,d,e,f\25,26,27,28,29,30
\31,32,33,34,35,36');

SELECT id, data FROM CSV_TO_JSON
(
    ON (SELECT csvData, id FROM dr181746_table)
    USING SCHEMA('{"record_delimiter":"\\"}')
) as csvJSON
ORDER BY id, data.a;

         id data.TOJSON()
----------- --------------------------------------------------------------
          1 {"a":"1","b":"2","c":"3","d":"4","e":"5","f":"6"}
          1 {"a":"7","b":"8","c":"9","d":"10","e":"11","f":"12"}
          2 {"a":"13","b":"14","c":"15","d":"16","e":"17","f":"18"}
          2 {"a":"19","b":"20","c":"21","d":"22","e":"23","f":"24"}
          3 {"a":"25","b":"26","c":"27","d":"28","e":"29","f":"30"}
          3 {"a":"31","b":"32","c":"33","d":"34","e":"35","f":"36"}