CREATE NEW TABLE with COLUMNS FROM four QUERIES + CONSTANT Fields

UDA
Enthusiast

CREATE NEW TABLE with COLUMNS FROM four QUERIES + CONSTANT Fields

I am newbie to databases and TeraData and MS TD SQL assistant.

I am trying to create a TBL from 4 different queries run seperately(manually)

I have a code for Select queries to Pull data but I do not know how would I create MyTable from 4 queries + additional 5 columns have to be added in a New Table that are not a part of any Table/View or Query.

I have 4 SCHEMAS, so when creating Table should one also have to give Schema Name ?

Please help with SYNTAX.

I have so far tried the following:

Create TABLE SCHEMA.MyTable as (

Reg VARCHAR (3),

Cou VARCHAR (6),

BR  VARCHAR (3).

KP VARCHAR (20),

SS VARCHAR (8),

FT VARCHAR (5)

BM VARCHAR (6)

Above fields are constant fields where data has to be hard coded in a Create Table query along with pulling data from 4 queries and dumping in MyTable.

This does not work. 

All help will be greately appreciated

6 REPLIES
Enthusiast

Re: CREATE NEW TABLE with COLUMNS FROM four QUERIES + CONSTANT Fields

Hi,

This is an example of your table, you must decide if the columns are nulleables or not, and select the correct column(s) for the primary index, i select Reg.

CREATE SET TABLE SCHEMA.MyTable ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      Reg VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      Cou VARCHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      BR  VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      KP  VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      SS  VARCHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      FT  VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      BM  VARCHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL

PRIMARY INDEX ( Reg );

Regards.

Enthusiast

Re: CREATE NEW TABLE with COLUMNS FROM four QUERIES + CONSTANT Fields

I do not know the Best Approach and I am a total Newbie with databases and SQL. Learing from Several Platfrom.

I have to create a Report by pulling data from database Views and exporting it into Excel Sheet.

The Columns of the Excel sheet has to be in correct sequence. The first 7 columns of the excel sheet will always be filled with fixed data.

The values of remaining 7 columns of the excel sheet are based on individual Select Query.

Each Select Query return weekly data of something. So Data among each 7 queries is different based on the conditions used in each query.

Each Select query has a common field "Date" and Count of Something. So Resultset for each query is always 2 Columns.

Example:

Date((CURRENT_DATE -7) and CURRENT_DATE), Count(Something)

The Goal is to Auomate this Process instead of Copying and Pasting Each ResultSet(Column) for Week Range.

/*-----First Create a Permenant Table in a Datbase----*/

Enthusiast

Re: CREATE NEW TABLE with COLUMNS FROM four QUERIES + CONSTANT Fields

CREATE SET TABLE Schema.MASTER_TBL,NO FALLBACK
No BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM=DEFAULT,
DEFAULT MERGEBLOCKRATIO

(Col1 CHAR(3) CHRACTER SET LATIN NOT CASESPECIFIC NOT NULL default 'NA' as "Reigon",

Col2 CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL default 'Brazil' as "Country",

Col3 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL default 'PPP' as "Brand",

Col4 CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL default 'Opt All (2,TT)' as "KPI",

Col5 CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL default '(2, TT)' as "Source System",

Col6 CHAR(6) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL default 'Weekly' as "Frequency Target",

Col7 CHAR(7) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL default 'TTT-CA' as "Brand/Market",

Col8 DATE(format 'DD/MM/YYYY' not null default date '01/01/2000') as "Date",

Col9 Integer as "Opp-Wt-Total",

Col10 Integer as "Opp-wt-1213",

Col11 Integer as "Opp-wt-1318",

Col12 Integer as "Opo-ott-Total",

Col13 Integer as "Requested S",

Col14 Integer as "Requested C",

Col15 Integer as "C Redeme",

UNIQUE PRIMARY INDEX (Col8);

Enthusiast

Re: CREATE NEW TABLE with COLUMNS FROM four QUERIES + CONSTANT Fields

Does above Syntax for Creating Permenant Table Structure looks correct?

Step2:

Insert all Fixed Values in a Table for 7 days along with Date Field. This Query Execute on 08/20/2013.

I want to Learn how to use CURSOR to loop through each each query independently.

I think this will allow me to Write the Resultset of each query Filling the remaining 7 Columns of the Excel Sheet.

Enthusiast

Re: CREATE NEW TABLE with COLUMNS FROM four QUERIES + CONSTANT Fields

No the DDL is not correct. While defining Col1, CHARACTER is misspelled as CHRACTER... You need to correct that.

Not exactly sure what you are asking in STEP 2... You need to ask questions with proper details :)

Re: CREATE NEW TABLE with COLUMNS FROM four QUERIES + CONSTANT Fields

Hi am trying to create a simple table in teradata.

my table has 4 colums and 2 rows 

column1=No

column2=Name

column3=science

column4=maths

row1=1,Abdul,50,49

row2=2,Kumar,49,50

could any one help me to know on how to create a simple table first in teradata