Error while running very long SQL query with UNION ALL operator in Vertica

Siddhi_Lal_Kaye

To have a single query that needs to be passed to another service, our application generated a query string. One portion of the query will have a sub-query string to union output rows like for e.g.

SELECT 1 as ID, 345.45 as PaidAmt FROM DUAL
UNION ALL 
SELECT 2 as ID, 789.45 as PaidAmt FROM DUAL
UNION ALL

There are some cases when this subquery becomes huge as the number of rows with UNION ALL increases. Vertica supports this query to some extent. But when there is let's say 3000 + UNION ALL operator used in a subquery it throws an error.

[SQL Error [4963] [54001]: [Vertica]VJDBC ERROR: The query contains a SET operation tree that is too complex to analyze

I could not find any documents that talk about the limited use of UNION ALL operator in query or length of the query string.

Is there any system parameter in Vertica or Linux for which we can change the value to successfully execute the query?
The error belongs to :
Class 54—Program Limit Exceeded
54001 - ERRCODE_STATEMENT_TOO_COMPLEX

PS. I have an alternate query to fix the issue removing UNION ALL. But I am looking for a solution (if any) to execute a query that went into error.

Siddhi_Lal_Kaye

https://www.vertica.com/docs/10.1.x/HTML/Content/Authoring/SQLReferenceManual/LanguageElements/Expressions/Expressions.htm#4 explains the limitation of max 2000. Thanks!