Convert CONCAT from SQLServer to Redshift

Question

I have script at MSSQL server that I need to convert to Redshift

Here is part of it

  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

I rewrite it like this

  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

But now I get error

[42883][500310] Amazon Invalid operation: function concat(double precision, "unknown", integer, "unknown", double precision) does not exist; java.lang.RuntimeException: com.amazon.support.exceptions.ErrorException: Amazon Invalid operation: function concat(double precision, "unknown", integer, "unknown", double precision) does not exist;

Where can be my problem?

Generally you shouldn't be using CONCAT in a query because it's a view-level concern (and separate data values should be returned to the client with their original data-types). Is there a reason you need to do CONCAT in SQL instead of in your application? I also note that using CONCAT in a GROUP BY does not give you the best performance (and it's unnecessary in your example as you're already using the two underlying values (month and year from TimeWorkedFrom) in your GROUP BY anyway. — Dai
– Dai, Commented Sep 5, 2018 at 9:52

Fahmi · Accepted Answer · 2018-09-05 09:50:47Z

2

use || (Concatenation) Operator - redshift support this operator or nested concat - for your reference https://docs.aws.amazon.com/redshift/latest/dg/r_concat_op.html

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)

answered Sep 5, 2018 at 9:50

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Eugene Sukh Over a year ago

Hm. now I get this error Invalid operation: column "be.timeworkedfrom" must appear in the GROUP BY clause or be used in an aggregate function;

Eugene Sukh Over a year ago

But, how I can do this? I newer in sql

Fahmi Over a year ago

use that column in group by clause like GROUP BY DATE_TRUNC('MONTH',be.timeworkedfrom), DATE_TRUNC('MONTH',be.timeworkedfrom), DATE_PART('MONTH', be.timeworkedfrom)||'/'||1||'/'||DATE_PART('YEAR', be.timeworkedfrom), be.timeworkedfrom

Collectives™ on Stack Overflow

Convert CONCAT from SQLServer to Redshift

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related