how to create a table from several select queries?

Database
Yan
Enthusiast

how to create a table from several select queries?

Hi there,

 

I have a query result from below and would like to create a table with the result data. Does anyone know how?

 

thanks a lot!

 

Select t1.SO_No, t1.SO_Item, t1.MATERIAL, t2.ATP, t2.ATP_Plant, t2.TNT
from
(
select A.SO_No, A.SO_Item, A.MATERIAL, A.Order_Qty
from A
group by A.SO_No, A.SO_Item, A.MATERIAL, A.Order_Qty
) t1

left join (
select A.SO_No, A.SO_Item,A.ATP, A.ATP_Plant, A.Days
from A
where A.ATP_Plant in ('A01','A02','A03','A04') and A.Source_Type is null
) t2;

 

Thanks!

 

Yan

on t1.SO_No = t2.SO_No and t1.SO_Item = t2.SO_Item


Accepted Solutions
Senior Apprentice

Re: how to create a table from several select queries?

Hi,

 

This is very straight forward, you 'wrap' your code in a 'CREATE TABLE xx AS' statement.

 

Try something like:

CREATE TABLE databasename.tablename AS
(Select t1.SO_No, t1.SO_Item, t1.MATERIAL, t2.ATP, t2.ATP_Plant, t2.TNT
from
(
select A.SO_No, A.SO_Item, A.MATERIAL, A.Order_Qty
from A
group by A.SO_No, A.SO_Item, A.MATERIAL, A.Order_Qty
) t1

left join (
select A.SO_No, A.SO_Item,A.ATP, A.ATP_Plant, A.Days
from A
where A.ATP_Plant in ('A01','A02','A03','A04') and A.Source_Type is null
) t2
) WITH DATA primary-index-clause;

Change the databasename and tablename to something meaningful for you, specify your primary index (recommended) and you should be ok.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
2 REPLIES
Senior Apprentice

Re: how to create a table from several select queries?

Hi,

 

This is very straight forward, you 'wrap' your code in a 'CREATE TABLE xx AS' statement.

 

Try something like:

CREATE TABLE databasename.tablename AS
(Select t1.SO_No, t1.SO_Item, t1.MATERIAL, t2.ATP, t2.ATP_Plant, t2.TNT
from
(
select A.SO_No, A.SO_Item, A.MATERIAL, A.Order_Qty
from A
group by A.SO_No, A.SO_Item, A.MATERIAL, A.Order_Qty
) t1

left join (
select A.SO_No, A.SO_Item,A.ATP, A.ATP_Plant, A.Days
from A
where A.ATP_Plant in ('A01','A02','A03','A04') and A.Source_Type is null
) t2
) WITH DATA primary-index-clause;

Change the databasename and tablename to something meaningful for you, specify your primary index (recommended) and you should be ok.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Highlighted
Yan
Enthusiast

Re: how to create a table from several select queries?

Thanks Dave! got it!