0 - 50 of 175 tags for sql

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!

Can anyone please give me some thoughts  on this
I have a scenario where I need to create multiple extract files based on a column values using bteq script and in unx environment.

 table abc 


C_Name   ID

xxxxx        1

yyyy          1

aaaaa       2

bbbbb       2

Hi All,


I have used TRIM ( BOTH FROM string_name) command to remove extra character / space coming in export file but it is not working. could you please help to resolve this issue ?


.set format on;

.set width 1000;

.set pagelength 100000;

.set heading '';

.set footing '';

Is there any way we can specifically identify the error line number in Nexus Query Chameleon?
If not, can we somehow code this in or run a statement before running our actual code?
I know SQL Assistant can do it.

Translating descriptive dates, like last-week, with in line SQL code to achieve Partition elimination.

Hello all,
I have to select all the records of top 20 values in a field. Each value may have thousands of records. So, these values have many duplicates.
Below is a sample dataset and I would need the values as highlighted with expected value.

I am new to Teradata and I am working on query for which I greatly appreciate any help. I have a following table (after ordering by col1 and col2) 
Col1        col2             col3        
Alpha      10:00          activity typ 1
Alpha       10:00:30    activity typ 2
Alpha       10:00:40     activity typ 3

Data Warehouses are known for storing data over time, both historical point-in-time transactions as well as temporal data that is valid over a range of time...

I have source data froma a table in the following way 

Source  Data table 

Date_key          Location       Vendor           call_cnt

2013-09-09      Highlands         att               1500

2013-10-28      Highlands       att                 200

what would be the code to count the number of words in a phase using Teradata SQL without using the oreplace function as I do not have access to this.
Thanks in advance.

Hi All,
Given the following data sorted by Name and Date:
Name      Status      Date
Bob        A           2013-08-01
Bob        A           2013-08-04
Bob        C           2013-09-13
Sally       A           2013-04-17
Sally       C           2013-06-07
Sally       C           2013-07-09

I'm looking for algorithm and guidance, and i will try to write my sql. Any help is much appreciated..
Say i've following data.

I have a table that contains telecom applications livetv data. Basically I'm looking for duration between the rows (lasttime column)  at status changes on one particular column.