Questions tagged [teradata]

Teradata is a parallel 'shared-nothing' database management system. It is primarily used for data warehouse applications with large data volumes.

Filter by
Sorted by
Tagged with
-1 votes
0 answers
10 views

Teradata: Lag? other uses to get previous date and reset on new date?

Should I be using LAG or Rows between 1 preceding and and current row? Case When prev_date = StartDate then 1 Else 0 End as count1, Lag(STARTDATE) Over (PARTITION BY AssignedTo ORDER BY ...
user avatar
0 votes
0 answers
29 views

Best approach to extract 1000 Gbs of Data from Teradata

What should be the best approach to download data from Teradata using python ? I have more than 3000 GBs of data to be migrated from teradata to another code service. Any approach or advise would be ...
user avatar
0 votes
0 answers
11 views

Download DML and DDL teradata SQL files in the local system using python

I am doing a schema migration project and what approach should I follow for downloading the DML and DDL files into the local system from teradata database using python. Unlike mssql-scripter and ...
user avatar
0 votes
0 answers
21 views

Teradata Large Table Partitioning or Split into multiple parts

We have a large table (4 Terabytes) in Teradata. For compliance reasons, we need to keep 13 months worth of data. However, for querying, we may only need one month worth of data (300GB). I have ...
user avatar
0 votes
1 answer
216 views

Teradata min and max dates Find the Employees Start Date and Time and End Date and Time for each date

I have columns AssignedTo, InternalID, Task Function, Min(STARTDATE), Max(ENDDATE) AssignedTo InternalID Task Function STARTDATE ENDDATE Joe 67077 Write the letter 08/24/2017 05:30 AM NULL Joe ...
user avatar
2 votes
2 answers
90 views

Is the order of columns in a clustered index important?

Does it matters which field I specify first in the index? would CREATE SET TABLE my_table ( validity_date DATE , branch_id SMALLINT , account_class_id BYTEINT , ...
user avatar
0 votes
1 answer
46 views

Outer Join on all columns if possible, or a subset of columns if not possible, and sum

I have two tables, call them T1 and T2 each with 4 columns A, B, C, Quantity. I want to do an outer join. However, some ABC combinations in T1 do not match with T2, and vice versa. My goal is to join ...
user avatar
-1 votes
1 answer
171 views

How to measure text similarity (Jaro-Winkler) in Teradata?

In Oracle we can measure text similarity with Jaro-Winkler like the following: SELECT UTL_MATCH.JARO_WINKLER_SIMILARITY('STACKEXCHANGE', 'STAMPEXCHANGE') MYSTRING FROM DUAL; --98 And it turns out ...
user avatar
  • 157
1 vote
1 answer
98 views

In Teradata SQL how to get random number between a pair, coming from field/column?

I am recently got stuck upon a simple problem in Teradata: how to get random number between a pair of number coming from field/column? The function RANDOM( lower_bound , upper_bound ) apparently is ...
user avatar
  • 157
0 votes
1 answer
85 views

How can I get all CREATE VIEW statements in Teradata?

How to get all CREATE VIEW statements with a single query? This Query is used to get the definition of a single view: show view db.tablename;
user avatar
0 votes
1 answer
62 views

How do you call an external C function from a SQL stored procedure?

In Teradata 17, I am trying to call their own hash_md5 external function from my own SQL stored procedure: replace procedure p() sql security creator begin declare l_hash varchar(32); set l_hash = ...
user avatar
0 votes
0 answers
73 views

A function that can be used directly in SQL cannot be used from a stored procedure

In the Teradata 17 instance that I work with, there is external function hash_md5 that Teradata provides. I can run all tests of this function suggested by Teradata, as long as I do it in the studio. ...
user avatar
0 votes
2 answers
430 views

how to select the not null value of a column when it has both null and not null value in teradata?

consider a table named "TABLE-A" with below data. ID hcc D_CD 1 null 1 1 5 1 2 null 2 2 10 2 2 20 3 3 null 3 I need to select ID,hcc fields from ...
user avatar
  • 117
0 votes
0 answers
21 views

How to select the values in transposed manner(as a new field) with teradata?

Consider a table named "Table1" which has coulumn1,column2...n i need a query which fetched the data like below. TABLE1: id Line_nbr Exam_cd 1 1 ENG 1 2 ...
user avatar
  • 117
-2 votes
1 answer
47 views

how to select the data in Teradata if it has duplicates in it?

consider TABLE1 and has the below records. ID MBR_ID STRT_DT END_DT ID_TYPE 1 001 01-01-2019 31-12-2019 TYPE_1 2 002 02-05-2019 31-07-2020 TYPE_1 3 002 ...
user avatar
  • 117
0 votes
1 answer
100 views

how to get monthly date in teradata

I have a table called oth_mbr. I need to get 3 years of data month wise. current date minus 36 months and each month data betwwe from_dt and thru_dt needs to be fetched. data shoulbe be in below ...
user avatar
  • 117
0 votes
1 answer
290 views

How to select the members who has the age more than 18 (from current date) by SQL in teradata?

If we are having a table named 'MEMBERS' with below details, then how to fetch the data from the table with the age 18 or more? S.no Name DOB City 1 sam 19890203 USA 2 andrew ...
user avatar
  • 117
0 votes
0 answers
40 views

Migration from Teradata to MYSQL over partition by

Working SQLs in Teradata select row_number() over (partition by job_name, parent_job_name order by elapsed_sec) as rowno from schema.xyz select min(elapsed_sec) over (partition by job_name, ...
user avatar
  • 3
0 votes
0 answers
31 views

Multiple join criteria with different keys?

I am having a hard time formulating this question for a targeted search and so none of the results I am getting seem to describe my exact situation. I work in healthcare and our tables are enormous, ...
user avatar
  • 1
3 votes
2 answers
283 views

How to join by the most recent preceding date?

Imagine I have a database with historic price data and a second table with dates A new row was entered into the price history table every time the price changed. How can I (efficiently) join the two ...
user avatar
-2 votes
1 answer
42 views

Merge Multiple Selects in one request [closed]

I have to extract a data using selects statements based on same conditions with a little change, I would like to make union for all my select in one statement: select Date_, application_name,count(...
user avatar
1 vote
0 answers
108 views

Teradata 'Column Dictioanry Name' and 'Column SQL Name' definitions and differences between them

With the following sql query Teradata shows a lot of information about the columns of a table. HELP TABLE Database_Name.Table_Name; Two of them being Column Dictionary Name and Column SQL Name. ...
user avatar
-1 votes
2 answers
51 views

SQL to return preferred duplicate rows

Have a data set that some rows have a duplicate. Looking to return the row with IND = Y if it is present, otherwise to return the N. +------------+---------------+------------+----------+ | ...
user avatar
  • 1
0 votes
1 answer
2k views

Select rows from table 1, table 2, then selecting based on ID

I am dealing with a large dataset, and have run into some issues because of this. I have a data set like: Table1 State ID Conditional_Value AZ 1 Yes AZ 2 ...
user avatar
0 votes
1 answer
149 views

Does teradata provides free or trial setup of multinode (at least 2 node) environment

I want to test my code on Teradata on 2 node environment. It doesn't need a power of multiple AMPs multiple nodes ( more than 2 ). Like Teradata provides VM of single node setup of its every version, ...
user avatar
1 vote
0 answers
364 views

Teradata Install 32bit appears to have failed

I'm getting the error "The installation of ODBC -32bit appears to have failed. Do you want to continue the installation?" and I'm not sure how to solve it. I've never installed teradata before, and I'...
user avatar
0 votes
2 answers
1k views

Different calculations in EXCEL and TERADATA SQL

The calculation select (75/979.86)*979.86 in Teradata SqL gives 78.3888. In excel, it would give 75 simply and that is logically correct. I get it that SQL is rounding off 75/979.86 to 0.08 ie the ...
user avatar
1 vote
2 answers
6k views

Remove all Null values from Sql table at once

I am very new to Teradata and SQL environment. I am trying to remove all the null values from sql table in teradata using ISNULL(COLUMNNAME,0). But it gives an error that "the data type does not match ...
user avatar
1 vote
1 answer
6k views

how to bring in the most recent record in SQL in Teradata

I have a table that contains store number, inventory $, inventory year from 2014 through 2018. so if store 111 has inventoried in all the years, I will have 5 records for that store: Store Inventory ...
user avatar
  • 11
0 votes
1 answer
685 views

How can I generate object DDL, like the CLI "show" command does?

When we run the show command, e.g. show table db.tablename; show view db.viewname, it lists the object definition. I believe it will query the dictionary tables and rebuild . Could you help us to ...
user avatar
0 votes
1 answer
192 views

For each row, find the next closest value in another table (Teradata)

I've seen a few close examples here but I'm not quite getting something close enough for me to noodle out a fix. I have events, where trucks come in and out of a gate and I have two tables. The ...
user avatar
  • 1
-1 votes
1 answer
175 views

How create copy SYSDBA?

now I have 2 user, DBC and SYSDBA. I want to create else 5 users with same rights as SYSDBA. Please help to write script.
user avatar
0 votes
1 answer
885 views

How make DBMS_OUTPUT?

I know in Oracle DBMS_OUTPUT.enable; DBMS_OUTPUT.put_line(in_COMP); Now I writing procedure in Teradata create procedure SANDBOX.CreateNewUser () begin DECLARE errstr VARCHAR(1000); begin ...
user avatar
1 vote
1 answer
356 views

How create user with admin option?

I have Teradata v16.10.0. I want to create user with admin options: with possibility get to grant select any tables/views. How to create such user right?
user avatar
0 votes
1 answer
2k views

Teradata: Get quarter for specific date

When selecting the maximum date of a column how do I obtain which quarter that date falls into without using a CASE statement?
user avatar
1 vote
2 answers
5k views

Calculate period start and end date

My table: Date Employee Status ----------------------------- 20171106 001 At work 20171107 001 Sick leave 20171108 001 At work 20171109 001 At ...
user avatar
  • 313
1 vote
2 answers
3k views

Database explorer disabled in SQL Assistant

I am a new Teradata SQL assistant user. I have used SQL Server Management Studio before. In the View option, I am unable to toggle the Database Explorer. Please let me know how I can access the ...
user avatar
  • 113
1 vote
1 answer
3k views

Group multiple rows with different column values to a single row

I have 3 records with 3 column values for the same key. PFB sample data. CREATE TABLE student ( student varchar(100), maths int, physics int, chemistry int ) PRIMARY INDEX (student) ...
user avatar
  • 21
0 votes
1 answer
438 views

Teradata ODBC - version 14 and 15 side by side

Is there a way to install version 15.11 alongside version 14.10 of the ODBC Teradata drivers? I've run the 15.11 install wizard but it seems to want to remove all previous versions automatically. ...
user avatar
1 vote
0 answers
536 views

Teradata SQL Assistant - CALL Failed HY000: User Defined Function source on the client is not supported by the driver

Using Teradata SQL Assistant to install a user defined R script fails with an error. The call looks like this: SET SESSION SEARCHUIFDBPATH = demouser; -- Tells the linux user tduser, where to find ...
user avatar
3 votes
1 answer
7k views

Teradata: Select Table With No Lock

As I understand, when selecting from a table in Teradata the table is locked by default. I would like to do a simple select * from table without locking the table. Meaning, i want to do a select and ...
user avatar
1 vote
2 answers
4k views

More efficient SQL for different databases with same schema

I have data stored in different monthly databases that have the exact same name schema for the tables and columns. Each monthly database has ~10 million rows. I'm looking for a more efficient way to ...
user avatar
  • 13
1 vote
2 answers
70 views

Aggregating spend by customer where accounts can be re-assigned to different customers

I want to join unique customer ids with their spends. Problem: The account_no can be assigned to a different customer (A,B,C etc) at different point in time (assume an employee A leaving the ...
user avatar
1 vote
0 answers
339 views

Teradata backup information query

I'm looking for a way to query the last backup time for each database in Teradata 14.10. I am piecing together something with info from DBQL tables, but am unable to get anything that seems to be ...
user avatar
3 votes
1 answer
581 views

Teradata to drive responsive web application

Does anyone have experience using Teradata to drive a typical, responsive client-side web application? What is your evaluation? Specifically, how well does it perform in single-read and single-write? ...
user avatar
1 vote
0 answers
136 views

Teradata REST API performance metrics

What are single read and single write speeds like with the Teradata REST API? Is it fast enough to be used as an operational database for a typical responsive web application? What is the difference ...
user avatar
0 votes
1 answer
71 views

Explanation of the query [closed]

Can somebody explain me the below query? select SUBSTR(SUBSTR(PART_SEQ_NBR, POSITION('_' in PART_SEQ_NBR )+1, char_length(PART_SEQ_NBR) ...
user avatar
  • 1
1 vote
2 answers
61 views

SQL to combine records only if data is available [closed]

I have a table with the following format: id description1 description2 123 null abcd 123 zyxw abcd 124 null efgh 124 vuts ...
user avatar
1 vote
1 answer
918 views

how to execute unix commands within nzsql?

I'm converting a script from Teradata to Netezza, and I'm stuck at the portion where BTEQ uses the functionality of the OS to check if a file contains any data. If it does, it is deleted. I want to ...
user avatar
1 vote
1 answer
420 views

Teradata 15 : Failure 5619 Role nesting exceeded single-level

I am using Teradata 15. In Active Directory, We have a policy to assign a universal group, which has people, to a local group, where permissions are assigned. So I create two Teradata roles to mimic ...
user avatar
  • 339