union all in sql statement

Applications Grid / Reports
#1

Hi,

​I created a grid with the following sql to join all the records from 2 tables:

​select ta.column1 as ta_column1 from tableA ta
union all
​select tb.column1 as tb_column1 from tableB tb

​When I run the application, it does shows all the records in the grid. However, when I tried using advanced search, it will prompt error.

​Also, I noticed that in the field folder, it only shows the field (ta_column1) from tableA. I need to use the auto lookup for the field in tableB thus I need those fields in tableB to appear.

​Can someone kindly advise? Thanks.

#2

the essence of union is that your output has the same column names. if you want to make new columns you have to add it to all sub queries. your query should be
select column1 as column1, ‘tableA’ as Source from tableA
union all
​select column1 as column1, ‘tableB’ as Source from tableB