50 - 100 of 210 tags for sql


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.

Can following query be rewritten so it does not have to use UNION ?  THANKS.
SELECT 1 AS FCST_MONTH_KEY, SUM(s.MDU_Plan), SUM(s.Bulk_Plan), SUM(s.DTH_Plan), SUM(s.CP_Plan), SUM(s.D2_Lite_Plan), SUM(s.UNKNOWNPLAN)


if(typeof(dstb)!= "undefined"){ dstb();}
Recently we switched over to TD 13 .
Previous code that contained statements with LIKE ANY('CRITERIA1%', 'CRITERIA2%', 'CRITERIA3%') syntax is now returning only matches on the first criteria matches.

     I need to filter only those records in a partition that have overlapping dates. Consider the following scenario,
1 123 1/1/2013 9/8/2013
2 123 9/1/2013 9/8/2013
3 123 9/9/2013 10/8/2013
4 245 1/1/2013 9/8/2013
5 245 9/1/2013 9/8/2013
6 245 9/9/2013 10/8/2013

The presentation is to acquaint business users with Structured Query Language (SQL) and some interesting techniques to access their data with examples for using CASE; Subqueries and Correlated Subqueries; Types of SQL joins and how they are implemented on Teradata; facts about NULLs; Derived Table usage and other interesting optimizer information.

Hi All,
I have the following SQL inside a stored procedure file and need some help.  I don't have access to the database so I can't test it out.

Table A has the value 'abc123' Table B has the value 'abc123'
I confirm this by

SELECT * FROM tableA a WHERE value='abc123'
SELECT * FROM tableB b WHERE value='abc123'

and both queries return results
however when i do:

I have requirement where i need to send mutliple files based on the data. Our enivroment is Unix and teradata.
EG: below is table with sample table.
C_Name   ID
xxxxx        1
yyyy          1
aaaaa       2
bbbbb       2
ccccc         1

Hi all


I am using Teradata Express 13.10 & I am trying to write a stored procedure which uses multiple references to dataset defined using a 'common table expression.'


I have a test case below:



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

I have a requirement i need to export a data from one table using fast export  Parallel load it into a table using MLOAD.
I can write seprated Fast export and Mload but As the table is huge and i dont want waste unix space as the fast export creates the big file.
Can we load it like a queue..

I have a list of products and a count corresponding to the quantity sold in a single table. The data is laid out as such:
Product Name     QTY_SOLD
Mouse                       23
Keyboard                  25
Monitor                     56
TV                             10
Laptop                      45

Hello Experts,
Can some please explain what is meant by Title Stacking? I came acorss a question asking "Does the AS Clause allow title stacking'?

How can I rewrite following query to avoid join condition like this :
AND SOM2.Address = SOM.ADDR_LINE1 || ' ' || ADDR_LINE2 ||' ' || CITY || ' ' || STATE || ' ' || ZIP_CODE








Hi experts,
Can you please let me know one the below behavior.

case when 1=1 then
cast('00010101' as timestamp format 'YYYYMMDD') 

O/P:  1/1/0001 00:00:00.000000