0

hi I want to union 2 tables but I am not sure if the table is exits this is my query:

select  a from table 
union  
select  case when exists(select a from table1) then (select a from  table1)
else (select a from table2) end;

got this

ERROR: ERROR 4840: Subquery used as an expression returned more than one row

I need to use limit 1 in a subquery but it's not the result that I wanted.

do you have other suggestions? thanks

table, for example, I just created them in my local env for testing:

table 1

CREATE TABLE public.table1 
(
    a int,
    b varchar(80),
    c int
);


CREATE TABLE public.table
    a int,
    b varchar(80),
    c int
);
0

2 Answers 2

1

In principle this won't work, as you can't check if table exists and then join it in a query.

The error is unrelated. You are selecting a value in a subselect and it returns multiple rows, as the table exists.

What you can do is:

  • Create temp table
  • Check if table1 exists using V_Catalog.table and if specific column existence is important, v_catalog.column
  • If it exists, select data from table1 into temp table
  • Use the temp table in the union instead of table1
-1

I have not knowledge working with Vertica but you can try using If exists like in SQL Server

If exist (Select 1 FROM sys.objects Where type = 'U' and name in ('YourTableName1', 'YourTableName2')) Begin Select * From Public.Table1 Union Select * From Public.Table2 End

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.