如何在snowfkale中存储-00:00时区值

Question

我有一个存储从源文件读取到雪花表的数据的场景:

输入数据线:

3 AC  0060876543NOV221080123 23 5 7 56709000900+0900

我必须读取"09:00"的最后四位数字基于加减号的格式。如果数据是-0900，那么在snowflake中应该存储为"-09:00"

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;

但是，我得到错误如下:

For minus value : Numeric value '-04:00' is not recognized
For plus value: Numeric value '09:00' is not recognized

更新:

这是我的teradata sql:

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

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

我想在snowflake中实现相同的功能，因此在第二个case语句中它抛出错误。最后一列，我需要在varchar列中存储内容。

Answer 1

SUBSTR(raw_data, LEN(raw_data)-5,3) || ':' || RIGHT(raw_data,2)