All Forums

Topics related to the Teradata Database, excluding database connectivity (e.g. JDBC) and extensibility (e.g. UDFs) . Typical topics are about SQL syntax and usage (SELECT, GROUP BY etc), database performance, use of database functions, comparison with / migration from Oracle or DB2, and generally why the Teradata Database rocks. For articles, blogs and more, see the Database section of the site.

You must login to post to the forums.
How to store output of show table in a variable in Stored procedure Topic by teradatauser2 1 day ago stored procedure

I am writing a stored procedure in which i need to drop a table recreate it and keep the defn of the table in an audit table. So, firstly, i am creating backup table as the original table with no data. then dropping the original table and recreating the original table with backup table. This is quite simple.

2 replies, 1 day ago
create a composite Collect stats command Topic by v_gabrielli 3 days ago

SELECT  databasename,tablename,indexnumber,columnName, columnPosition,
'COLLECT STATISTICS ON ' || TRIM(databasename)   ||'.'||   TRIM(tablename) ||' INDEX (' || TRIM(columnName) || ');' 
FROM dbc.indices
WHERE indextype in ('P','S')
    AND databasename='MENSA_TABLE'

3 replies, 1 day ago
Join Index and Value Ordering Topic by oshun 1 week ago join index

The follwing is not clear to me: We have value ordering for Join Indexes, but at the same time the Join index has a Primary Index and is ROWHASH distributed. For me this means, Master Index and Cylinder Index carry the information about on which Data Blocks a searched ROWHASH can be found. This is done with a binary search on MI/CI. 

2 replies, 1 day ago
Most efficient spatial query?? Topic by raulbenet 2 days ago efficient, spatial, geospatial, WKB, WKT, query

Hello there,
I have table in Teradata Express that contains the countries of the world. The NAME column contains the country names and the GEOM colum contains their geographical information (the GEOM colum is of type ST_Geography). I created this table by importing a shapefile using Teradata's TDGeospatialImporter tool.

0 replies
SQL to TERADATA Migration Topic by SHERIN JEYABOSE 4 days ago
  1. CONVERT (VARCHAR,COL1,23))AS VARCHAR(100) - here VARCHAR is used like an attribute in SQL whereas it is a datatype.  Will TD accept such ?
7 replies, 2 days ago
Problems with "WITH(NOLOCK" Topic by aolympio 03 Apr 2009

Hi!I'm trying to perform a query like this:SELECT FIELD FROM SOME_TABLE WITH(NOLOCK) WHERE FILD > 0;But it returns this error:"5628: Column NOLOCK not found in MY_DATABASE."What can I do to perform my query using WITH(NOLOCK)?Is it possible in Teradata or is there another solution to get the same result?Thanks for help,Anderson

7 replies, 2 days ago
View TPT Log Topic by john9 2 days ago

Hi ALL -
I have executed a TPT script with export operator and able to generate the output data file successfully......and i also see a log file in the path

1 reply, 2 days ago
System Views DBC.COLUMNSTATS & DBC.MULTICOLUMNSTATS not existig in TD 14 Topic by ankitkhandelwal 15 Jul 2013 DBC.COLUMNSTATS, MULTICOLUMNSTATS

Hey All
I have a procedure which creates dynamic SQL statements to collect stats on table after its loaded.
The procedure gathers stats from two tables DBC.COLUMNSTATS & DBC.MULTICOLUMNSTATS.
Now this procedure works fine in TD 13.10 but fails in TD 14 saying dbc.COLUMNSTATS does not exist.

4 replies, 2 days ago
CREATE TABLE AS STATEMENT ERROR Topic by achikan01 3 days ago

Apparent anomaly in the functioning of the CREATE TABLE AS statement. In the following SQL, the B_TEST table DDL gets created with double-quotes around the CHECK col in 2 cases:

3 replies, 2 days ago
Dynamic Date Range in where clause Topic by vinaynaran 4 days ago

Hi all,
 I have below query where I have hardcoded date-2 and date-1
 from SL_Table
 WHERE calendar_date between date-2 and date-1
 group by 1
 Is there any change where I can have dynamic date range?

4 replies, 2 days ago
Extra space is getting added with data while loading data into varchar data type column Topic by ksaikrishna 4 days ago

Hi All,
 I am getting  extra space while inserting integer data into varchar data type column.
Please explain the reason behind this.
create volatile table rowhash_my,no log
col1l varchar(20)
on commit preserve rows;
insert into rowhash_my values (123);

2 replies, 4 days ago
Migration of Oracle UDF to Teradata Topic by vinaywani 1 week ago

I am trying to migrate an Oracle view to Teradata
which uses oracle user defined function in select clause
and function contains the logic which cannot be migrated to TD SQL UDF.
Has anyone faced this situation before?
Is there any other approach apart from writing c/java UDF?

1 reply, 4 days ago
Row Counts of all Tables in a database Topic by abhijit.p 1 week ago #ROWCOUNT

Hello All,
I faced one scenario where I couldn't find any solution.
Let's take an example. 100 Tables are there in a database. How to know the row counts for all the 100 tables in this database.

4 replies, 4 days ago
Cant make this Teradata Create procedure work Topic by RodRicardIII 5 days ago

Create Procedure StateAirportInventory
In Parm1 varchar(12)
Dynamic Result sets 1
Declare c Cursor With Return Only\
Select state, airname
From airport
where state = parm1
Order by airname desc
Open c;

1 reply, 4 days ago
Execute immediate - exception handling Topic by DS 5 days ago

Hi All,
I am preparing  a dynamic insert statement in stored procedure, i am excuting it by using execute immeidate.
execute immediate (dynamic Insert statement) is failing with sqlcode -5628 ( Column %FSTR not found in %VSTR.). 
I want to continue execution by handling this error. can someone give an example to handle dynamic sql error/exceptions.

2 replies, 4 days ago
Teradata VM Space increased too much Topic by SamiShah 5 days ago

Hi All,
I have Teradata 15 VM. When i copied it, it took around 18 GB. I connected it with Tableau and there was a join between two tables. Total records of both tables are around 350,000. The query in tableau took around 20 minutes and than VM displayed an error ("Out of disk space") and it crashed.

0 replies
Storing with recursive result set Topic by mnagara 5 days ago capturing with recursive result set, storing with recursive result set, with recursive, recursive

Hi Guys,
i'm using 'With Recursive' to fetch required result set, i need to insert this result set into another table. Please let me know how we can do this.
I tried below syntax, but it's not working, 
with recursive <recursive_name>
(select column list)
select * from seed table
union all

2 replies, 5 days ago
Overlapping time periods, competing records by date created. Topic by ScottDufour 1 week ago Period functions, overlap, competing periods.

I have a very large dataset of multiple part number with different pricing for different time periods.  Often all or a portion of a time period is superceded by a later record, and then returns to the original price after the superceding period ends.  I need a querty that sorts all this out and gives me a result showing the correct price in discrete time periods.  I've tried many different atte

2 replies, 5 days ago
Cold Tables and MVC probability Topic by Shock 6 days ago

i've written a view that reads from the heatmap view (and function) and gets you the list of the cold tables (Teradata 14.10) so you can find a list of possible candidates to compress!

0 replies
Special Characters in Teradata Topic by rtakle_d 05 Sep 2013 special characters, junck characters in Teradata

We are facing an urgent problem where we need to find out the special characters in a Teradata column. Do you know how can we find it?
The special character can be smiley/@#$%^&*/any winding font character. We don’t have a comprehensive list of special characters.
Below potion we can use …

17 replies, 6 days ago
Looping in teradata Topic by mnagara 1 week ago database, string splitting, stored procedure, looping, pivoting

Hi Guys,
I need some info on looping in Teradata with below requirement. Anny help will be appriciated. Thanks in advance,
source field is string which is having below format,

2 replies, 6 days ago
TeraData Topic by pvmscs 1 week ago

Any good tranning institute to learn teradata with ream time example.

1 reply, 6 days ago
Content of Row Pointer Array for Value Ordering Topic by oshun 1 week ago nusi, Cylinder Index, master index, value ordered

In case of a NUSI ordered by ROWHASH the array of row pointers in each NUSI data block contains the orderd ROWID of its rows. Basically (leaving the UNQIUENESS value beside), it is ordered by the ROWHASH.

0 replies
Data Reconciliation Topic by Dhanraj8080 1 week ago

Hi ,
I'm new to Teradata and in learning stage , could any one help me on Data reconcilation of around 20k tables. is there any Teradata Utility that would make Huge Data Comparison in an easy way ,with out much handcoding . Or how to handcode in an efficient way so that dynamic query handle different table level Data comparison.

0 replies
How to find No of rows that a query has returned? Topic by ajai_47 06 Aug 2007

hii want to find the no of rows returned by a select query.based on the no of rows returned i have to run another query. i.e if the query returns one row then a query should be run and if it returns more than one row another query should be run.. it should be like * from table1if no of rows returned = 1 thenselect * from table 2else if no of rows returned > 1 thenselect * from table 3end ifi dont know whether we can use if conditions like this in between queries, but my requirement is like this only. please help me on this.

7 replies, 1 week ago