5,622 questions
0
votes
2
answers
131
views
Grouping of records in case values are null
We have got a table with a identifier, a key/value pairs and a start and end timestamp which indicates the valid period for the values.
MASTER_WORK_ORDR_ID
START_TS
END_TS
WORK_ORDR_ID_CTXT
...
- 15
0
votes
0
answers
65
views
Python teradatasql - Timeout per sql request issues
Presently trying to create a teradatasql python means of creating a timeout per each sql request made to a connection of 30 seconds but when trying the "request_timeout":"30", to ...
- 35
0
votes
0
answers
80
views
How does using RTLD_NODELETE affect Teradata UDF shared library unloading behavior?
I'm working on a UDF solution for Teradata Database using C-based UDFs compiled into a .so shared object library. The UDFs run in non-secure mode, inside the main Teradata processes.
Observed behavior:...
- 51
0
votes
0
answers
72
views
PySpark to Teradata insert fails with java.sql.BatchUpdateException when using batchsize
I am trying to insert data from HBase into Teradata using PySpark.
The data is read into a Spark DataFrame and inserts successfully when I limit the DataFrame to 3000–5000 rows like this:
df = df....
- 155
2
votes
3
answers
136
views
Changing data structure from "Date" to "From_Date" and "To_Date" based on ID and Flag
I have IDs, their as at date (end of month format), and their corresponding flags. I only care if the flag is Y.
It is in the following format:
ID
as_at_date
Enabled_Flag
1
31/01/2025
Y
1
28/02/2025
Y
...
- 23
1
vote
2
answers
71
views
Consolidate date ranges using Teradata
I need to take a list of date ranges and consolidate them. In our existing table, sometimes the FROM_DT and TO_DT are the same and the FROM_DT in the following row is 1 day after the previous row's ...
-1
votes
1
answer
98
views
Matching holidays between two dates (SAS TO SQL)
I have two tables one with request dates and SLA and the other with the list of dates of US holidays.
I want to write query that checks if any of the holidays fall between the request dates and the ...
- 873
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
3
answers
122
views
Teradata SQL Conditional Join
I'm guessing my question was asked before, but after hours of scouring, either I'm not able to figure out how the answers match my dilemma or it truly hasn't been asked (I'm guessing the former). So, ...
- 111
0
votes
0
answers
43
views
To remove multiple rows using GROUP BY in Teradata sql assistant
Im using the below query and its result will be,
sel SOLD_ACCTBL_DOC_ID,prim_SOLD_ACCTBL_DOC_ID,
case when CPN_NUMBER=1 then cpn_number else '' end as CPN_NUMB_1,
case when CPN_NUMBER=...
0
votes
0
answers
67
views
Create oracle link to teradata
isql works normally but when selecting with database I got "NativeErr = 50404"
My odbc.ini is :
[myteradatadb]
Description = TERRADATA
Driver = /opt/teradata/client/20.00/odbc_64/lib/...
- 13
0
votes
0
answers
66
views
Invalid Period value constructor. The beginning bound must be less than the ending bound
I have the following stored procedure that raise an error I don't succeed in resolving.
I know the problem is on the line "PERIOD..."
AND PERIOD(CAST(max_dt_deb AS TIMESTAMP), CAST(...
- 141
0
votes
0
answers
143
views
How to avoid "no more spool space" when joining two very big tables (thousand of billions)?
I have two tables I am using on a request.
Table 1 has 1 billions rows / Table 2 has one trillion rows.
When I execute the following request I have the error "no more spool space"
create ...
- 141
0
votes
0
answers
46
views
Teradata trigger to update values based on lookup tables
I'm trying to create a trigger to update a couple of columns in a main table (UI_tab) with values from two lookup tables (lu1_tab and lu2_tab). Here are the table layouts:
UI_tab:
network_id int
...
- 185
0
votes
1
answer
110
views
Display column names in export file bteq command
I have SQL queries with bteq that sometimes return no data and it's normal.
I want that the export file, even when the query returns no data, contains the columns names.
How can I do that please?
...
0
votes
1
answer
77
views
Converting 24hour to 12 hour format in Teradata [closed]
Looking to convert time format from 24hour to 12hour in Teradata but heading nowhere.
E.g., I have 22:30:45 and I want 10:30:45PM
- 873
0
votes
1
answer
31
views
Get Date for second and fourth Friday
Given Current_Date(), how can I get the dates for the second and fourth Friday of the previous month in Teradata?
E.g., if current_date is '2025-03-25' then the dates for the second and fourth Friday ...
- 873
0
votes
0
answers
92
views
How to convert timestamp from various specified time zones to another various specified time zones (Teradata)?
Please advise how to convert timestamps from various specified time zones to another various (or to the same) specified time zones, considering daylight saving time.
E. g. for this table with these ...
0
votes
0
answers
47
views
Teradata Spool issue due to several union
I am wondering how to solve teradata query with several unions that causes spool issue.
let say.
single Query1 is executed and completes 11 seconds.
single Query2 is executed and completes 13 seconds.
...
- 163
0
votes
2
answers
73
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
0
answers
20
views
Grouping the analytical functions in Teradata
I have the following Teradata code that successfully performs a Z test on data in the z_base table
SELECT * FROM TD_ZTest (
ON z_base AS InputTable
USING
FirstSampleColumn ('FALLOW_RR')
...
- 1
1
vote
1
answer
91
views
Aggregate 3-month rolling dates with overlap [closed]
Suppose I have below dataset:
date Value
01-Jul-24 37
01-Aug-24 76
01-Sep-24 25
01-Oct-24 85
01-Nov-24 27
01-Dec-24 28
And I want to aggregate by 3 months rolling:...
- 13
0
votes
3
answers
128
views
My date column is causing my query to crash, what is the issue?
I am running the query below but I need to remove the WHERE clause. It works fine with it, even filtering on other Cust_Name, but when I remove it I get an error saying "Invalid date supplied&...
- 11
0
votes
2
answers
114
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
99
views
How can I create Year and Month columns in SQL that seamlessly translate into Power BI?
Using Teradata SQL, am trying to create columns showing only year (e.g. 2025) or month (e.g. 6) extracted from a datestamp. Currently stored as int
Upon importing to Power BI, it'll reconfigure that ...
- 11
0
votes
1
answer
161
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
0
answers
86
views
SQLAlchemy Teradata - Unable to use the df.to_sql functionality
I am attempting to use SQLAlchemy to load data from a pandas dataframe into a Teradata database table using the pandas.to_sql method.
import sqlalchemy as sa
username = "my_username"
...
- 439
1
vote
1
answer
67
views
SQL optimization improve efficiency | filter data on non primary key column
I have a SQL Teradata table with 4 columns. Cust_id & Act_num are indexed columns, and Date is not indexed.
I need to filter the data on the DATE column.
Select
Cust_id,
Act_num,
Date,...
- 75
0
votes
0
answers
34
views
Teradata table partition
I have partitioned a table thus:
partition by ( range_N ( Prcsd_Dt between date '2015-07-01' and date '2027-06-30' each interval '1' year
, no range )
, CASE_N ...
- 1
0
votes
2
answers
129
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
800
views
Only an ET or null statement is legal after a DDL Statement
I'm trying to run a statement in Teradata, using DBeaver 24.2.
When I run my statement in Teradata SQL Assistant, it runs perfectly, but when I run it using DBeaver, I'm getting the following error:
...
0
votes
2
answers
90
views
Finding the number of previous tickets in the last 30 days
I'm trying to add a column for "number of tickets in the last 30 days" to a query of all tickets in 2024. What I've got so far:
SELECT customer_id,
ticket_number,
ROW_NUMBER() ...
0
votes
0
answers
60
views
Teradata Query Performance
Below query is running for long time with high CPU and IO usage.
Mainly because of last AND condition.
SELECT
A.CLMN1
,COALESCE(G.oDY_CNT,1) AS DY_CNT
FROM
DBNAME....
- 11
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
1
vote
1
answer
70
views
Reading Polygon data from Teradata into Tableau
I understand Tableau can't read a clob field in Teradata where I have the Polygons. However I understand there is a work around to extract data that can be used by Mapping capabilities of Tableau. Can ...
- 39
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
0
votes
2
answers
86
views
For duplicate rows, creating a column that shows status based on date
I am hoping for some help with the scenario below in SQL
Current State:
For a particular combination of med_id, casemgr_id, casemgr_clnt_id, the status should be reflected accordingly:
Desired Output:...
- 63
1
vote
3
answers
85
views
Rewriting query
I have a scenario where I want to take only one row from a group based on the column values in TID column. For example my key columns in the table SRN,GIO and FID. Now there is another column TID, now ...
- 145
1
vote
1
answer
64
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
70
views
How can I separate RowNumber counting?
I'm trying to create a point in time hierarchy in my organization. The problem I'm running into are some folks have reported to the same manager more than once in their career. I tried doing a ...
- 247
0
votes
1
answer
91
views
TDML: Error occured while writing to garbage collector file
I was facing the error above in teradataml py package on Win11, when using Dataframe.from_query().
But I am sure the exception would be triggered in (many) other methods.
Why ?
Because of a generic ...
- 1,564
3
votes
1
answer
62
views
Finding customers who've had multiple tickets within a 3-day period over the last month
I have a table that includes columns for receive_date, customer_id, and ticket_number. I want to find all the tickets for customers who've had more than 1 ticket created within 3 days of each other.
...
0
votes
1
answer
57
views
Condensing history table for teradata SQL
I have a history table that tracks all sorts of changes to different fields. I only need to analyze the changes to [MTN_STATUS_IND]. Because it's a history table that tracks a lot of changes there ...
- 3
3
votes
3
answers
87
views
Check how many rows in a grouping have the MAX value SQL
My dataset looks like this:
TEAM_ID PLAYER_ID NUM_POINTS
21 39 20
21 50 10
21 67 10
22 74 0
22 73 0
I want ...
- 714
0
votes
2
answers
89
views
How to Populate Column Values Based on Conditions in SQL?
I’m trying to explain an interesting scenario I’m facing in SQL. I’m not sure if there’s a specific window function or other keywords to achieve this, but let me explain.
I have a table structured as ...
- 145
0
votes
1
answer
100
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
44
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
1
answer
85
views
Combining XML files Using SQL
I want to combined mulitple exisiting XML files into one XML output file.
I am a new developer and still learning.
I have a table with four columns - ROWID, XMLMESSAGE, BUS_DTE, DW_LOD.
I want to ...
- 1
0
votes
1
answer
73
views
How to get the table name from Teradata QryLog for INSERT, UPDATE and Delete statement
I have a task to extract the Target tablename involved in INSERT, UPDATE and DELETE operation in Teradata
for an example
if the SQL in dbc.Qrylog is below - I need TD.Employee alone;
INSERT into TD....
- 1
1
vote
2
answers
184
views
Extracting value from comma-delimited string in Teradata
I need the value extracted from between the 5th and 6th comma of a string. STRTOK() is skipping the positions that are empty and not counting them. For example, this returns null because there are not ...
