Questions tagged [teradata]
Teradata is a parallel 'shared-nothing' database management system. It is primarily used for data warehouse applications with large data volumes.
75
questions
0
votes
1
answer
219
views
Similar to how there exists a volatile table in Teradata SQL, how can I create a volatile table or something similar in Azure?
Seen below is the code of how I would create a volatile table when I was coding in Teradata. I liked it because it would be the same structure and format as a normal query, but it would make a ...
-2
votes
2
answers
271
views
Next 90 day review date (Teradata)
Teradata Related question… I have a table containing new starters along with a start date, every 90 days from the start date I need to conduct a review with the starter
How do I identify the next 90 ...
- 1
0
votes
1
answer
345
views
Teradata: Temporary Table vs Create and Drop Table
Currently I have seen an ETL process where a user created tables that are used to store an intermediate result and then drop the tables at the end of the ETL process.
At first glance it seemed to me ...
0
votes
0
answers
143
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 ...
- 101
0
votes
1
answer
2k
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
...
- 11
2
votes
2
answers
223
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
, ...
- 125
0
votes
1
answer
56
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 ...
-1
votes
1
answer
754
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 ...
- 199
1
vote
1
answer
448
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 ...
- 199
1
vote
1
answer
693
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;
- 11
0
votes
1
answer
124
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 = ...
- 37
0
votes
2
answers
2k
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 ...
- 117
-2
votes
1
answer
722
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 ...
- 117
0
votes
1
answer
554
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 ...
- 117
0
votes
1
answer
2k
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 ...
- 117
0
votes
0
answers
61
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, ...
- 3
0
votes
0
answers
35
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, ...
- 1
3
votes
2
answers
718
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 ...
- 31
-2
votes
1
answer
45
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(...
1
vote
0
answers
273
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. ...
- 11
-1
votes
2
answers
163
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.
+------------+---------------+------------+----------+
| ...
- 1
0
votes
1
answer
3k
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 ...
0
votes
1
answer
163
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,
...
- 167
1
vote
0
answers
423
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'...
- 11
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 ...
- 115
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 ...
- 115
1
vote
1
answer
8k
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 ...
- 11
0
votes
1
answer
931
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 ...
0
votes
1
answer
253
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 ...
- 1
-1
votes
1
answer
199
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.
0
votes
1
answer
1k
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
...
1
vote
1
answer
466
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?
0
votes
1
answer
3k
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?
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 ...
- 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 ...
- 113
1
vote
1
answer
4k
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)
...
- 21
0
votes
1
answer
469
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.
...
- 1
1
vote
0
answers
629
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 ...
- 11
3
votes
1
answer
8k
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 ...
- 157
1
vote
2
answers
5k
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 ...
- 13
1
vote
2
answers
71
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 ...
- 21
1
vote
0
answers
406
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 ...
- 11
3
votes
1
answer
603
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?
...
- 163
1
vote
0
answers
142
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 ...
- 163
0
votes
1
answer
82
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)
...
- 1
1
vote
2
answers
63
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 ...
- 163
1
vote
1
answer
1k
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 ...
- 11
2
votes
1
answer
491
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 ...
- 349
1
vote
1
answer
2k
views
Can't get update one prompt for same variable over multiple tables
I've tried using the following:
select * from TABLE1 where COLUMN= :VAR
union
select * from TABLE2 where COLUMN= :VAR;
I'm getting the following error message back:
[Teradata Database] [TeraJDBC 14....
- 11
0
votes
1
answer
477
views
Teradata Join Syntax
I'm having a hard time doing what I want to do here, as SQL (and Teradata SQL on top of that) is not my main forte.
I have two queries that I want to join together. I want everything that returns ...
- 161