I have a pretty complex with statement that combines a lot of information to grab with a select statement in teradata studio. The with statement works wonderfully and selects the data I need, but I can't figure out how to create a view out of it, or save it as a persistent table, or do anything with it that would make it a persistent way to select the data. I want to use this with statement to save the select statement as a persistent table that I can then pull from using tpt scripts in an ec2 instance.
I used to create volatile tables instead of using a with statement and I could create the table by doing the correct select and insert statement. However, this is going to be put into a production environment and scheduled, and they weren't ok with that and said to wrap it up in a with statement.
I've tried wrapping the entire with statement into a create table or create view statement. I've tried changing the select statement that grabs the data and turning that into a create table or insert into statement, but none of these seem to cooperate with the with statement syntax.
WITH
customer_month_ad AS
(
),
months_since_last_payment AS
(
),
customer_month_2 AS
(
),
customer_month AS
(
),
date_things AS
(
),
network AS
(
),
skeleton AS
(
),
customer_stuff AS
(
),
customer_keyz AS
(
),
customer_activity AS
(
),
order_date AS (
),
months AS
(
)--,
SELECT *
FROM customer_month_ad
;
I want to save the output from the select statement into a persistent table.
1 Answer
Try this query
create table <your_table_name> as select * from <table_names>
OR
create table <your_table_name> as <your_select_statement>
Replace:
<your_table_name> by the name of the resultant table
<table_names> by the names of the table from where data is coming
<your_select_statement> by the select statement if you have a different one which is
not like select * from .......
