0 - 50 of 208 tags for sql


Hi Everyone,
                    I am new to Teradata forum. Could anyone in forum please help out in deriving an analytical function.
Trying to create a new date column using windowing function which display date of child (SEQ_ORDER (5)) as is and move dates of its first parent (SEQ_ORDER (4)) to all the parent level i.e 3,2,1

I have one doubt.
Suppose my table contains 1000 records, I need to write a sql statement to group table in equal number of records.

I have a parameter in a report (Report Builder 3.0) called REPORT_FILTER.  This parameter has 4 available values.  The labels are (1) Home Group, (2) Home Branch, (3) Other Group, and (4) Other Branch.  The corresponding values are 1, 2, 3, and 4.

I'm struggling with a query to fetch entire database hierarchy and calculating path of each node from root. I need to create same directory structure on the file system whereby each directory represents a database.

For a Fortune 50 Banking Company. W2 or corp-to-corp. Local candidates preferred but those willing to relocate will be considered. Manager would like to interview as soon as possible.

Must have hands on experience with SQL development in Aster environment

Hi All,
I have data like these . 

Mar 22, 2016 12:06:35 PM

I am trying to use XMLAGG to transpose the row-wise data to comma separated value in Teradata 15. Its working fine with English (LATIN character set) characters. But while I am using other characters (Unicode character set) I am getting 'string contains untranslatable character' error.
Please suggest how to resolve the issue.

Hi All,
I have a dataset that looks like this:
ID        start_date    end_date       flag
12345  2014-06-04  2014-07-03    I
12345  2014-07-04  2014-08-14    O
12345  2014-07-04  2014-12-01    O
12345  2014-07-04   2015-03-01   O
12345 2015-03-02   2015-05-01    I
12345 2015-05-02    2899-12-31   I

Hi All,
It may seem weird, but i came across this. i have a stored procedure written which takes isource_db as input, Eg: UAT_GCFR_VIEW.XYZ

insert into ADWP_WORK1.Norm_Collateral_Fac
select pst_obligor_id,pst_fac_id,pst_sys_id,Collateral_Cd,Collateral_Cd_Desc,Maturity_date
where not
Collateral_Cd_Desc is null

Teradata allows to use fields from SELECT block in other blocks. 
A have two tables T1 and T2. And there is a field "a" in table T1 and in T2. And i made a new field in select block^
case when T1.a > T2.a then T1.a else T2.a end AS a

Presto is an open source distributed SQL engine, originally developed by Facebook for their massive Hadoop data warehouse.

Please forgive me if this is a basic question, but I have found myself responsible for creating SQL reports which is not my forte.  Any help will be greatly appreciated!

Which of the following partitioning expression has a better performance while SELECT, INSERT operations ? Consider the table has data around couple of Terabytes.
PARTITION BY RANGE_N(LOAD_TIMESTAMP  BETWEEN TIMESTAMP '2015-01-01 00:00:00.000000' AND TIMESTAMP '3499-12-31 23:23:59.999999' EACH INTERVAL '1' DAY );

Dear All, Need help with below situation....
id        date
100   6/6/2015
100   8/1/2015
100   9/30/2015
200   8/11/2015
300   7/18/2015
300   1/10/2015
300   3/5/2015
300   6/15/2015

I have a query with a UNION.  I was trying to combine 2 records into 1.  The top half gets records from 1 set of tables and the bottom half gets records from a differen set of tables.  Both halves use fields from 2 derived queries.  The results currently look like this:

My Undesrstanding was this 

  • WHERE CLAUSE on an INNER JOIN CONDITION Vs  INNER JOIN  WITH AND <condition> will be the same 

e.g. A Inner Join B on A.x=B.y WHERE B.z between Date1 and Date2 
e.g. A Inner Join B on A.x=B.y AND B.z between Date1 and Date2 

The following SQL statement using this syntax failed after query execution:

There is  "poetic query" that takes forever to run
Kind of looks like this
(case <condition involving tb1 and tb2>) as "Dcol1",
Sum ( col1.tb3),
sum (col2.tb3 ),
sum (col3.tb3)
tb1 left outer join tb2 <condition> LOJ tb3 <conditions>

We are pleased to announce general availability of the Teradata Developer Tools for Visual Studio 15.11.
This product may be integrated with Visual Studio 2010, 2012, 2013 or 2015 and can be downloaded from the following locations:

Below delete is consuming 11K CPU.Need assistance to re-write the below delete sql to consume less CPU.




Hi all,
I had written a DB query to be run on SQL Server and the table structure was like:

How to center align a decimal value(including negative numbers) with 20 characters as total fixed length.
For ex: -234567.89 should be displayed as :
'     -234567.89     '

Good day all,
I have data that consists of 3 months of charges for each line item.
Bill_No                Bill_Month                     Charge
00121       4/10/2015 12:00:00 AM           1300.00
00121       5/10/2015 12:00:00 AM           1300.00
00121      6/10/2015 12:00:00 AM            1400.00

I have a table with the following layout

cust_acct_id (PK)| effect_dt | expiry_dt | curr_rec_ind | field_a | field_b | field_c | field_d

I have to populate any NULL values in field_a, field_b, field_c, or field_d with the most recent value received for that field for the same cust_acct_id.

Sample Input:

I am new to TD SQL environment and learning the differences as I code.
I wanted to know if there are any resources available (other than TD DevEx) which explain the differences within TD SQL and MS SQL e.g cannot use variables except in SProc, temp tables vs volatile tables etc ?
Thanks in advance,

I am a TD newcomer and still figuring out details. 
I came across below piece of code that caught my attention.

I have a difficult task (or at least this is how I see it) to implement using SQL. Here is the case:
- there are two tables: A and E each one having a numeric column. Each of the two tables contain checkpoints(numbers) for a specific region.

What is the best thread for SAS programmers using Teradata SQL?  Especially since TD SQL and Proc SQL use different SQL languages and we have to remember which hat to put on mid paragraph!  So I think we should just have a hat with two rims we can turn from side to side.  All we need now is gang signs.
- Hurley

Here is my insert statement.


SELECT MAX(CAST (click_dt AS DATE FORMAT 'yyyy/mm/dd') ) FROM B;


I get and empty table even though the answer to below query is 5/25/2015

with drvd_amts (acct_nbr, grp_br_ps_org_id, PSAmt)
sum(d.pstd_ttl_amt) as PSAmt

from psfs.ps_gl_acct_ldgr d
where d.fiscal_yr_mth_nbr between 201500 and 201508
and d.acct_nbr between 130500 and 160500

group by d.grp_br_ps_org_id, d.acct_nbr) 


I need to generate a report for top 10 rows for each date in a table. I can write a statment for each date but can anyone help with writitng it in single query;

Hi Guys, thanks for reading the post. I did do a search for "view on view" and there was no result.

We are pleased to announce general availability of the Teradata Developer Tools for Visual Studio 15.10.
This product may be integrated with Visual Studio 2010, 2012 or 2013 and can be downloaded from the following locations:

Hi all,
I'm writing here because I didn't find any previous entry that solve my strange problem.

1) I define a table that has a column with COMPRESS clause

The following SQL function works:
REPLACE FUNCTION dl_stg.mmtest ( a VARCHAR(1) , b INT , C INT )

I am tyring to write a query that will give me the customer counts on a rolling 52 week ending on monthly basis. I can't seem to find anything anywhere that will help me. I am trying to avoid pulling the query 12 times to get the 52 week customer counts ending for each month!
this is what I have so far:

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 is he can't select any of the views of USER1 :

I have a table with Customer, Quarter, Month and Sales (montly) figures.
I need to calculate Quarter Sales and Previous Quarter Sales per Customer, while also showing Montly sales (see desired table format below).
For quarter sales I use sum() over partition by

Oracle DB offers a so-called multi-table insert statement which is very handy for ELT processing. It offers the possibility to insert multiple rows into one or more target tables from one source row. Here is a short example that should demonstrate what it does: 
-- source: source_table_1 - table with many columns

Hi All,


Order by Behavior is different in DB2 and Teradata 


I already tried adding Case specific which doesn’t work for my project related data .


Here is the main query modified (table-name etc for security reasons and its output Demo Data but similar to main one )

We are pleased to announce general availability of the Teradata Developer Tools for Visual Studio 15.01.
This product may be integrated with Visual Studio 2010, 2012 or 2013 and can be downloaded from the following locations:

Hello Experts
New year greetings. I need another help. Creating a seperate post for that.




1 SAM 50 D1

2 TOM 50 D1

3 RAM 40 D1

4 PAM 30 D1

5 JAM 30 D1

6 MAM 40 D2



I have run into a perplexing little problem. I know how to work around it, but want to understand the reason it is occuring.




When I use the FIRST_VALUE function on a date as below, it returns two rows:

Hi , I tried checking forums before posting this query. Please suggest 


I am  getting below o/p by joining couple of tables


A1   01

A1   02

A1   02

A1   03

A2   01

A2   03

A2   03

A3   01

A3   01

A3   01

This session is intended for those already familiar with writing SQL but who are somewhat new to Teradata.

while searching for a possibility to get the datatype of the columns of a view I came

I want to write SQL UDF with Multiple Lines:
1. Declare variables.
2. Set those variables with select into statement.
3. some if conditions on Variables
4. return some value.
What is the syntax to write this.
I'm using teradata Version

This presentation demystifies usage of  OLAP Analytics function for TD 15.0 and previous releases.