546 questions
Advice
0
votes
3
replies
70
views
Procedure to show percentage of nulls in TERADATA SQL ASSISTANT
I'm working in Teradata SQL Assistant and I need to create a stored procedure that, given a table name, returns the percentage of NULL values for each column in that table.
Right now, I generate the ...
- 67
0
votes
0
answers
45
views
Teradata PI+Non PI in join
Will primary index be utilised in Teradata if join condition on NON primary index along with primary index columns?
Suppose,
-- Table A
CREATE TABLE Orders (
OrderID INT,
CustomerID INT,
...
- 11
0
votes
2
answers
72
views
Group count on derived table in Teradata
I'm trying to create a frequency count table on a derived healthcare table of member age in months when receiving a first medical diagnosis (datediff(month,d.birthdate,d.min_claimdate)).
My cleaned up ...
- 906
0
votes
2
answers
109
views
Passing stored procedure arguments to FOR loop cursor
I was wondering if it was possible to pass in string arguments from a stored procedure into a FOR loop cursor SELECT statement definition? i.e something like
CREATE PROCEDURE db_name.procedure_name (
...
- 279
0
votes
1
answer
157
views
Pass table columns as arguments into Teradata SQL stored procedure
For context, I am very new to Teradata SQL and am currently trying to understand how to build out user defined functions (UDF) and procedures (UDP), in SQL form only, though am having some ...
- 279
0
votes
2
answers
128
views
Lag or first_value doesn't work when year is changed
with
--my input--
x(id1,med_id,dt,dt_tm,casemgr_id,casemgr_clntid,status) as (
select 123456,98410,date'2024-04-19',timestamp'2024-04-19 09:00:00',12345,67891,-2
union all
select ...
- 63
0
votes
0
answers
45
views
I want to figure out how to count the number of months consecutively in a query
I am trying to extract data in relation to customer savings on Teradata. So I want to figure out how many months in a row a customer has saved money in 2024 and to display that as a number. I.e if a ...
1
vote
1
answer
50
views
Teradata SQL to see if customer has unique National Insurance Number
I have a list of customers and each customer should have a distinct national insurance number assigned. Some customers will have the same NIN. How do I write the query to check whether each customer ...
- 25
0
votes
0
answers
109
views
Distinct product grouping based on a set of columns with duplicate rows
THIS IS THE CURRENT STATE DATA:
THIS IS THE DESIRED FUTURE STATE:
Basically, the requirement is that, when the txn_type="dept" and txn_grp="store", from that txndate, within 183 ...
- 63
1
vote
1
answer
60
views
Teradata SQL workday function?
In Excel I can do WORKDAY Function like this =WORKDAY("2024/11/15";15)
How if applied to SQL TERADATA? or is there a way to display the date 15 days in the future by excluding Weekend ?
I ...
- 11
0
votes
1
answer
99
views
Repeat Date in Teradata Studio vs. SQL Data Assistant
I am new at my job and we use Teradata. I am using Teradata Studio, and one of our longer Procedures starts with this statement:
SELECT ...
0
votes
0
answers
39
views
TD_Smote function in Teradata
I am attempting to run the Smote function in Teradata uisng the code below which is taken directly from the terdata guidance document.
SELECT * FROM TD_SMOTE (
ON iris_enc_sample AS InputTable ...
0
votes
0
answers
39
views
Teradata SQL - table has a list of activation and deactivation activity dates on individual lines, can they be combined to show full activity time?
The table is laid out like this
LastName
FirstName
AccountNumber
ServiceCode
ActivityCode
ActivityDate
The activity code is either A for activate and D for deactivate
Activity date is a date field of ...
- 1
0
votes
0
answers
99
views
WHERE clause appearing AFTER a GROUP BY clause in Teradata
I've run across a query in Teradata that has me a bit confused. Consider the following query:
SELECT
ID
, MIN(In_Time) as In_Time
, MAX(Out_Time) as Out_Time
FROM table
GROUP BY 1
WHERE In_Time IS ...
0
votes
1
answer
87
views
Teradata set role for a user is not working by c#
I am trying to build a console app that retrieves users from LDAP, creates the same users in Teradata, and grants privileges to these users based on their group membership.
My app is functioning well, ...
- 124
1
vote
2
answers
78
views
SQL query to create running balance on hand?
In Teradata, I have a query that returns date, initial, sales, and deliveries.
SELECT
date, initial, sales, deliveries
FROM sample
I would like to create a new column that acts as a running balance ...
- 199
0
votes
1
answer
84
views
Join two tables in SQL with date condition
In the first table, I have client number, start date and other columns:
Client number
start date
other1
other2
xyz
2024-04-01
values10
values20
abc
2024-01-02
values11
values21
ert
2024-01-03
values12
...
0
votes
0
answers
129
views
Syntax for like operator in teradata sql assistant
What is the correct syntax for like operator that can be used for multiple values in the filter condition while writing a query in teradata SQL assistant?
For example a column named uniq_id is there ...
0
votes
2
answers
142
views
Teradata query to Calculate Start and End dates Based on a condition
I have a dataset for certain STS codes and I need to retain the Start and End Date When Status =03
I have the following Data
HAVE
ID CHGDATE STS
101 11/21/2022 ...
- 593
0
votes
0
answers
388
views
How to set default role for Teradata User?
I have create a role test_role. Also I create a view for a table which allow only see 3 columns out of 5. Then I assign this view to this role.
CREATE ROLE test_role;
Create a View
CREATE VIEW ...
- 124
-1
votes
1
answer
60
views
issue with case statement
Input data:
NUMBER
FLAG_1
FLAG_2
FLAG_3
FLAG_4
FLAG_5
FLAG_6
123456
X
X
X
X
X
X
Case stmt:
SELECT DISTINCT
NUMBER,
CASE
WHEN FLAG_1='X' AND FLAG_2='X' AND FLAG_3='X' THEN 'PRE'
WHEN FLAG_4='...
- 5
0
votes
1
answer
275
views
Generating list of numbers in Teradata Sql Asst
I want to generate a list like -3, -4, -5, -6, -7 in Teradata Sql asst. Is there any direct way to do it. Sample attached here.enter image description here
- 1
0
votes
1
answer
70
views
Teradata SQL Asst query - Error in where clause
I have included one 'where' clause which I was hoping to return output with reference to current_date but I am getting an error. Can someone please take a look at it and let me know what exactly I am ...
- 1
0
votes
1
answer
881
views
Teradata DBC user not able to login. How to fix it?
I was trying to enable LDAP and auto-provisioned Directory users. I successfully managed to configure LDAP with Teradata and manually mapped one user from LDAP to Teradata. This user has successfully ...
- 124
0
votes
1
answer
61
views
SQL Query Date time Suggestion
I have a SQL which gets data until yesterday by using where clause as
"
Where tpd."RELATIVE_GLOBAL_DAY" = -1
"
The db is defined as such that when I want to get todays data, I will ...
- 1
0
votes
1
answer
80
views
CASE WHEN or WHERE with new column creation, naming each category, and SUM AS or OVER the category (Teradata SQL)
I have the following 5 CASE and SUM statements that work. I can create a category first, and then I can sum of this category.
However, given that I have several categories, my task is to wrap these in ...
- 61
0
votes
0
answers
38
views
Teradata: expected something like ';' between the word 'multicasematch' and '('
I am trying out a MultiCaseMatch statement in Teradata where I get the error message "expected something like ';' between the word 'multicasematch' and '(' ". Would anyone be able to help me ...
- 11
0
votes
0
answers
262
views
Can I see where my Teradata stored procedure is failing?
I have a stored procedure in Teradata that used to work but some changes recently have rendered the output of the procedure non-existent. Partial code is pasted below in-case anyone can find the error ...
-1
votes
1
answer
55
views
where clause using multiple conditions
Code1 | Code2 | Code3
----------------------
101| 102 | null
101| null | null
102| 104 | null
The above is the table called DiagCodes
Select *
From
Where
Where condition should check all the ...
- 7
0
votes
0
answers
554
views
How to fix Teradata Parallel Transporter error: '2673 The source parcel length does not match data that was defined'
I am using Teradata Parallel Transport to select and insert data from tables in a SQL Server to identical tables in a Teradata database. When reviewing the Error Table after failure, I see error 2673 ...
0
votes
0
answers
62
views
Want to run Update in Teradata but with Inner joins
I want to update a column named 'FLAG' and set its value as C when the records from two different tables have a match on a few conditions. Please see the below sample of what I'm trying to achieve in ...
-2
votes
2
answers
229
views
Having count(distinct x) not returning correct results - Teradata SQL
I'm working in Teradata, and my data looks like this:
Image of data
my query is:
select id, type
from table1
where id in (100, 101)
group by 1,2
having count(distinct type) > 1
but its returning ...
- 23
0
votes
1
answer
277
views
How to fix TPT script error? 'MSG='[Microsoft][SQL Server Native Client 11.0]String data, right truncation' '
I am using Teradata Parallel Transport to select and insert data from tables in a SQL Server to identical tables in a Teradata database. I have done this before successfully but this is the first time ...
0
votes
1
answer
158
views
How to take the preceding Value based on condition in teradata sql
I meet some issues when trying to get the preceding GRADE based on The BEGINING_DATE (when it change )
Here is my data :
ID
GRADE
BEGINNING_DATE
ENDING_DATE
1
12
14/06/2021
14/06/2022
1
12
14/06/2021
...
- 35
0
votes
0
answers
41
views
teradata query to analyse a particular DOB and convert it to max date of that particular month
I want to build a teradata query that will analyse the DOB field of a table and use case statement to update accordingly. It will keep the month and year fields as it is and change the date as per ...
- 29
0
votes
0
answers
106
views
Teradata - Insert statement works for me but not colleague - Invalid timestamp
I have a SQL script I use to import data using Teradata SQL Assistant. It works for me but not for my colleague: they get "Error importing batch records 1 - 20: [6760] Invalid timestamp."
I ...
- 127
0
votes
0
answers
504
views
Facing issue converting string - UNICODE to LATIN
I am trying to insert data from table A to another table B having 900+ columns, but this is failing with this error:
Failure 6706 The string contains an untranslatable character.
I found out ...
- 21
0
votes
1
answer
29
views
Get values followed by duplicates values in sql
I have a value in a column in sql
1
2
3
1
4
5
My expected output is
123
145
I need a sql statement to achieve the above result
I didn't get the proper result by using rank and row number function
...
0
votes
1
answer
706
views
Nested Case When Statements
Is there a more efficient way to write this case when statement so as to not have so many "when c.clm_its.host.cd IN...". I was thinking it could be possible to add a nested case when within ...
- 37
0
votes
0
answers
52
views
How to return only multiline text comment on the Table_Comment attribute in the wsp_iq.Text_Comment table?
could you please help me with the issue below?
I work in Teradata assistant environment. I have table wsp_iq.Text_Comment with text comment attribute called Table_Comment that contains many text ...
- 11
-1
votes
1
answer
59
views
Get first date that account-prod get discount sql
I have a huge volume of a history of transactions table with acc_nbr, tran_nbr, tran_amt, tran_dt, and tran_dis tran_prd
I need to find the first date for each acc_nbr and tran_prd
Find the First ...
- 1
0
votes
1
answer
82
views
Teradata - Add dates as a new column into an existing table
In Teradata, I have a table full of calendar dates, and I have another table full of Customer IDs. I want to add all calendar dates into the Customer table, so that every Customer has a row for every ...
- 161
0
votes
1
answer
396
views
Zero Results in division teradata
I try to divide to decimal columns
But keeps returns zeros if after casting to decimal
For example
A B
677.00 900.00
A/B
Returns 0.000
I try to divide to decimal columns
But keeps returns ...
- 1
0
votes
0
answers
37
views
Teradata Labelling correct status involving multiple processes
I need help with determining correct STATUS of an asset, start & final refresh time correctly as one asset can be tagged to multiple processes having different status.
START TIME: It should give ...
- 21
1
vote
1
answer
69
views
Teradata Maximum process end time capture for multiple processes
I am trying to fetch data for one asset for reporting purposes which is dependent on multiple processes and start time should be the earliest job that starts among them and end time should be latest ...
- 21
0
votes
0
answers
130
views
Teradata SQL - Qualify max / row / rank
Below is the sample data I have
enter image description here
I am trying to get either of two outputs whichever is possible
enter image description here
or
enter image description here
I have tried ...
0
votes
1
answer
198
views
How can I change a datetime value to a string, but keep the other values at Date value?
I'm dealing with a dateTime such as '1800-01-01 00:00:00' and I want to convert it to a string = 'N/A', but keep everything else as a dateTime.
I tried converting it to a string (To_char(date_time)) ...
0
votes
0
answers
506
views
using python unable to insert pandas dataframe to teradata table
I have to push data from pandas dataframe to teradata table using python
tried different methods teradatasql , pyodbc, sqlalchemy , teradata.UdaExec but am getting unique errors for each method
Below ...
- 109
0
votes
2
answers
331
views
I would like to generate a list of dates with timestamp at an hourly interval using SQL in teradata
just want to know if there is a way that I can generate a list of dates for a certain date range (e.g.2023-01-01 to 2023-01-20) with timestamp at an hourly interval in Teradata using SQL
Results that ...
0
votes
0
answers
65
views
Teradata Update based on Fiscal Month End
We need to determine if employee is New Hire or not. (Flag 'Y' or 'N'). A fiscal month starts on 22nd of each month and end on 21st of next month. Can you please help with the logic?
HireDateStart = ...
- 21