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

This is a sample of subquery used in Vertica Query which is a string generated from the application.

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?

I have an alternate approach to fix the query removing UNION ALL. But looking for a solution to the error generated.

Thanks in Advance!

Answer

If the CSV option proves impossible, I would use a temporary table – and it would also speed up your query:

CREATE LOCAL TEMPORARY TABLE helper_table(id,paidamt)
ON COMMIT PRESERVE ROWS AS
          SELECT 1, 345.45 
UNION ALL SELECT 2, 789.45 
UNION ALL SELECT 3, 213.44
[.2997 more rows . .]
;

INSERT INTO helper_table
          SELECT 3001, 4345.45 
UNION ALL SELECT 3002, 3789.45 
UNION ALL SELECT 3003, 1213.44
[.2997 more rows . .]
;

SELECT
  <<whatever>>
FROM helper_table JOIN <<other_tables>> ON <<whatever>> 
;

The helper_table will die as soon as you log off again.

Alternatively, use vsql with a script like this:

CREATE LOCAL TEMPORARY TABLE helper_table (
  id      INT
, paidamt NUMERIC(9,2)
)
ON COMMIT PRESERVE ROWS;

COPY helper_table FROM stdin;
   1|  345.45 
   2|  789.45 
   3|  213.44
3001| 4345.45 
3002| 3789.45 
3003| 1213.44
.

-- Now you can work with it ...
SELECT * FROM helper_table;