All Forums

Share best practices, knowledge, successes and issues in Analytics, and discuss Analytical Applications.

You must login to post to the forums.
how to compute the kpi using sql Topic by tong 19 Aug 2007

Hi,I need to compute a kpi named "Low-grade Silent Customers"which means customers that haven't use our specific service for3 months to 1 year.In our data warehouse,we store specific serviceusage records in separate tables by month,and each table has morethan one hundred million recodes,who can give me a smart sql?

9 replies, 7 years ago
get the max from table that has 2 columns unique and one duplicate Topic by geom 20 Feb 2012

Ok I was sure I knew how to do this but for some reason what I was working on was not working.

I have a table with structure like this


AccountNO        TelephoneNO     Start_Date

1234567           5191111111        01/03/2011

7654321           5191111111        04/03/2011

3459012           4161112222        04/03/2011

1 reply, 7 years ago
AJI Hits Report from DBQLObjTbl table - Query? Topic by soumyasin 28 Nov 2011 aji, dbqlobjtbl


I guess we can find out the relevance/ frequency of usage of an AJI from the table DBQLObjTbl. Can someone please let me know how?


The purpose is to find out the unused or less used AJIs in the system and thereby dropping them or finding a way to use.


Appreciate any help in this matter.



1 reply, 7 years ago
need the syntax for the tdQuery function in teradataR 1.0.1 Topic by GETeraTiger 14 Feb 2012



Trying to use the tdQuery function to return a subseet of a table into R but cannot locate the syntax.  I see the entry in the function list, but if I click on it in a browser, I get "Page cannot be displayed"

Is anyone activly using this?





3 replies, 7 years ago
Problems with Hebrew display on TeraData-based SSAS cubes Topic by shmueldar 29 Jan 2012 ssas, cube, hebrew

Hi all,

This is my first post here, and I searched the forums pretty thoroughly before, to make sure this wasn't discussed before.

I'm developing an SSAS cube with data from a TeraData DB, using a .NET Provider for TeraData.

Some of the data are dimension members written in Hebrew.

3 replies, 7 years ago
DecisionCast for Excel 2007 in Windows 7 Topic by mt 26 Jan 2012 windows, windows 7, excel, decisioncast, excel 2007, decisioncast for excel, win7

Greetings forum members!

I'm trying to get DecisionCast for Excel 2007 to work in Windows 7 and the installer just hangs.

Does anyone have experience with this product in Windows 7?

Does anyone know how to shim or fix the installer so that it can successfully complete?

Thanks for reading!


1 reply, 7 years ago
Error Connecting to JDBC Database in teradataR Topic by quinnj 06 Jan 2012 teradatar, arc jdbc table copy teradata data mover tptapi partial table copies, connection problems

Hi All, first time posting on here, so let me know if this question has already been asked or belongs somewhere else.


1 reply, 7 years ago
Taking advantage of Partition elimination Topic by prabhakaran.mails 11 Jan 2012 partition elimination

I have a Large Fact table paritioned by date(daily date) and i have another smalldimension table "dates" which has coulmns current,current_month_end,previous_month_end so which flag is set to "Y" for the corresponding date other dates is set to "N".


2 replies, 7 years ago
difference between having a filter condition in the where clause as against a on join clause Topic by prdeepak 11 Jan 2012 join, on, where, clause

Hello everyone,

  I have SQL question, about how does having a filter condition like (say) prod_cat = 7980 in the following two conditions will be treated

1. In a where clause of multiple joins

    something like a inner join b

                               on [join cond]

                               inner join c

1 reply, 7 years ago
volatile Table Topic by rajdaksha 01 Jun 2009

HiAnybody please explain volatile Table in teradata.Bcoz i have a couple of questions about for the same.

6 replies, 7 years ago
How to replace null date value with varchar Topic by a.prodhani 24 Dec 2011

According to User , I need to replace 'N/A' as Not avaiable in place of date where date value is null.

So, can anyone help me out.


2 replies, 7 years ago
Hexadecimal to integer conversion Topic by younes 25 Dec 2011 sql, hex to int conversion

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'


0 replies
Index wizard is asking me to Knock of NUPI's created my other "index artists" .. Topic by shaftngears 06 Nov 2011 query, nusi, business, objects, report

It was a complex Business Objects report with some > 30 joins and instead of coming up with some sort of Join Index recommendation I Wiz is asking me to drop a bunch of NUSI's that are'nt getting used and says it will improve the system performance 15%. I  dont follow how dropping indexes that have no relevance to queries could manage to improve performance. any thoughts on this folks ?

3 replies, 8 years ago
data distribution concept Topic by tdice 22 Nov 2011

Need to kow these basic things from teradata guru's.

I have a table employee having 4lac record.emplno and deptno are columns apart from other columns.
I have a table  dept having 1000 records and deptno is UPI.

q1) how to choose PI for employee table. data distribution is primary consideration or fetching query output is
primary criteria.

0 replies
Telling the optimizer to stop using Alias names Topic by shaftngears 07 Nov 2011

In analysing explains - I noticed that the optimizer ( I am on 13.1x) is actually using table alias names  that are referred to in view and we have to delve into each view definition to find out what those aliases mean. Is there a Diganostic statement that will prevent them using these aliases and instead just refer to the qualified table as DB.Table.

e.g. in replace view it will

0 replies
Casting char(3) to integer Topic by Jampa 24 Oct 2011

Good Day Forum,

I'm new to TD.  this is my first post here.


I have a scenario where i need to convert a char(3) to integer



-13 --- characters length should be 3  but i get 13 space characters so i cast(substr( MaxTemp,1,3) as char(3)). now i need to convvert the char(3) to integer

My script:

1 reply, 8 years ago
Help Needed in writing SQL Topic by Ansh 22 Jul 2011

Hi All,

I have the below data in 1 table :

Log Date Tablename Space Occupied(GB)
1-Jul-11 Employee 1
2-Jul-11 Employee 2
3-Jul-11 Employee 3
… …
… …
… …
31-Jul-11 Employee 5
1-Jun-11 Dept 6
2-Jun-11 Dept 7
3-Jun-11 Dept 8
… …
… …
… …
30-Jun-11 Dept 10

Now I want to write a query so as to produce the output like below :

Log Date TableName Space Occupied(GB)
Jul-11 Employee Avg of all the spaces for July Month
Jun-11 Dept Avg of all the spaces for June Month

2 replies, 8 years ago
SQL help - Median, 75th percentile price Topic by jarmoney 16 Oct 2011

I just recieved an adhoc request to get some report from teradata -

We have an Orders table that stores brand, item and price.  For every brand, for every item, we want to find out the median price, the 25th percentile price and the 75th percentile price. 

My questions are:

1 reply, 8 years ago
String Search and Replace. Topic by CapsuleCorpJX 16 Oct 2007

Is there an SQL update statement that can scan through the strings of a column, look for a particular word and replace it with another word? The column has sentences in it, and I want to replace just one word while leaving the rest of the sentence intact.

10 replies, 8 years ago
function to convert from base n to base 10 Topic by mbenita 21 Sep 2011


Does anyone have a function to convert data from a base n(in my case really a base 32) to a base 10 number?

Any help would be appreciated.


1 reply, 8 years ago
Estiamte the cost of data in DW Topic by kiboxi-866 12 Sep 2011


Is there some way to estimate the cost of deliver data from DW to "client"?

I'm looking for some generic way to do this, because in my company we don’t want to overwhelm DW with SQL queries. One have to pay for every data that comes to him.

Two simple ideas are:

1) cost base on CPU, I/O operations. But in practice there’s no way to predict usage of CPU

0 replies
10065 WSA E HOST UNREACH: Topic by abparker 22 Jan 2009

Having trouble connecting to a teradata server on a large corporate LAN. So large I can't find help internally. Getting the 10065 Host Unreachable message. Specifically, it says "The Teradata Server can't currently be reached over this network."I am new to Teradata and to this forum, so go easy on me. I suspect an internal firewall issue is causing the problem. The server is definitely up and running. It is on domain A and I am on domain B several hundred miles away. The folks in domain A can connect to the server but can't see my SQL server. I can see my SQL server but can't connect to their Teradata server.Other than a firewall issue, I suspect a shared trust may or may not exist between the two domains. Either way, am I in the ballpark or is there something specific the error message is telling me?Thanks for your help!

4 replies, 8 years ago
Using a VIEW to UNION two tables yields an Out of Spool Space condition Topic by cchristsrc 10 Aug 2011

I created a view that unions all columns in two identical tables together. They are identical in structure but not data content. When I run the SQL and join fields against the view I run out of spool space. But when I create a SQL statement that unions the one table's data with the other table, the query runs just fine. Why does using the view cause us to run out of spool space. We have COLLECT STATISTICS on all the joined fields. Any thoughts?

Example View with UNION

0 replies
Seem to be getting a 2646 spool space error when there is no spool space problem Topic by JustMe 10 Aug 2011 spool, 2646

I have a user whose job keeps abending with a 2646. When I check the spool distribution, not even one amp is touching the allocated spool ceiling. Here is how the data is distributed for the ID during the query in question ( I won't put the entire result set because there are so many rows, but I ordered by peakspool descending):

FADWDPLM $LB$&S&D&H 83 5,518,902,272.00 10,666,309,509.00
FADWDPLM $LB$&S&D&H 149 147,533,824.00 10,666,309,509.00
FADWDPLM $LB$&S&D&H 108 104,856,064.00 10,666,309,509.00

3 replies, 8 years ago
Determining how much Spool Space is available Topic by badOedipus 29 Jul 2011 spool space, account

Does anyone know of a way to determine the amount of spool space a Teradata account is allocated?

1 reply, 8 years ago