100 - 150 of 210 tags for sql


Hi All,
I would like to know, in Teradata, does it allow single quotes for numeric datatype (int or decimal)?
I have tried TD using quotes. It works. but one of my friend is facing issue when using single quotes.
create table lavendar.alpha(eno integer,salary decimal(12,2));

Hi, I'm Sebastian and I need your help you. I have rows with '    ' data, for example:
My table have 1 row of the name 'tx_name' and... in the name have:
'     Pepito'(in that case use trim for remove )
'Pepito      Albert'(How remove it this space?)
I try with:

I would like your help for the next issue.It is a real problem,but i will presente you like an example.
I have a view which is composed as below
viewa =
sel * from
inner join
sel * from table_a
inner join table_b

Hi, I am trying to import data from a text file using a SQLAssitant. But it is giving me an error as the string contains ",".
I am using this below syntax for inserting:




INSERT INTO kumar_test(?,?)


Install Teradata Client on Unix Sun Solaris SPARC and general x86 machines

Hi All
I'm trying to implement Oracle REPLACE() function in teradata using SQL statements only. I searched the forum , but found Oreplace() UDF as the only solution.  Can someone help me in implementing this is pure teradata SQL  ?? 

I need to load a file using mload where  the load clm has a data like this 03/29/2013 12:00:00 PM.
select cast(cast('03/29/2013 12:00:00 PM' as timestamp(0) format 'MM/DD/YYYYBHH:MI:SSBT') as varchar(22))
I need it to load as Timestamp rather than varchar

Hi there...
I'm pretty nobbish in this type of SQL so please try not to be rough for me :)
Well.. I have to convert numeric field to date, than count some upload data and group by table.
Numeric_column like 20121121133549 which is probably like YYYYMMDDHHMMSS

I need to export file with certain fields. Where one of coulmn data has a CR.
For eg:
 record1 aaa      bbb       ccc
 record2 ddd      eee       ffff 
The spaces between the aaa bbb represents the New Line Char..

My Source data has only two digit year when i am trying to convert to timestamp
sel cast('12/12/12 00:00:00' as timestamp(0) format 'MM/DD/YYBHH:MI:SS')
12/12/1912 00:00:00
tried with the current_timestamp(0)
sel cast(current_timestamp(0) as timestamp(0) format 'MM/DD/YYBHH:MI:SS')

My Source data has only two digit year when i am trying to convert to timestamp
sel cast('12/12/12 00:00:00' as timestamp(0) format 'MM/DD/YYBHH:MI:SS')
12/12/1912 00:00:00
tried with the current_timestamp(0)
sel cast(current_timestamp(0) as timestamp(0) format 'MM/DD/YYBHH:MI:SS')

I've run into an issue with a rollup I am trying to do and was wondering if anyone has any ideas on how to do it in as few steps as possible.
I have used several olap functions to build a table that has the following data:

I have a timestamp with time zone field and I would like to get the number of seconds that have elapsed from the start of that day.  This seems like it should be very trivial but I've searched for hours and tried many things that don't work.  What is a simple method for getting the number of seconds?

Hi, I have one scenario and I am looking for your help.
we have large DW with almost 10 different soruces with 400 tables. So, I want to know what are all sources are populating each table. 
in all these tables SRC_CD is Mandatory.

I need to insert data from a field A which is an integer to field B which is TIME(6).
The data in field A is something like this:
1. 1256, which means 12:56:00
2. 0145, which means 01:45:00
I want to store it in field B as 12:56:00 and 01:45:00
Can you please help?


I am running a sql query using SAS EG. It is essentially an explicit sql query that is wrapped within sas commands that make it run on the teradata engine. I need help resolving this error.
NOTE: There were 144405 observations read from the data set XXX.MCID_TMP.

I have the following tables EMPLOYEE& DEPT.
I am writing the following query :
validtime as of date '2001-01-01'
select *
on emp.dept_id=dept.dept_id;

I have a table (I'll call this DTM) which has a column of the type FLOAT (I'll call this VAL).  This table currently includes rows in which the value of that column is NaN (not a number).  We are inserting these values through DataStage / Java, which is taking the values of "NaN", "+Infinity" and "-Infinity" (all legit IEEE 754 floats) from a text file an

Hi, can anyone tell me how to check the progress of my SQL Query?  I've looked around on viewpoint but the best I get to is to check wheter or not is running or if there is a CPU skew.  The hep told me to click on session in the query monitor, but that's not doing anything.  Anyone?




Playing with some engine designs that require volatile tables structures based on existing tables structures on a system. The number of unique table structures is very high, so minimal customization should be required or it becomes a sustainability problem.

Very simple example: 


I am a beginner concerning the Teradata. I was trying to find simple descriptions on the internet about functions QUALIFY; RANK; OVER; PARTITION BY - unfortunately unsuccessfully. I coudn't find a simple explanation what does each of the funcions do.

Can anyone send me a link or give a explanation about this functions.




Is there any tool or method or something else which can help me with analysis of the process.

The process contains around 15 steps and each job step is represented with one SQL script. I am working with Teradata.

I am trying to import a file that contains the folowing information. I ahve tried it both as a txt file and csv file. Also have gone into "options" to make sure columns are sepreated by the correct format...tab/comma/etc. The file loads completely, but when I view the table all the columns are empty. Any ideas as to why this is occuring?

Select EmailAddress, CreatedDate
From _ListSubscribers
Where ListID = 10630 AND CreatedDate > (Select DATEADD (day,-90,getdate()))

Hello everyone,

I need some help figuring out how to drop the last four digits of a 9 digit zip code. If I have a list of subscribers with a couple hundred thousand that have zip codes withe the following format...."27751-9099". Is there anyway that I can remove the last four digits so that we can compare zip codes based on a 5 digit basis?

Here is what I have that is causing the error........












I am trying to import some data and it keeps giving me a format error. Is this because I am not including the column headers? Thanks for the help!

I need some help as to how I can import a .csv file with the date format as MMDDYYYY so that it changes to YYYYMMDD. I know you can do a INSERT INTO table.name function and add the FORMAT(column name) function, but I am not exactly sure of the syntax and how to correctly do it. Any help or guidance is appreciated.

I have a table with a column of alpha-numeric data the currently has multiple (periods) "." and spaces in the string.

I need to remove them for data compare in another DBMS.

I have searched not only through this forum but others as well and can not seem to find the solution.

Here is what I am trying to do... I am designing an extract process to export cusomters and orders.   The input will be list of customers and a date range.

The first thing it has to do is find all the orders in that data range for that group of customers.  Query on the order table.

I'm curious to see if there is a way to get period-to-date values (e.g. WTD, MTD, QTD, YTD) without using a CTE or subquery. Here is an example query using a CTE:

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. Storing this data is one thing, but retrieving it to answer critical business questions can be a Structured Query Language (SQL) challenge...

Suprisingly, I'm not finding many examples of this out there.  

I have a table like:


id  foo

--  ----

1   yes

2   yes 

3   no


i want a 1 row result set like: 



1: yes, 2: yes, 3: no


I want to Update and Insert when a condition in the case was true. However, it seems that Teradata doesn't accept it. My question is, is there a way for that to happen?



CASE WHEN SEL Table1.Column return TRUE






Are there any doc's which discuss SQL UDF's?  (Especially the various qualifiers in the CREATE FUNCTION statement).  I am trying to determine why my regular SQL works as expected, but when you put the same code into an SQL UDF, it returns unexpected results.  Any insight would be greatly appreciated.

I have to join two tables using a unique field but the trouble is one table has placed that unique field in all caps and the other table has the field in all lowercase. When joining these two they bring back nothing. When I look up the data individually I can find the same records in both tables.

Hellooo , i have a table "table1" with one field named "field1" where it contains hex values .

i want to convert the values in this field to integer by one query :


select function(field1) from table1


possible values for example  : 'B52B' ,'DBC0' ,'963B'



I am far from new to database programming but totally new to Teradata and really struggling with stored procedures (amongst other things).

I have a particular problem at the moment with a stored procedure failing with the error message "SELECT-INTO returned more than one row".

Hi Masters,

I am looking for a better approach for the below requirement with out using Union

Input table:  (total volume will be 100 million records)












Set SQL is a way of operating on data that is radically different from historical concepts of computer programming that can be diagrammed with flow charts.  Grab a cup of coffee and review some history that will explain why the industry is mired in cursor logic and loops that cannot exploit the power of today's software and hardware.



How to get "View vs Table dependency" from dictionary tables by simple querying them? Query needs to list the table names  used by a View. It is possible in Oracle using dba_dependencies and similar table to get the list of tables used by a view. Please advice.


Is there a permission that will allow a particular user (ME) to execute an EXPLAIN of Sql that updates or inserts into tables or selects from tables  that  I don't have update or insert or select permissions on?  (or creates a table in a database that the "Explaining user" doesn't have permission to)?

I have a table with the below sample data (minus 'desired output' column).  I need to figure out how to query that data to get the values in the 'desired output' column.  I only care about the EMPL_ID, DATE_IN, DATE_OUT data and want to determine the total number of consecutive nights the employee stayed in a hotel (no matter what hotel). 

Hi, I have a table called Employee with Join Date and Last Date. This table may also contain future employees. Now I want to write a View to list all employees (current and future; but not previous employees). Can anyone suggest me best Date logic to get this information.


As you know, Teradata handles ANSI-SQL as standard but also supports various Teradata SQL extensions to the ANSI-SQL, like EXPLAIN, SHOW, HELP, COLLECT STATS, etc...

Would there be a complete list of these Teradata SQL extensions anywhere?  If anyone has one, that'd be great.  If they have explanations, that'd be even greater.

Top Tier Financial Client has Multiple positions open for Data Analysts. Multiple Locations. Immediate Interviews. Read below for Details.

Locations: Richmond, VA / Mclean, VA / Plano, TX

The job description:

I have a column that has both numbers and texts in the fields.
xyz 120 abc lmnop
xy 120
?/ 567 abc
.... 233

As you can see the fields can have numbers, texts, special chacters, null values, etc. If there is a no numbers in a field I want to return null for that.

I am currently working on a requirement that deals with various effective spans for records. The final result should show overall effective spans per CUSTOMER_ID. However, date entries can be split up over multiple entries and locations:

ABC 123 100B 01/01/2010 03/15/2010
ABC 142 100C 03/01/2010 04/30/2010
ABC 301 300A 06/01/2010 08/15/2010
ABC 425 400B 06/15/2010 06/30/2010
ABC 259 300C 07/15/2010 12/31/2010
ABC 450 400C 10/01/2010 03/01/2011