CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
lohith ramineni
lohith ramineni

Reputation: 57

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

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

Upvotes: 0

Views: 94

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25893

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

Upvotes: 1

Related Questions