topic Re: Is there any way to use OUTER UNION CORR in hive sql? in SAS Enterprise Guide

Is there any way to use OUTER UNION CORR in hive sql?

ahmed_00 — Fri, 12 Jun 2020 14:07:30 GMT

I am trying to create table from four tables by using UNION logic, but all the tables have one column different.

PROC SQL;

create table output_table

select cl1, cl2, cl3 from tb1

OUTER UNION CORR

select cl1, cl2, cl4 from tbl2

OUTER UNION CORR

select cl1,cl2, cl5 from tbl3

OUTER UNION CORR

select cl1, cl2, cl6 from tbl4;

quit;

on this query the output table contains all columns (cl1,cl2,cl3,cl4,cl5,cl6).

So my question is is there any way to get the whole column in Hive SQL please.

create table output_table

select cl1, cl2, cl3 from tb1

UNION ALL

select cl1, cl2, cl4 from tbl2

UNION ALL

select cl1,cl2, cl5 from tbl3

UNION ALL

select cl1, cl2, cl6 from tbl4;

IF i use like this the columns on the output table contains only from the first table. (cl1,cl2,cl3)

But I need to get all columns on my output.

mkeintz — Sat, 13 Jun 2020 03:10:10 GMT

What do you mean by "So my question is is there any way to get the whole column in Hive SQL please"?

Patrick — Sat, 13 Jun 2020 03:42:51 GMT

Can't you just create a common column in your select statement?

  select cl1, cl2, cl3 as cl_common
  from  tb1
  UNION ALL
  select cl1, cl2, cl4 as cl_common
  from tbl2
...and so on

Make sure you use the ALL keyword unless you also want to dedup the result set.