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 ensure atomic nature of multiple table actions? Topic by sdc 1 day ago

I am working on an application which generates a result that I want to store in a Teradata database.  The database consists of several tables and the application's result will span multiple tables.  I want to ensure either (1) a whole result is written or (2) nothing is written.  In other words, I want the result to be atomic.

50 views
1 reply, 3 hours ago
FASTLOADCSV Error JAVA Topic by zioli 1 day ago java jdbc fastLoad teradata

Hi Everybody, 
I  am having problem on using the FastLoad protocol. I am tryng to insert data, based on a csv file,  into a Teradata (V.14) table using the JDBC Teradata driver. Here it is my URL connection:
jdbc:teradata://XXX.XXX.XXX.XXX/DATABASE=XXX_XXX_XXX,TMODE=ANSI,CHARSET=UTF8,TYPE=FASTLOADCSV 
and here it is (in a simple way) my code:
 

        

37 views
1 reply, 7 hours ago
REGEXP_REPLACE does not replace all occurrences, even when setting occurence_arg=0 Topic by gordh 2 days ago

Hello Teradata forum users,
 
I'm on Teradata database version 14.00.07.15 and I want to use the REGEXP_REPLACE function on a varchar field, to replace everything that is not a digit (0-9):
     select 'abcde1234f5' as vat,REGEXP_REPLACE(VAT,'[^0-9]','',1,0,'I')AS VAT_CLEAN
This returns a correct answer:
     abcde1234f5  12345
 

102 views
3 replies, 8 hours ago
Cumulative BITOR Topic by Abhishek_RC 17 hours ago BITOR

Hi,

I want to write a sql which will help me find cumulative bitwise OR values as shown below - Most of the cumulative functions use SUM, AVG, MIN, MAX but I could not find a proper solution for this using Teradata SQL. Can someone please advise ? Thanks in advance.

 

SR value  BITOR_OP

1    1      BITOR(1,0)=1            

49 views
1 reply, 17 hours ago
How to convert column character set in Teradata table. Topic by TDFUser 1 day ago

Hi ,
My Requirement is that i want to change character set of a column. For some tables, i want to change columns character set  from Latin to Unicode and for some other tables from Unicode to Latin.
Is there any command available which i can execute in SQL Assistant directly with out drop and recreating table. Its very urgent .Please help.

59 views
1 reply, 22 hours ago
HOw to join on nearest lower value in teradata sql Topic by DeepakAsule 1 day ago

I have two tables in teradata 
table1
1
2
3
5
 
table 2
2
3
4
6
 
output
 
2 2 
3 3
4 3
6 5
 
if matcing value found col should join on matching value else nearest lower value.
 
Please help me 
 
I have to implement this scenario,,,It's urgent

102 views
6 replies, 1 day ago
116 views
4 replies, 1 day ago
UNICODE to CHAR Translate - TRANSLATE doesnt work. Topic by indrajit_td 15 May 2012

Hello,

 I need to insert data into target and the source table for which has the corresp column (SRC_COL1) defined as as "varchar(50) character set unicode" The target table has the col (TGT_COL1) defined as VARCHAR(50). Also I need to look back the existing values in target so my joining columns are SRC_COL1 & TGT_COL1. The below join works fine:

10337 views
9 replies, 1 day ago
Teradata Syntax Error Topic by mae 2 days ago
select  
all_members.member_amisys_nbr,
eff_date.date_date as effective_date,
end_date.date_date as end_date

from etl_access_own.dim_date as eff_date

inner join on
etl_access_own.dim_member_eligibility as member_eligibility
(eff_date.date_dim_ck=member_eligibility.eligibility_end_date_dim_ck)

inner join on
etl_access_own.dim_date as end_date
76 views
2 replies, 2 days ago
How to bring un-utilized space for utilization in terdata Topic by prasanth225 1 month ago

Hi,
I am going through some of the topics on space utilization and got confused. Can you please clarify the below points:
1. I have calculated
SUM(CurrentPerm)/1024/1024/1024 &
MAX(CurrentPerm) * (HASHAMP()+1)
 
for a particular database in DBC.DISKSPACE. The outputs are 1084 and 1.25 respectively.
 

985 views
9 replies, 2 days ago
Query to find tables with no stats defined/old statistics Topic by teradatauser2 28 Jul 2012

Hi,

I need a query to find the the tables in a database for which there are no stats defined yet (This will help me to tell uses to define stats). Also, i want to list down the tables in a database for which the stats are old (say 1 month old).

I dont want to do a help stats on every tables and find the details (practically not possible). Is there a query for this.

16006 views
13 replies, 2 days ago
Data Type Conversion BIGINT to INTEGER Topic by rohit.lalwani1 27 Jun 2014

I want to change  the datatype of few columns in one table from BIGINT to INTEGER. (Table is not Empty)
Is there any Alter Table statement or i have to used new table and then copydata and rename ??
Please suggest

536 views
2 replies, 2 days ago
Greenpum to Teradata Migration ( UDF hash_MD5() issue ) Topic by pankajcse1 3 days ago

We are having issue with implementing Change Data Capture through a UDF called hash_md5() which we created in our DB environment from the below link:-

111 views
3 replies, 2 days ago
Does one need unicode compression in Tredata 14.0 Topic by pmehrotr 20 Dec 2013

Iknow i Teradata 13.x, followng syntaxt is provided to implement compression for unicode
CREATE TABLE Customer

(Customer_Account_Number INTEGER,
Customer_Name VARCHAR(50),

1058 views
10 replies, 2 days ago
Database on VMWRE get stuck. Topic by indrajit_td 27 Jun 2013

Hello,
 
 I have been using the VMWARE - Teradata 14. The database on the VMWARE doesnt seem to be progressing and is stuck on this state of pde
 
TDExpress14.0_Sles10:~ # pdestate -a
PDE state is RUN/STARTED.
DBS state is 1/1: DBS Startup - Initializing DBS Vprocs
 
Can you please suggest.
 
Thanks,
Indrajit
 

966 views
1 reply, 2 days ago
Performance Tuning - Report for analysis Topic by tssr_2001 13 Jan 2010

Hi,In Oracle 10g, there are AWR and ADDM Reports. 1. AWR Report : It consolidates the information regarding TOP SQLs, All Database Configurations,.... which will be helpful to find the bottlenecks2. ADDM Report : Provides recommendations to rectify the problems in Database based on AWR Report.Is there similar kind of stuff in Teradata, to : 1. Identify bottlenecks2.

3454 views
4 replies, 2 days ago
Measuring Performance in DownSized Environment Topic by tssr_2001 2 days ago query performance, database, environment

Hi All,

 

I am measuring the performance of a query execution in a test environment and i have a following scenario..... This test environment has data volume equivalent to production environment, but its hardware is downsized compared to production environment, i.e., its 40% or 50% of production environment or configuration.

 

45 views
0 replies
Top percentage group of items, by variable Topic by Simpl3 4 days ago top, percent, by variable, ORDER BY

I am trying to get the top 20% of a larger grouping of items;
For example

233 views
5 replies, 3 days ago
Multiple Case Statements Topic by dqualls 3 days ago

I need a query to reference the below list and if IN this list, apply the first CAST statement, but if not in the list, apply a different CAST statement looking at unit_sell rather than unit_cost.

Any help would be greatly appreciated.

 

WHEN state IN('CT','DE','GA','KS','KY','ME','MI','NE','NJ','OH','OH','SD','WA','WV','WI') 

 

 

68 views
0 replies
Teradata Open Query syntax Topic by app087 3 days ago
 SELECT 
*
from openquery ([LPWVD4237,11210] , '
SELECT
lm.process_date, 
lm.lm_client
, lm.lm_loan,
lm.LM_SPOCID
, lm.LM_SPOC_Name,
lm.[LM Status] as LM_Status
,lm.[lm removal dt] as LM_Removal_Dt
from loss_mit..LM_Inventory_History lm
where lm.lm_loan IN (''0633812755'')
  and lm.[LM Status] IN (''R'') ' )
176 views
3 replies, 3 days ago
Invalid defaulted table ? Topic by maxedison 4 days ago Unexpected results

Hi -
I've a question to which I can't find a satisfying answer and I hope someone may be able to respond with 'chapter and verse' information.
The following SQL works in TD 14.10 as long as there is a 'DB_WRK' database available for the user to create the two tables indicated:
 

85 views
1 reply, 3 days ago
DB2 to Teradata Migration Topic by raj_2008 4 days ago migration, data-type, data type conversion

Hi,
I am presently working on DB2 to Teradata migration. I am trying to convert one DB2 data-type to its Teradata equivalent. Can anyone please help me figure out what will be the Teradata equivalent for the following DB2 data-types -

  • TIMESTAMP(6) WITHOUT TIME ZONE
  • CHAR(x) FOR SBCS DATA

Any help is appreciated.
Thanks,
Raj

127 views
1 reply, 3 days ago
Finding and Flagging a record with Non-Latin Characters Topic by ejsk 4 days ago

Hi TD Experts -
My background is in SQL Server and have been recently a migrant to TD.  My question is:
 
I need to identify if a record contains a non-Latin character.  This is not a do something once found, but a simple if the character exists, return 1, else 0. 
 
I imagine I am overthinking this - and tried a couple of find functions - with no success.

172 views
2 replies, 4 days ago
Table design question Topic by teradatauser2 4 days ago

Hi,
I have a table design related question.

85 views
0 replies
Usage of Staging tables with NoPI and Insert/Select Topic by raj_2008 2 weeks ago NoPI table

Hi,
It is often recommended to first load the data to a Staging table with NoPI and then use Insert/Select to load the data from the Staging table to the base/production tables vs loading the data directly to the base/production table(s).

225 views
2 replies, 4 days ago

Pages