KNOWLEDGE BASE

Error "[Amazon][RedShift ODBC] (30)...One or more of the used functions must be applied on at least one user created tables" Connecting to Redshift


Published: 06 Nov 2017
Last Modified Date: 07 Nov 2017

Issue

When using the LISTAGG function in a RedShift query an error might occur:
  [Amazon][RedShift ODBC] (30) Error occurred while trying to execute a query: ERROR:  One or more of the used functions must be applied on at least one user created tables. Examples of user table only functions are LISTAGG, MEDIAN, PERCENTILE_CONT, etc  

Environment

  • Tableau Desktop 10.0 and newer
  • Amazon Redshift

Resolution

Change the delimiter used in the LISTAGG arguments.

For example, if the SQL statement is:
​SELECT region,
LISTAGG(state,';')
WITHIN GROUP (ORDER BY region)
FROM public.superstore
GROUP BY 1

Change it to:
​SELECT region,
LISTAGG(state,',')
WITHIN GROUP (ORDER BY region)
FROM public.superstore
GROUP BY 1

Cause

The semicolon delimiter is being parsed incorrectly. Using a different delimiter will run the query successfully.
Did this article resolve the issue?