将CONCAT从SQLServer转换为红移

Question

我在MSSQL服务器上有脚本，需要转换为Redshift。

这里是它的一部分

  CROSS JOIN (
SELECT        MONTH(be.TimeWorkedFrom) AS MONTH,
                YEAR(be.TimeWorkedFrom) AS YEAR,
                CONCAT(DATEPART(MONTH, be.TimeWorkedFrom),'/',1,'/',DATEPART(YEAR, be.TimeWorkedFrom))AS MonthBilled
FROM            BE2_Billing_Entries be

WHERE           be.TimeWorkedFrom > '1/1/2015'
                AND be.ProviderId = 65277

GROUP BY        MONTH(be.TimeWorkedFrom),
                YEAR(be.TimeWorkedFrom),
                CONCAT(DATEPART(MONTH, be.TimeWorkedFrom),'/',1,'/',DATEPART(YEAR, be.TimeWorkedFrom))
                )t

我就这样重写了

  CROSS JOIN (
SELECT          DATE_TRUNC('MONTH',be.timeworkedfrom) AS MONTH,
                DATE_TRUNC('YEAR',be.timeworkedfrom) AS YEAR,
                CONCAT(DATE_PART('MONTH', be.timeworkedfrom),'/',1,'/',DATE_PART('YEAR', be.timeworkedfrom))AS MonthBilled
FROM            billing_entries be

WHERE           be.timeworkedfrom > '1/1/2015'
                AND be.providerid = 65277

GROUP BY        DATE_TRUNC('MONTH',be.timeworkedfrom),
                DATE_TRUNC('MONTH',be.timeworkedfrom),
                CONCAT(DATE_PART('MONTH', be.timeworkedfrom),'/',1,'/',DATE_PART('YEAR', be.timeworkedfrom))
                )t

但现在我错了

42883亚马逊无效操作:函数连接(双精度、“未知”、“整数”、“未知”、双精度)不存在；java.lang.RuntimeException: com.amazon.support.exceptions.ErrorException: Amazon无效操作:函数连接(双精度、“未知”、“未知”、双精度)；

我的问题在哪里？

Answer 1

对于您的参考op.html，请使用x(串联)操作符-红移支持此运算符或嵌套的concat。

CROSS JOIN (
SELECT          DATE_TRUNC('MONTH',be.timeworkedfrom) AS MONTH,
                DATE_TRUNC('YEAR',be.timeworkedfrom) AS YEAR,
                DATE_PART('MONTH', be.timeworkedfrom)||'/'||1||'/'||DATE_PART('YEAR', be.timeworkedfrom) AS MonthBilled
FROM            billing_entries be

WHERE           be.timeworkedfrom > '1/1/2015'
                AND be.providerid = 65277

GROUP BY        DATE_TRUNC('MONTH',be.timeworkedfrom),
                DATE_TRUNC('MONTH',be.timeworkedfrom),
                DATE_PART('MONTH', be.timeworkedfrom)||'/'||1||'/'||DATE_PART('YEAR', be.timeworkedfrom)