Function in Snowflake

user_16595206 0
Viewed 59
create or replace function MMfGetPropValChar(propvalue varbinary(518))
RETURNS varchar(255)
as
$$
select 
    (CASE
     WHEN substr(${PROPVALUE}, 1, 3) = '0x00E700' Then CAST(substr(${PROPVALUE}, 9, 510) As Varchar(255))
WHEN substr(${PROPVALUE}, 1, 3) = '0x01E700') Then CAST(substr(${PROPVALUE}, 9, CAST(substr(${PROPVALUE}, 6, 1) as integer)) As Varchar(64))
)
ELSE (select current_timestamp)
End as test
return test;
$$

Below is the error I am getting:

Compilation of SQL UDF failed: SQL compilation error: syntax error line 2 at position 0 unexpected 'select'. syntax error line 3 at position 1 unexpected 'CASE'. syntax error line 4 at position 45 unexpected 'Then'.

snowflake-cloud-data-platform
1 Answers

The following example might help:

CREATE OR REPLACE FUNCTION MMfGetPropValChar(p1 varbinary)
RETURNS varchar
as
$$
  select 
    CASE
      WHEN substr(p1::varchar, 1, 3) = '0x00E700' 
         then substr(p1::varchar, 9, 510)::varchar
      WHEN substr(p1::varchar, 1, 3) = '0x01E700' 
         then substr(p1::varchar, 9, substr(p1::varchar, 6, 1))::varchar
   ELSE current_timestamp()::varchar
   End as test
$$
;


SELECT MMfGetPropValChar(to_binary(hex_encode('Snowflake'), 'HEX')) as res;

RES
2021-10-05 05:14:52.369 -0700

Note: I cast to varchar using the :: notation, just to make the SQL easier to read.

user_87675700
Related