在红移中创建sql自定义项

kgsdhlau 于 2021-08-01 发布在 Java

关注(0)|答案(1)|浏览(343)

我想在redshift中创建一个udf来替换下面的case语句

SELECT CASE
    WHEN column1= 'value' then coalesce(replace(column2,',', '')::decimal, 0) /52
    WHEN column1= 'value2' then coalesce(replace(column2,',', '')::decimal, 0) /76.0
    WHEN column1= 'value3' then coalesce(replace(column2,',', '')::decimal, 0) * 89.3
WHEN column1= 'value4' then coalesce(replace(column2,',', '')::decimal, 0) * 17.85
WHEN column1= 'value5' then coalesce(replace(column2,',', '')::decimal, 0)
ELSE NULL END

sql amazon-redshift

来源：https://stackoverflow.com/questions/62659288/create-a-sql-udf-in-redshift

1条答案

按热度按时间

oalqel3c1#

CREATE OR REPLACE FUNCTION f_convert_string_to_decimal(VARCHAR, VARCHAR) 
RETURNS DECIMAL 
STABLE AS $$
-- Input: 'value2', '999,999,999'
-- Output: 13157894.723684210526
SELECT CASE
            WHEN $1 = 'value'  THEN COALESCE(REPLACE( $2, ',', '')::DECIMAL, 0) /52
            WHEN $1 = 'value2' THEN COALESCE(REPLACE( $2, ',', '')::DECIMAL, 0) /76.0
            WHEN $1 = 'value3' THEN COALESCE(REPLACE( $2, ',', '')::DECIMAL, 0) * 89.3
            WHEN $1 = 'value4' THEN COALESCE(REPLACE( $2, ',', '')::DECIMAL, 0) * 17.85
            WHEN $1 = 'value5' THEN COALESCE(REPLACE( $2, ',', '')::DECIMAL, 0)
      ELSE NULL END
$$ LANGUAGE SQL
;

赞(0）回复(0）举报 2021-08-01

我来回答

在红移中创建sql自定义项

1条答案

相关问题

热门标签

最新问答