0 - 50 of 190 tags for sql

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.

Can someone elaborate on the SQL SECURITY INVOKER option in stored procedures? I need to create a stored procedure and ensure the privilege(i.e. the privilege on the underlying database objects inside the procedure) of the user calling the procedure is always checked and I think the SQL SECURITY INVOKER option achieves that. 


I've found several posts regarding the same topic of an untranslatable character, but don't know how to fix the problem.

I am facing a problem when writing a case statement, 
Here is the sample query

I'm new to TeraData. Following is the script i am using to create to While loop in Stored Procedure.
But TeraData Studio is giving me Syntax Error:
can we use loop in Stored Procedure or is there any other way to do that.
Kindly suggest.
        IN i INTEGER,

Hi. I am receiving the above error when trying to perform a password change via a third party password reset tool. We are using Windows Server 2012 R2 and the password software requires the 32 bit version of the ODBC driver. We are using 15. We have confirmed that the ODBC connection is working.

I have an audit table that I am creating a view for so I can create some reporting for. In the original table, ther serials are separated by a comma and are all in one comlumn. Here is an example:

Hi Gurus,
    Could you help me with the problem below?
    I have created a SET table with two columns: user VARCHAR(12) and prob FLOAT.
    I would like to acquire 3000000 user with bigger prob.
    So, I do like this:
    Step 1,

I'm trying to write an SQL statement based on this example data (just the first 3 columns)
799              01.06.2014   1            
799              02.06.2014   1

I have a column that counts the amounts of serial numbers in a column. This column can have multiple serials separated by a comma.
Here is the SQL to create this column:
(char_length(serial_number) - char_length(apputil_user.oreplace(serial_number, ','))) + 1 as num_serials

whenever i run any query in terdadata, i am getting blank result in columns having VARCHAR or CHAR data type. using windows 7.  please help if anyone have idea. not able to understand why CHAR values are not showing in the answersheet result?

Existing data in table test : (Temporal table)

id name    valid_dt
1  cat     2012-06-16 - 9999-12-31

Incoming Table : (Temporal)

  id name  valid_dt
  1  bat   2013-12-28 - 9999-12-31

After Merge update , test table should have

We have a table in which we have a field which is defined as VARBYTE. 
could you pleae let me know if there is a way to read the VARBYTE data in readable format ?

I have a table to be updated having 3 columns ext_JobGroupCd,ext_JobCd,ext_OrderIngroup

I'm looking to split a string in Teradata.


The table might look something like this.



I'm trying to use SUBSTRING and INSTR to extract specific words.  So, say I want to select "goodbye". I'm trying the following query. 

General format of pivot...
SELECT * FROM pivot(

ON { table_name | view_name | ( query ) }

PARTITION BY col1[, col2, ...]

[ ORDER BY order_by_columns ]

PARTITIONS('col1'[, 'col2', ...])


PIVOT_KEYS('key1', 'key2'[, ...])

PIVOT_COLUMN( 'pivot_column_name' )

I'm trying to run this recursive query but it's giving me an error:

Does anybody knows if there is a portable version of Sql Assistant?
Like a folder I can copy to my pc and just run the .exe?

Hi guys,
I'm a student at Teradata. I found some diffeence when running SQL in TD and Oracle.
Can anyone help to explain the parsing and executing principle of TD SQL?
It'll be great if there is a comparision.
Thanks  alot!