如何将以下的 SQL 查询转换为在 Snowflake 中运行的查询?

huangapple go评论66阅读模式
英文:

How to convert the below SQL query to work in snowflake?

问题

SELECT 
    LEFT(Replace(SERIAL_NBR,'"',''),34) AS SERIAL_NBR
    ,to_timestamp(Replace(INSERTED_DTM,'"',''), 'DD/MM/YYYY') AS INSERTED_DTM
    ,LEFT(Replace(GROUP_NAME,'"',''),1024)
    ,LEFT(Replace(FIRST_NAME,'"',''),50)
    ,LEFT(Replace(LAST_NAME,'"',''),50)
    ,LEFT(Replace(REASON_CODE_ID,'"',''),30)
    ,CASE 
       WHEN isnumeric(Replace(VALUE,'"','')) = 1 
       THEN CAST(Replace(VALUE,'"','') AS FLOAT)
       ELSE NULL
     END AS VALUE
    ,LEFT(Replace(AUTOLOAD_DELIVERY_STATE_ID,'"',''),15)
    ,LEFT(Replace(RIDER_CLASS,'"',''), 20)
    ,LEFT(Replace(ADJUSTMENT_NOTES,'"',''), 1024)
FROM stage."TL_A2_Adjustment_Note"
WHERE Replace(SERIAL_NBR,'"','') != 'A2'
英文:
SELECT 
   LEFT(Replace([SERIAL_NBR],'"',''),34) AS [SERIAL_NBR]
   ,CONVERT(datetime, Replace([INSERTED_DTM],'"',''), 103) AS [INSERTED_DTM]
   ,LEFT(Replace([GROUP_NAME],'"',''),1024)
   ,LEFT(Replace([FIRST_NAME],'"',''),50)
   ,LEFT(Replace([LAST_NAME],'"',''),50)
   ,LEFT(Replace([REASON_CODE_ID],'"',''),30)
   ,CASE isnumeric(Replace([VALUE],'"',''))
       when 1 
			then CAST(Replace([VALUE],'"','') AS float)
			else null
	 END AS	[VALUE]
    ,LEFT(Replace([AUTOLOAD_DELIVERY_STATE_ID],'"',''),15)
    ,LEFT(Replace([RIDER_CLASS],'"',''), 20)
    ,LEFT(Replace([ADJUSTMENT_NOTES],'"',''), 1024)
FROM X 

This is what I changed it to

SELECT 
    LEFT(Replace(SERIAL_NBR,'"',''),34) AS SERIAL_NBR
    ,to_timestamp(Replace(INSERTED_DTM,'"',''), 'DD/MM/YYYY') AS INSERTED_DTM
    ,LEFT(Replace(GROUP_NAME,'"',''),1024)
    ,LEFT(Replace(FIRST_NAME,'"',''),50)
    ,LEFT(Replace(LAST_NAME,'"',''),50)
    ,LEFT(Replace(REASON_CODE_ID,'"',''),30)
    ,CASE 
       WHEN is_Double(Replace(VALUE,'"','')) = 1 
       THEN CAST(Replace(VALUE,'"','') AS NUMBER)
       ELSE NULL
     END AS VALUE
    ,LEFT(Replace(AUTOLOAD_DELIVERY_STATE_ID,'"',''),15)
    ,LEFT(Replace(RIDER_CLASS,'"',''), 20)
    ,LEFT(Replace(ADJUSTMENT_NOTES,'"',''), 1024)
FROM stage."TL_A2_Adjustment_Note"
WHERE Replace(SERIAL_NBR,'"','') != 'A2'

It's giving me the error

> 001044 (42P13): SQL compilation error: error line 9 at position 9

At WHEN is_Double(Replace(VALUE,'"','')) = 1

答案1

得分: 1

The case statement is saying "when it can be parsed as a double/numeric do so, else use NULL."

Thus, in Snowflake, this section:

,CASE 
   WHEN is_Double(Replace(VALUE,'"','')) = 1 
   THEN CAST(Replace(VALUE,'"','') AS NUMBER)
   ELSE NULL
 END AS VALUE

can be replaced with TRY_TO_DOUBLE, which, if it fails to parse as a float, will return null.

So it would be:

,TRY_TO_DOUBLE(Replace(VALUE,'"','')) as value
英文:

The case statement is saying "when it can be parsed as a double/numeric do so, else use NULL"

Thus is snowflake this section:

 ,CASE 
       WHEN is_Double(Replace(VALUE,'"','')) = 1 
       THEN CAST(Replace(VALUE,'"','') AS NUMBER)
       ELSE NULL
     END AS VALUE

can use TRY_TO_DOUBLE which if it fails to parse as a float, will return null.

thus:

    ,TRY_TO_DOUBLE(Replace(VALUE,'"','')) as value

huangapple
  • 本文由 发表于 2023年2月6日 09:29:45
  • 转载请务必保留本文链接:https://go.coder-hub.com/75356648.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定