All Forums Database
step_runner 3 posts Joined 04/16
21 Apr 2016
Terradata : Terradata not taking table-name for query.

I am working on Terradata SQL query, and in that query I am performing a join. Unfortunately, terradata is not accepting the table name as it is because there is a . or period in table name.
Query :
 

Insert TEST (NAME) VALUES((
sel
smallname||' '||bigName
,upper(smallname)
,upper(bigName)
from domain.sourceTable as a
join domain2.destinationtable as b on b.someId=a.otherId))

Error log :
 

5628: Column smallname not found in domain.a or domain2.b
 
What am I doing wrong? Any ideas. Thank you. :-)

AtardecerR0j0 71 posts Joined 09/12
21 Apr 2016

smallname and bigName must be a column name of any table you use in your query.
Are you sure domain.sourceTable or domain2.destinationtable have any column with those names?

Be More!!

yuvaevergreen 93 posts Joined 07/09
21 Apr 2016

Check for the column name

step_runner 3 posts Joined 04/16
21 Apr 2016

I have removed the 2 more columns which I was trying to get. Modified SQL query is :

Insert TEST (NAME) VALUES((
sel
 smallname||' '||bigName
from domain.sourceTable as a
join  domain2.destinationtable as b on b.someId=a.otherId))

smallname and bigname exist in the DB. But the query processing is going wrong is what I think, why is it trying to look for domain2.smallName instead of domain2.destinationName.smallName is what I don't understand.

yuvaevergreen 93 posts Joined 07/09
21 Apr 2016

Its searching in the table...thats the reason the error is
5628: Column smallname not found in domain.a or domain2.b...
In table a or table b, the column name should be present...

step_runner 3 posts Joined 04/16
21 Apr 2016

It is. That's what I am saying. It is not printing the correct table name then if it's searching. Why is it printing domain2.smallName instead of domain2.destinationTable.smallName is my only problem. I believe the dot is causing a problem.

ToddAWalter 316 posts Joined 10/11
21 Apr 2016

As specified the expression says:
select <the result of concatenating the contents of column smallname with a blank with the contents of column bigname>.
It does not say:
select <from a new columname created by concatenating the contents of those columns>
It is not possiblle to create a new columname with an expression in a directly specified SQL text. Only if creating the SQL with dynamic SQL in a stored procedure or by generating the SQL with some program outside.
 
In either case, smallname and bigname are going to be column references so they would need to exist in the referenced tables in the query.

Fred 1096 posts Joined 08/04
21 Apr 2016

Are you just saying that the wording of the error message is a bit misleading and it would be clearer to say:
Column smallname not found in a or b
or
Column smallname not found in domain.sourceTable or domain2.destinationtable
since there is actually no table named domain.a or domain2.b?
 
 
 

hpanthi 1 post Joined 04/16
28 Apr 2016

That is the normal way of returning error messages when one uses alias for table. Teradata always uses alias name (domain.a and domain.b in this case) while showing the error messages.
Most probably, your column is present with different alias name. Can you share the table structure (SHOW TABLE) for the column (smallname) that you are trying to retrive. 
/Hemant
 

You must sign in to leave a comment.