Syntax Error when using length function with repeat function in Athena

Question

After running the below query in Athena I am getting the error:

```
SELECT
  REPEAT('*', LENGTH('testing'))  AS "e7cfff0a-f4d3-4fd9-a6cc-c5f7af177074"
FROM "default"."all_flights"
GROUP BY
  REPEAT('*', LENGTH('testing'))
LIMIT 500
```

```
SYNTAX_ERROR: line 2:3: Unexpected parameters (varchar(1), bigint) for function repeat. Expected: repeat(T, integer) T
This query ran against the "amtestdb" database, unless qualified by the query. Please post the error message on our forum  or contact customer support  with Query Id: 0f51f40e-66e1-4512-aa22-ed36b4f7bee6
```
Wondering why this query doesn't run as expected?

Answer

From your error seems as the LENGTH('testing') returns a BigInt, while REPEAT expects an Int as second parameter.

You can try to CAST(LENGTH('testing') AS INTEGER)

https://docs.aws.amazon.com/redshift/latest/dg/r_CAST_function.html

Syntax Error when using length function with repeat function in Athena

相關內容