KNOWLEDGE BASE

Error "One or more of the used functions must be applied on at least one user created table" Using LISTAGG with Amazon Redshift

Published: 21 Mar 2018
Last Modified Date: 08 Jul 2022

Issue

When connecting to Amazon Redshift with custom SQL statement including the LISTAGG() function, the following error occurs:

[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

  • Amazon Redshift
  • Custom SQL

Resolution

Use the following workaround:
  1. Use Initial SQL to create a temporary table with a modified query, and then connect to the temporary table using custom SQL:

    Using Initial SQL, create a temporary table with the results of the desired Custom SQL query:
    Create table MyConnection as (custom_query_here)

    For example, if the custom SQL query is:
    select field_a, listagg(field_b, ',') from table group by field_a

    then the Initial SQL will be:
    create table MyConnection as ( select field_a, listagg(field_b), ',') from table group by field_a)
If data freshness is a concern, consider using a view in the Initial SQL statement (instead of a temporary table) as follows:
 
create or replace view <view_name_here> as 
<custom_query_here>
with no schema binding;
 
  1. Use a Custom SQL statement to retrieve the data from the temporary table/view:
Select * from MyConnection
Did this article resolve the issue?    
PRINT THIS PAGE
Trending Articles
Attachments


All Support

SUPPORT BY PRODUCT
TOOLS & DOWNLOADS
INFORMATION
ASSISTANCE