Combining CASE and LISTAGG in a REDSHIFT stateement

user_9427988 0
Viewed 50

Below is a snip of code in which I am trying to list all the product dates per product id. To avoid the LISTAGG size restriction, I', trying to only list dates within the last 12 months.

SELECT distinct product_id, as_of_date, 
CASE WHEN date_cmp(to_date(cast(as_of_date as text),'YYYY-MM-DD'),to_date(cast(add_months(current_date,-12) as TEXT),'YYYY-MM-DD')) > 0     
  THEN LISTAGG(as_of_date, ',')    
  WITHIN GROUP (Order by product.as_of_date)
  OVER (PARTITION BY product_id)
END  from product

On running this I get the error message

"Result size exceeds LISTAGG limit"

which presumably means that the CASE isn't selecting only the last 12 months.

All the date casting was because without it I was getting date timestamp errors.

Any ideas would be appreciated!

amazon-redshift sql
1 Answers

You state you are "trying to only list dates within the last 12 months" but the case expression says "if the as_of_date is at most one year old, please return all dates of product_id partition".

I guess you rather want something like listagg(case when date_cmp(...) then as_of_date else '' end, ',') within ... or listagg(distinct ...) thereof.

user_6535390
Related