All Forums Database
emilahlstrom 11 posts Joined 11/13
18 Sep 2014
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!
 
 
 
 

Raja_KT 1246 posts Joined 07/09
18 Sep 2014

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.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

SAP 73 posts Joined 08/14
18 Sep 2014

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 !!!!

SAP

Raja_KT 1246 posts Joined 07/09
18 Sep 2014

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

SAP 73 posts Joined 08/14
18 Sep 2014

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 !!!
 

SAP

Raja_KT 1246 posts Joined 07/09
18 Sep 2014

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 :)
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

SAP 73 posts Joined 08/14
18 Sep 2014

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

SAP 73 posts Joined 08/14
18 Sep 2014

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 :(
 

SAP

Raja_KT 1246 posts Joined 07/09
18 Sep 2014

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

SAP 73 posts Joined 08/14
19 Sep 2014

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 !!!!
 

SAP

Raja_KT 1246 posts Joined 07/09
19 Sep 2014

I suggest you provide the code, what you did and what errors you get and in what environment...like Studio/assistant/unix/ Teradata BTET or ANSI mode...... Sometimes a quick answer gives wrong result.
Try with small examples, and clear your doubts.
Also I see that when you put all problems in one post, it does not get answered :).
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.