CODEWITHSUNDEEP
amazon-redshift
user2493287
user2493287

Reputation: 256

AWS redshift query having dateadd function not working with dynamic argument

I am currently writing AWS redshift query where I have to add Interval and its value to dateadd function as per value present in that row. But, redshift query is giving error. Below is the query.

select unit, value, lastorderdate, dateadd(o.unit, o.value, o.lastorderdate) as newdate
from Order o

Error: ERROR: syntax error at or near "." and its pointing to dateadd function.

Above is the example query, my query is having join with multiple table, hence cant avoid alias.

Any suggestion here. Thanks in advance.

Upvotes: 0

Views: 554

Answers (1)

Bill Weiner
Bill Weiner

Reputation: 11042

The first argument of dateadd() is a datepart not a string. This value is consumed at compile time and cannot be a column AFAIK. It needs to be static during the query and is of a type that cannot be stored in a column. I don't think this (I think you are trying to represent an interval dynamically based on unit) can be done this way.

I think the easiest way to do this is with a CASE statement that multiplies or divides based on the value of unit. For example use "sec" as the datepart and multiply by 60 IF unit is min or multiply by 3600 if unit is hour or ...

Or you could but the dateadd function inside the CASE - when unit = 'sec' then dateadd ...

The second method will be better if you are, at times, dealing with months or years.f

select unit, value, lastorderdate, 
  case 
    when o.unit = 'day' dateadd(day, o.value, o.lastorderdate) 
    when o.unit = 'week' dateadd(week, o.value, o.lastorderdate) 
    when o.unit = 'month' dateadd(month, o.value, o.lastorderdate) 
    when o.unit = 'year' dateadd(year, o.value, o.lastorderdate) 
  end as newdate
from Order o;

Upvotes: 0

Related Questions