This is a deprecated version of the SnowConvert documentation, please visit the official site .
Severity
Medium
Description
JSON_TABLE requires a second column as JSON type, in any other position or even if only one column is provided as JSON type Teradata will throw an error. So this message appears when SnowConvert can't get the second column, and how is showed, also can't do the conversion either.
Example code
Input Code:
SELECT * FROM JSON_TABLE
(ON (SELECT trainSchedule as ts
FROM demo.Train T)
USING rowexpr('$.weekShedule.Monday[*]')
colexpr('[ {"ordinal" : true},
{"jsonpath" : "$.time",
"type"" : "CHAR ( 12 )"},
{"jsonpath" : "$.city",
"type" : "VARCHAR ( 12 )"}]'))
AS JT(Ordinal, Time, City);
SELECT * FROM JSON_TABLE
(ON (SELECT T.*
FROM demo.Train T)
USING rowexpr('$.weekShedule.Monday[*]')
colexpr('[ {"ordinal" : true},
{"jsonpath" : "$.time",
"type"" : "CHAR ( 12 )"},
{"jsonpath" : "$.city",
"type" : "VARCHAR ( 12 )"}]'))
AS JT(Ordinal, Time, City);
Output Code:
SELECT
*
FROM /*** MSC-ERROR - MSCEWI2060 - UNSUPPORTED JSON_TABLE, OPERATOR QUERY SHOULD HAVE JSON COLUMN AS THE SECOND COLUMN ***/
JSON_TABLE
(ON (SELECT
trainSchedule as ts
FROM demo.PUBLIC.Train T)
USING rowexpr('$.weekShedule.Monday[*]')
colexpr('[ {"ordinal" : true},
{"jsonpath" : "$.time",
"type"" : "CHAR ( 12 )"},
{"jsonpath" : "$.city",
"type" : "VARCHAR ( 12 )"}]'))
AS JT(Ordinal, Time, City);
SELECT
*
FROM /*** MSC-ERROR - MSCEWI2060 - UNSUPPORTED JSON_TABLE, OPERATOR QUERY SHOULD HAVE JSON COLUMN AS THE SECOND COLUMN ***/
JSON_TABLE
(ON (SELECT
T.*
FROM demo.PUBLIC.Train T)
USING rowexpr('$.weekShedule.Monday[*]')
colexpr('[ {"ordinal" : true},
{"jsonpath" : "$.time",
"type"" : "CHAR ( 12 )"},
{"jsonpath" : "$.city",
"type" : "VARCHAR ( 12 )"}]'))
AS JT(Ordinal, Time, City);
Recommendations
Do not use * and ensure that the second column is a JSON type on the column list in the SELECT query of the ON expression.
