FAQ Database Discussion Community


What is ROWS UNBOUNDED PRECEDING used for in Teradata?

sql,teradata,window-functions
I am just starting on Teradata and I have come across an Ordered Analytical Function called "Rows unbounded preceding" in Teradata. I tried several sites to learn about the function but all of them uses a complicated example explaining the same. Could you please provide me with a naive example...

Teradata variable with list of values

sql,teradata
Is there a way to pass a list of values in a variable and use that in an IN() statement to check a field against the passed in list of values? The only thing I can think of is something like this: SELECT field_name WHERE (SELECT INSTR(@variable_list, field_name))>0 Thanks....

can I connect nodejs with teradata (any nodejs npm for this)?

node.js,jdbc,npm,teradata
Is there anyway I can connect teradata to nodejs server? I have use case where I need to pull teradata information in my rest APIs. Can I use this npm package?...

How to keep the first row of a certain group based on some condition on Teradata SQL?

sql,teradata
I have table in Teradata that looks like this ID | Date | Values ------------------------ abc | 1Jan2015 | 1 abc | 1Dec2015 | 0 def | 2Feb2015 | 0 def | 2Jul2015 | 0 I want to write a piece of SQL that keeps only the earliest date of...

Columntype of a column in a view

teradata,rdbms
Is there any way to find the column type of the columns in a view. For example for tables we can fire select Columntype from dbc.tables where tablename=:name and databasename=:name; But for views the above query always returns null value. ...

SQL Combing the top 2 field values into 1 value

sql,teradata
I have a very simple query that returns the Notes field. Since there can be multiple notes, I only want the top 2. No problem. However, I'm going to be using the sql within another query. I really don't want 2 lines in my results. I would like to combine...

Teradata error : select * must have from clause

sql,teradata
How can I convert this sql server query to Teradata query. This is not working in Teradata. select *, (select top 1 endvalue from #AUID where #AUID.adate <= t.adate and #AUID.tid=t.tid order by #AUID.adate desc) as AsD from History_1 t ...

Teradata - Cannot nest aggregate operations

teradata
The PROD_AMT I'd like to get is when ACCT_NBR, PROD_NBR And PROD_AMT are the same, I only need one PROD_AMT which is 100 (from distinct), and when ACCT_NBR are the same but PROD_NBR are different, then the PROD_AMT I need is 90 (30+60) SELECT ACCT_NBR ,COUNT(DISTINCT CASE WHEN PROD_NBR =...

How to dynamically use Arcmain or just partionly copy tables in Teradata?

sql,database,teradata,database-restore
I need to copy tables from one teradata server to another one pretty much tables. In order to solve this problem, I have been advised to use arcmain. So table can be transfered this way: logon ZZZZ/YYYY,XXXX; COPY DATA TABLES (DATABASENAME.TABLENAME11) (FROM(DATABASENAME.TABLENAME1)), (DATABASENAME.TABLENAME12) (FROM(DATABASENAME.TABLENAME2)), (DATABASENAME.TABLENAME13) (FROM(DATABASENAME.TABLENAME3)), RELEASE LOCK, FILE=NVDSID1; However...

How to get number of rows affected by last several sql statement inside procedure in teradata?

sql,stored-procedures,rows,teradata
So let I have 3 merge statement inside the procedure: CREATE PROCEDURE SP_Employee(OUT num_rows BIGINT) BEGIN MERGE INTO t1 USING t2 ...; MERGE INTO a1 USING b2 ...; MERGE INTO c1 USING d2 ...; END; How can I get number of rows affected inside procedure? I know about ACTIVE_COUNT, but...

Avoid repetition of formulas in SELECT

sql,teradata
Let's say I have the following: SELECT users_aggregate_data.treatment, SUM(users_aggregate_data.GS_Oct_Dec), AVG(users_aggregate_data.items_sold_Oct_Dec), -- .. and a few other such as std_deviation, .. SUM(users_aggregate_data.GS_Jan_Mar), AVG(users_aggregate_data.items_sold_Jan_Mar) -- .. and a few other such as std_deviation, .. FROM users_aggregate_data In users_aggregate_date I have painfully aggregated sales data by customer and by time span. I was...

Group By & Having vs. SubQuery (Where Count is Greater Than 1)

sql,teradata
I'm struggling here trying to write a script that finds where an order was returned multiple times by the same associate (count greater than 1). I'm guessing my syntax with the subquery is incorrect. When I run the script, I get a message back that the "SELECT failed.. [3669] More...

TERADATA - How to split a character column and keep the last token?

sql,substring,token,teradata,strtok
I have a table with article names and I would like to select the last word of each article of the table. Right now I'm doing it in SAS and I my code looks like: PROC SQL; CREATE TABLE last_word as SELECT scan(names,-1) as last_w FROM articles; QUIT; I am...

Teradata - Numeric overflow occurred during computation

sql,casting,teradata
I am having an issue with a calculation in one of my Teradata queries. I am multiplying two numbers by each other but i am getting a "Numeric overflow occurred during computation." error when running the query. I ran a type on both fields and they are DECIMAL(18,15) and DECIMAL(18,9)....

Is there a way to reuse subqueries in the same query?

teradata
See Update at end of question for solution thanks to marked answer! I'd like to treat a subquery as if it were an actual table that can be reused in the same query. Here's the setup SQL: create table mydb.mytable ( id integer not null, fieldvalue varchar(100), ts timestamp(6) not...

Teradata SQL Assistant: Alternatives {on Windows}

teradata
Are there alternatives to Teradata SQL Assistant {on windows}, possibly freeware? Heidi is great but it does not support Teradata. What are the pros and cons of the alternatives?

sql update in teradata

sql,teradata
table_1 ( c1 int ,c2 int ,c3 int ); insert into table_1 values (1,2,3); update table_1 set c2 = c2 + 5 --c2's value suppose to be 7 now ,c3 = c2 + c1; --c3's value suppose to be 7 + 1 = 8 now select c1, c2, c3 from...

Calculate previous week data in Teradata

teradata,weekday
How to calculate prior 7 days data (Sunday-Saturday of the previous week). on whatever day of week it is run it should always give Sunday-Saturday of the previous week. thanks

Optimizing an update statement - Teradata

optimization,sql-update,teradata,spool
I'm trying to run the below update, but continue to run into spool space issues. Is there any way I can optimize? UPDATE dp_wedw_snd.platinumsaves SET package_name = (SELECT package_name FROM dp_wedw_snd.selfpaysubs_agid_platsaves WHERE trim(dp_wedw_snd.platinumsaves.esn1) = trim(cast(dp_wedw_snd.selfpaysubs_agid_platsaves.esn as varchar(255))) and abs(dp_wedw_snd.platinumsaves.shortdate - dp_wedw_snd.selfpaysubs_agid_platsaves.dateasof) = (select min(abs(dp_wedw_snd.platinumsaves.shortdate -...

Find out the currently running queries in teradata using SQL instead of viewpoint

teradata
I want to find out what are the queries that are currently under execution specific to a user or a group . I don't want to use view point . I need to achieve this functionality using a SQL.

Teradata: use of aliases impacts EXPLAIN estimation of time

sql,teradata,explain-plan,aliases
I have a relative simple query SELECT , db1.something , COALESCE(db2.something_else, 'NA') AS something2 FROM dwh.db_1 AS db1 LEFT JOIN dwh.db_2 AS db2 ON db1.some_id = db2 = some_id EXPLAIN gives an estimated time of something more than 15 seconds. On the other hand, explain on the following, where we...

Display only rows that do not match the control table

sql,teradata
I'm currently having a problem since I can't really explain it in words it's better if I put a test table here. Table1: Source_DB Source_TBL Source_Col Target_DB Target_TBL Target_Col Metric Source_VAL Target_Val ____________________________________________________________________________________________________________ Source_D Source_T Col1 Target_D Target_T Col1 Index 1 1 Source_D Source_T Col1 Target_D Target_T Col1 Length 10...

SQL Select * and cast one of those variables

sql,teradata
I am working in TeraData and I want to select all the columns in a table except I need to cast one of them. Is there an easy way to do this without writing all of the column names out every time? This describes what I want to do: SELECT...

How to format Date as this format 'DD.MM.Y4'?

teradata,datetime-format
I have been trying several ways to represent date as this way 10.12.2014. But I didn't find proper way to solve this problem, Teradata's FAQ gives some ways how to use FORMAT, but none how to solve my problem. So let we have SELECT DATE'2014-12-04' How can we make it...

Teradata 13: CASE statement in Join

teradata
Is CASE statement allowed in join? Is there a good way to accomplish the task here? My real query has some other left join. I would like to join T1 and T2 in this condition: 1. when T1.sub_service is not null, then join with T2.type 2. when T1.sub_service is null,...

More than one value returned by subquery

sql,teradata
I have a sub query that returns more than one value.Please see the query below. There is a possibility that more than one order exist for a given customer and date. In that case I only want to return one order (It might sound strange but that is what I...

What is the difference between executing a macro and executing the contained instructions within it in Teradata?

macros,teradata
Sometimes it can well be observed that individual instructions within a macro execute way faster than a whole macro in Teradata ? Is this just a delusion or is there any logic behind it ? I am newbie to Teradata and I will appreciate if someone explains the reasons from...

How to convert a timestamp from one timezone to other

sql,timezone,teradata,dst
I have a table with TIMESTAMP column in Teradata. I want to consider the values stored in this column as if they are from 'America Pacific' timezone and convert it into GMT timestamp. I tried, select timestamp_col, timestamp_col at 'GMT' timestamp_col_gmt, timestamp_col at 'America Pacific' timestamp_col_pac, from table_name; timestamp_col timestamp_col_gmt...

Connecting R To Teradata VOLATILE TABLE

r,teradata,volatile,rodbc
I am using R to try and connect to a teradata database and am running into difficulties The steps in the process are below 1) Create Connection 2) Create a VOLATILE TABLE 3) Load information from a data frame into the Volatile table Here is where it fails, giving me...

Executing TeraData codes using SAS function

function,sas,teradata
I have a question about executing the year() function in TeraData from SAS. For example, when executing the code below, I will get the error message below the codes. Is there a way to execute the year() function besides using the year function in SAS after the table is created...

Child user can't select view of parent in teradata

sql,select,parent-child,teradata
In Teradata SQLAssistant, I have created a db user USER1 which has 17 views. This user can do a SELECT on any view, thanks to a profile/role I granted and then associated to him. His child USER2 is a db user with the same profile/role as USER1, but my problem...

Teradata create global temporary table

sql,teradata
In Teradata I want to create a global temporary table and then reference this global table in other queries. I don't want to create a volatile table as I am using Teradata through another front-end and that front-end has to be able to tell whether the temporary table exists, hence...

How to read every row in SQL

sql,teradata
How do I read each line to determine if the customer has fully paid or partially paid or never paid? I would like the code to read every line of each customer's bill until I find the TOTAL_PAYMENT_AMT = TOTAL_DUE. If found, then mark it as paid, if not found,...

Count and return where 2 fields match

sql,teradata
I'm trying to write a script that counts results based on 2 fields matching - but not matching like identically, but where the values re-occur throughout the table. For example, I want to find where Field A and Field B = x & y, respectively (and count those results) however,...

How to use a subquery inside an INTERVAL function in Teradata?

teradata
I want to use Interval function in Teradata much like this : SELECT DATE '1998-08-31' + INTERVAL '30' DAY; But I want to make the date range configurable. That is I want it to be read from a table instead of using a hard coded value like '30' in the...

Code equivalent of “%[^ ]%” to Teradata

like,teradata
if @gl_cst_ctr_cde not like "%[^ ]%" Hi guys, Above is a Sybase code and would like to know the Teradata equivalent of not like "%[^ ]%" Many Thanks!...

CREATE VIEW returns error “date does not match a defined type name”, but the actual query runs normally

sql,teradata
Using Teradata SQL, I'm running a query against an 'Order History' table I loaded, which involves finding the most recent order where a particular change occured. I ran into some trouble as the Order_Date was loaded as VARCHAR with varying lengths, however I've gotten around this by using CASE WHEN...

How get information from multiple tables using cursor?

sql,cursor,teradata
I have a query, that returns multiple tables, something like that: SELECT TableName, DatabaseName +'.'+ TableName, ColumnName FROM DBC.Columns WHERE ColumnName = 'id' And I need to loop through these tables by looking to the information stored in these tables, in order to get only specific tables. I tried something...

How to find all the tables in database Teradata with specific column names in them?

sql,database,teradata
I have 2-3 different column names that I want to look up in the entire DB and list out all tables which have those columns. Any easy query? I have seen solution for MySQL, which won't work here because TD as far as I know don't have schemes, but instead...

TPT load operator to load relational tables to Teradata target

teradata
Can we use TPT load operator to load Oracle relational tables to Teradata target? Since FLOAD only loads from flat files, can we use Tables here in TPT LOAD operators? What can be the workaround this? We have the oracle tables which needs to be loaded to teradata target using...

Setting Minute and Second component of Timestamp field to 0

sql,database,timestamp,teradata,data-type-conversion
I have a requirement such that if I enter a timestamp(yyyy-mm-dd hh:mm:ss) say 2015-04-05 16:45:12 I need it converted to 2015-04-05 16:00:00 Is there an elegant way to do this in Teradata rather than extracting hour and then casting it with date?...

fast export unexplained failure

teradata,informatica,fastexport
I have roughly 14 million records that I am attempting to export from a Teradata table to file using a fast export connection object. There is no size limit for fast export files on our Linux system, and there is 1.2 TB of available space in the target directory. The...

TERADATA: query optimization

sql,teradata,query-performance
This query is working but it seems to take longer time than usual to retrieve the data. Is there a better solution to optimize this query? I need to get all PRD_ID from T1 and T2 even if there is no match with S1 and S2. SELECT DISTINCT T.PRD_ID T.AMOUNT,...

SQL Teradata - in query create new column that multiplies column by 2 if certain value is true

sql,teradata
I have a sql query I'm running that exports 2 columns, cost and months. The months column either has a value of 6 or 2. I want to create a new column that checks the months column and sees what the value is. If the month value is 6 then...

How to measure user priority over a request in teradata?

sql,request,user,teradata,priority
In Teradata, I have set two differents priorities (low and high) for two different users Is there something that can proove that a request sent simultaneously by these two users will be treated differently according to the user priority ? Thanks in advance....

How to find two rows where a field value is x and 2nd row is x + 1

sql,teradata
I have a list of records (transactions). I want to be able to ONLY include rows where the transaction numbers have a sequential value +1. Not even sure where to start to get it to list ONLY those transactions. I have it working to list all transactions sequentially, but not...

Incremental loads in Sqoop

hadoop,hive,teradata,sqoop
I have a table in Teradata which is loaded with new data on daily basis. I need to import this data to Hive. I'm trying to use Sqoop but how should I do incremental load using Sqoop? I checked incremental load options available in Sqoop --check-col This options expects only...

A SAS value cannot be converted to a Teradata date

sas,teradata,data-integration
I've got a temporary work table with a date variable source_datetime in SAS DIS. This variable is in the DATETIME22.6 format. I have a teradata table with a date field target_date (type DATE), and using a table loader I am attempting to map source_datetime to target_date. When I run the...

Selecting A Date Between Another Field's Date and Other Date

sql,teradata
So, here's what I'm trying to accomplish - finding a date between an existing date and 30 days after. My subquery code: select sldate from sldaterefret group by sldate having sldate between orig_sldate + 30 Hope that makes sense. As always, thank you in advance....

How to calculate the Difference between SUM values

sql,teradata,difference
I am pulling total account balances for various dates and need to calculate the difference between those total account balances but need solution. SQL: SELECT SNAPSHOT_DATE, SUM(ACCOUNT_BALANCE_AMOUNT) SAVINGS_BALANCES FROM pIDWUserVW.PRODUCT_SAVINGS_VW WHERE SNAPSHOT_DATE IN ('2015-01-02', '2015-01-30', '2015-02-02', '2015-02-27', '2015-03-31') GROUP BY SNAPSHOT_DATE ORDER BY SNAPSHOT_DATE ASC; Results: SNAPSHOT_DATE SAVINGS_BALANCES 1/2/2015 10,407,069,092.55...

Teradata CASE and HAVING COUNT

sql,teradata
I have a table Handset. There are some duplicate imei's and i have to select only one imei each with these requirement: when unique imei found then pick that one when duplicate imei found, if one data_capable = 'Y', pick that one. when duplicate imei found, if both data_capable =...

regex in teradata, how to pass two values in

sql,regex,teradata
I have the following regex which works, but I need to find this expression if shop_id also regexp_substr(field_text ,'(?<=\?page_id\=)(.*?)(?=\&)' ,1 ,1, 'i') I tried this: regexp_substr(fullurl ,'(?<=(\?page_id\=|/page_id/))(.*?)(?=\&)' ,1 ,1, 'i') But it states this is not a valid pattern, can someone please explain why not, as it works in regex...

Syntax error: parse error, when creating a regression test checking empty tables

sql,teradata
I am creating a regression test query that shall check if tables are populated or not. This is done by getting the first row in each table A,B,C and if its id exist it shall get value "Y". If not then value "N". I created the code below to do...

How can I check if a character is allowed to be uploaded in Teradata?

character,teradata
How can I check if a character is allowed to be uploaded in Teradata ? Recently I was uploading (using jdbc) a .csv file that contained some weird SUB characters. The upload failed. Later i found out that those weird characters were the older version of the end of file...

How to format a date as part of a string in Teradata?

sql,date,teradata
I'm trying to derive a filename in Teradata. The format should be like this: X_<YYYYMMDDHHMI>_Y.dat This is how I'm trying to achieve it: 'X_' || CAST(CURRENT_TIMESTAMP(FORMAT 'YYYYMMDDHHMI') (CHAR (12)) AS VARCHAR(50)) || '_Y.dat' But I keep getting a bunch of syntax errors. Any idea on how to achieve this?...

Trying to get connecting Dabase Name using java

java,teradata
conn = jdbcTemplate.getDataSource().getConnection(); DatabaseMetaData databaseMetaData = conn.getMetaData(); oracleSchema = databaseMetaData.getUserName(); // Oracle gives me database name i am connecting to.... But is there is any specific method which gives me teradata database name?...

Average Hour of Day in Teradata

sql,datetime,timestamp,teradata
I have a table with hundreds of timestamps. I extract the hour, then I can simply average that, however, this is where I get issues. select purchase, CAST(AVG(EXTRACT(HOUR FROM opened_at)) AS INT) as average_open from db.purchasetable group by 1 When I average hour 22 and hour 2 I get 12(obviously),...

Teradata Convert DATE to midnight

date,time,teradata
below is the original code convert(datetime, (substring(convert(varchar(26), convert(datetime,getdate())),1,12) + '23:59:59:996')) output is = 6/17/2015 11:59:59.996 I converted it to Teradata below CAST(CURRENT_TIMESTAMP AS DATE FORMAT 'MM/DD/YYYY') || ' 23:59:59:999990' but upon compiling my store proc, it produces an error below Invalid or missing expression 'E(5404):Datetime field overflow.'. Is there a...

How to create an SQL query that checks for duplicates in multiple tables

sql,teradata
I want to create an SQL query where I check if duplicates exist in several different tables. I do this in Teradata. I want the output to look like the following. |Table A| |Table B| |Table C| |Table D| |Table D| For each of these columns we can get the...

SSIS Teradata ole db connection

ssis,teradata
I'm working on modifying an SSIS package that includes several lookup transformations. In my experience you must have an OLEDB connection created to use a lookup. I am being asked to change the lookup to connect to a Teradata connection. We have installed the Attunity drivers and Teradata is an...

Difference in these 2 queries in TERADATA

teradata
SEL * FROM TABLE WHERE a=10 VS SEL * FROM TABLE WHERE a='10' Here a is BIGINT, Explain plan does not show any difference, how teradata handles this and any difference in these query ? ...

SQL query to identify 0 AFTER a 1

sql,teradata
Let's say I have two columns: Date and Indicator Usually the indicator goes from 0 to 1 (when the data is sorted by date) and I want to be able to identify if it goes from 1 to 0 instead. Is there an easy way to do this with SQL?...

How to show records were 3 fields match

sql,teradata
I'm trying to write a query that will list the columns in a table when 3 specific fields are the same, but unknown: TABLE: FIELD 1 | FIELD 2 | FIELD 3 | FIELD 4 ---------|--------------|------------|--------------- 1 | 01-01-15 | 21 | 150 1 | 01-01-15 | 24 | 12...

Teradata 3130 Error in JPA/Hiberate context

java,spring,hibernate,jpa,teradata
In batch script I use a loop to execute a bunch of sql (hql) against a Teradata databse. After some iterations I receive the following error: Teradata databse: 3130 Response limit exceeded Now the documentation suggests (as well the answer on this question) that this is due to to many...