sql - 雪花: Conversion error of an teradata query to snow sql

Question

我有一个 Teradata 查询(使用示例值更新了字段):

select (case '-' when '-' then '-' ||'04' || ':' ||'00'
else '04' || ':' ||'00'
end (Interval hour to minute)) +
 (case
    '2400' when '2400' then 24
    else 0
end (interval hour));

输出:-04:00(varchar 类型)

select (case '-' when '-' then '-' ||'04' || ':' ||'00'
else '04' || ':' ||'00'
end (Interval hour to minute)) +
 (case
    '1835' when '2400' then 24
    else 0
end (interval hour));

输出:20:00(varchar 类型)

想要在雪花中转换相同的值，但相同的输出值无法插入到雪花 varchar 列中:

SELECT                             
(CASE SUBSTR(raw_data, 48, 1) 
 WHEN '-' THEN CONCAT('-' , SUBSTR(raw_data,49,2) , ':' , SUBSTR(raw_data,51,2))
 ELSE CONCAT(SUBSTR(raw_data,49,2) , ':' , SUBSTR(raw_data,51,2)) END) + 
 (CASE SUBSTR(raw_data,40,4)  WHEN '2400' THEN 24 ELSE 0  END)  
AS COLUMN_1
FROM
    (SELECT  temp_row.$1 as raw_data  from
           @JOB_MANAGEMENT.SNOWFALKE (file_format => 'DB.TBL_FILE_FORMAT',
            pattern=>'.*/input_file.txt') temp_table) temp;

示例:

SELECT                             
(CASE '-' WHEN '-' 
THEN CONCAT('-' , '04 , ':' , '00')
ELSE CONCAT('04' , ':' , '00') END)  + 
(CASE '1825'  WHEN '2400' THEN 24 ELSE 0  END)

输出:-04:00(列类型 - varchar)-> 但在雪花中抛出错误。无法识别数值“-04:00”

Answer 1

此方法不是尝试重写查询 1:1，而是专注于使用 TIME_FROM_PARTS 重写逻辑。 :

SELECT 
  CASE WHEN SUBSTR(raw_data, 48, 1) = '-'
       THEN TIME_FROM_PARTS(24-SUBSTR(raw_data,49,2)::INT, SUBSTR(raw_data,51,2)::INT, 0)
       ELSE TIME_FROM_PARTS(SUBSTR(raw_data,49,2)::INT, SUBSTR(raw_data,51,2)::INT, 0)
  END
FROM ...