All Forums Database
Lillian_Ding 2 posts Joined 09/14
24 Sep 2014
Error: Syntax error: expected something between the word 'a' and '.'

Hi all,
I'm new to Teradata and just started using it a week ago. I have trouble figuring out what is wrong with the following short code:
 
create table c as
( select a.col_1, a.col_2, sum(a.col_3) as total_col_3, count(a.col_4) as cnt_col_4
from TABLE_A a, TABLE_B b
where a.col_5=b.col_5 and a.col_6=b.col_6
group by 1,2) with data primary index(a.col_5);
 
Any advice or suggestion would be highly appreciated. Thanks!
 

Raja_KT 1246 posts Joined 07/09
24 Sep 2014

You do not have a.col5.
Give an alias to all columns. It is a good way to identify.
Try this, putting a.col1 as PI. You can change later.
create table c as
( select a.col_1 as jj, a.col_2 as kk, sum(a.col_3) as total_col_3, count(a.col_4) as cnt_col_4
from TABLE_A a, TABLE_B b
where a.col_5=b.col_5 and a.col_6=b.col_6
group by 1,2) with data primary index(jj);
 

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.

Lillian_Ding 2 posts Joined 09/14
25 Sep 2014

Hi Raja_KT, thank you for your help. It solved the problem.

kingkong 6 posts Joined 02/15
04 Feb 2015
select a12.xx  xx,
 a12.bb  bb
from (select a11.zz zz,
 CASE WHEN substring(a11.zz ,2,1)  between '0' and '9' THEN substring(a11.zz,1,1)  ELSE substring(a11.zz,1,2) END zz
from table1  a11) a12
group by a12.xx,
 a12.bb

Hi Guys Very helpful post. I am new to Teradata and getting  similar eror with  the above case statement. can yu help please. This is puzzling.
 
Thanks in advance
 
 
 
 

dnoeth 4628 posts Joined 11/04
04 Feb 2015

Your SUBSTRING uses ODBC syntax, which is not valid, it's either substring(a11.zz from 2 for 1) or substr(a11.zz ,2,1)

Dieter

kingkong 6 posts Joined 02/15
04 Feb 2015

thanks a million Dieter. I will test it tomorrow and let you know how it went.
 

kingkong 6 posts Joined 02/15
05 Feb 2015

Hi Dieter. Thanks again.  it fixed the issue.
 
 

You must sign in to leave a comment.