creating table with sum-function

Database
Enthusiast

creating table with sum-function

Hi everyone,

Im trying to create a table but when try to run it, it says:  

EXPLAIN Failed.  [3707] Syntax error, expected something like a 'CHECK' keyword between ',' and the 'SUM' keyword.

Is there someone out there who knows what this might mean?

CREATE SET TABLE QFU.QFU_Operation_material_cost ,NO FALLBACK ,NO BEFORE JOURNAL,NO AFTER JOURNAL,

CHECKSUM = DEFAULT,

DEFAULT MERGEBLOCKRATIO

(

repair_order_number VARCHAR(7000) CHARACTER SET LATIN NOT CASESPECIFIC,

repair_order_suffix VARCHAR(7000) CHARACTER SET LATIN NOT CASESPECIFIC,

job_number DECIMAL(18,0),

MAT_OP_FLAG VARCHAR(7000) CHARACTER SET LATIN NOT CASESPECIFIC,

MAT_OP_NUM DECIMAL(18,0) ,

MAT_OP_DESC VARCHAR(7000) CHARACTER SET LATIN NOT CASESPECIFIC,

COST DECIMAL(18,0),

SUM(BASE_JOB.vm_lab_cost) DECIMAL (18,0) vm_lab_cost,

SUM(BASE_JOB.vm_subl_cost) DECIMAL (18,0) vm_subl_cost,

SUM(BASE_JOB.vm_tot_cost) DECIMAL (18,0 vm_tot_cost)

)

PRIMARY INDEX (MAT_OP_NUM)

Have a great one!

10 REPLIES
Enthusiast

Re: creating table with sum-function

How are you going to populate 

repair_order_number VARCHAR(7000) CHARACTER SET LATIN NOT CASESPECIFIC,

repair_order_suffix VARCHAR(7000) CHARACTER SET LATIN NOT CASESPECIFIC,

job_number DECIMAL(18,0),

MAT_OP_FLAG VARCHAR(7000) CHARACTER SET LATIN NOT CASESPECIFIC,

MAT_OP_NUM DECIMAL(18,0) ,

MAT_OP_DESC VARCHAR(7000) CHARACTER SET LATIN NOT CASESPECIFIC,

COST DECIMAL(18,0),

CREATE SET TABLE is just a data definination language.

SAP
Enthusiast

Re: creating table with sum-function

Hi , Can we create tables with ADD_MONTHS operation or any other arithmatic operation ??? Because in SQL Server , we have datediff operation on one of its field in DDL . Do we have such concept in Teradata ?

Thanks in Advance !!!!

Enthusiast

Re: creating table with sum-function

Yes very much you can, example below:

create table db1.itemppi_test as(select l_orderkey as lo, l_partkey as lp, add_months(l_shipdate,2) as dd from 

db1.itemppi) with data

SAP
Enthusiast

Re: creating table with sum-function

Thanks Raja !!!! But can u pls help me to convert below mentioned DDL to Teradata syntax error free DDL .

CREATE MULTISET TABLE DATABASE.NAME

 ,NO FALLBACK

 ,NO BEFORE JOURNAL

 ,NO AFTER JOURNAL

 ,CHECKSUM = DEFAULT

 ,DEFAULT MERGEBLOCKRATIO

 ( 

 Starttime TIMESTAMP(6) NULL,

 endtime TIMESTAMP(6) NULL,

 secondsCNT AS (datediff(second,PackageStartDTS,PackageEndDTS))

 )

UNIQUE PRIMARY INDEX(secondsCNT)

;

I am confused at datediff part . How will i convert this to Teradata in DDL ?

Thanks in advance !!!

Enthusiast

Re: creating table with sum-function

I am not sure of datediff but perchance doable.It looks like datediff is a complex function. I think you take from some other Databases?

Why do you do transformation in a DDL? DDL is meant for data defination.Small work like default is acceptable. DML is meant for data manipulation. 

There are some Databases they want to cram as much as possible into inappropriate place. For me it looks like doing kitchen work in bedroom( and sleeping in kitchen) :). Slowly we  are forgetting the basic of computer----logic(al), which means step by step. Just kidding :)

SAP
Enthusiast

Re: creating table with sum-function

LOL :) :) Yes , I have taken it from MS SQL Server !!!! They have it in such way . :) :) Thanks for your reply !!! :) :) Have a fun friday !!!

SAP
Enthusiast

Re: creating table with sum-function

Hi Raja ,

Can u pls help me on using While LOOP in Stored procedure ? Is it legal to use things as below : - correct me if i m wrong

WHILE (SQLCODE = 0) do

...

end while;

but it fails in studio express 15 :(

Enthusiast

Re: creating table with sum-function

SAP
Enthusiast

Re: creating table with sum-function

Thanks Raja !!!! Sorry for buggin again !!! Can u pls put your thoughts on below points :-

1. I am unable to use EXECUTE IMMEDIATE or even EXECUTE to run Dynmaic SQLs in stored procedure . But  i am able to use DBC.sysexecsql . What s the reason ?

2. However ,  am unable to create any tables inside stored procedure along with other DML operations . But i can create it separately in a stored procedure only with DDL .

3. When i create volatiletable  inside stored proc and have an insert statement and create a proc . It says Volatile table doesnt exist

ex: REPLACE PROCEDURE dfg.sdfffffff

begin 

call dbc.sysexecsql('create volatile ......;');

Insert into volatil values (chsc);

4. Do we really need to use ':' host variables while passing parameters ?

Note : I m using TD 14.01 with TD Studio Express 15 . 

Do i need to do any settings change or ask my DBA to work on something because this s new setup created just few days before ?

Thanks in advance !!!!