Snowflake Substring Concat报错：无法识别空数值，财季上季度计算咨询

错误原因 #

• 截取位置错误：FYxxQy格式的财季字符串共6位，季度数字位于第6位，原代码中SUBSTR(p.current_qtr,5,1)取到的是字符Q，而非季度数字，后续隐式转换失败触发报错
• 运算优先级错误：Snowflake中字符串拼接符||优先级低于算术运算符，未加括号时会先尝试将拼接后的字符串做减法运算，触发数字格式识别错误
• 分支逻辑错误：ELSE分支的拼接逻辑完全不符合需求，误将财年数值减1且未保留财季前缀

修复后代码 #

SELECT *,
CASE 
   WHEN SUBSTR(p.current_qtr, 6, 1) = '1'
      THEN SUBSTR(p.current_qtr, 1, 2) || (TRY_CAST(SUBSTR(p.current_qtr, 3, 2) AS INTEGER) - 1) || 'Q4'
      ELSE SUBSTR(p.current_qtr, 1, 5) || (TRY_CAST(SUBSTR(p.current_qtr, 6, 1) AS INTEGER) - 1)
END AS prev_qtr
FROM your_table_name AS p 

验证效果 #

内容的提问来源于stack exchange，提问作者B.Z.B