I have 4 diff tables as below:
Table columns are as below:
Need to select all columns from all the 4 tables.
I am able to select AMOUNT and RETAIL columns, but not able to figure out the way to select CA_XXXX_CA_APPLE_ID column.
Code tried so far:
Select ' Select AMOUT,RETAIL'+
' from '+TABLE_SCHEMA+'.'+ table_name +
' Union All'
From INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'CA_%_APPLE';
It gives me below:
Select AMOUT,RETAIL from data.CA_2003_CA_APPLE Union All
Select AMOUT,RETAIL from data.CA_2004_CA_APPLE Union All
Select AMOUT,RETAIL from data.CA_2005_CA_APPLE Union All
Select AMOUT,RETAIL from data.CA_2006_CA_APPLE Union All
Desired Output:
Select CA_2003_CA_APPLE_ID,AMOUT,RETAIL from data.CA_2003_CA_APPLE Union All
Select CA_2004_CA_APPLE_ID,AMOUT,RETAIL from data.CA_2004_CA_APPLE Union All
Select CA_2005_CA_APPLE_ID,AMOUT,RETAIL from data.CA_2005_CA_APPLE Union All
Select CA_2006_CA_APPLE_ID,AMOUT,RETAIL from data.CA_2006_CA_APPLE Union All
is it even possible what I am trying to achieve?
Thanks