All Forums

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

You must login to post to the forums.
Need help on logic! Topic by Tnewbee 20 May 2010


I have spent 2 days trying this using OLAP functions like ROw_number but does not work out in all cases.
and I am not allowed to create a procedure.

This is my lookup table.
Team Hierarchy Cnt_Tasks SEQ_NUM
Team-B 1 22774 1
Team-R 1 11387 2
Team-G 1 1000 3
Team-R 2 1637 4
Team-Y 2 4092 5
Team-O 3 59071 6
Team-J 5 7114 7

1 reply, 9 years ago
Warehouse Miner Topic by VSong 22 Feb 2008

Our team is trying to use warehouse miner to do some fairly basic analytics and we're getting stuck (factor, cluster, etc). Can anyone recommend a good reference guide specific to this application. I've already checked out Amazon and didn't see anything that looked promising. Any and all help is much appreciated.

2 replies, 9 years ago
Need help with OLAP or If function in subquery Topic by gfuller40 13 May 2010

I have a pretty complex query that pulls the following "Insured IDs" from a table specified in the coalesce statement. The problem is that there are duplicate records with the exception of two letters in a string. I want to be able to only bring in the record with the "RT" In the string. I realize i might have to use a substring of sorts to accomplish this as well as a subquery? Perhaps an IF statement?

This is what the data looks like:

42078 00561070 610000000 06042129 Smith Joe 00561000000000000EE19470123F
42078 00561070 610000000 06042129 Smith Joe 00561000000000000RT19470123F

10 replies, 9 years ago
OLAP functions Topic by sheridany 11 May 2010

I am trying to utilize the olap functions to speed up a large query. In particular, a person can show up more than one time in a day across several days.

It looks like this:

day1 abc
day1 abc
day1 edf
day 2 abc
day2 edf

I want to keep only instance of each person by day and keep the first instance in the sorted order. they can show up every day but I only want the earliest instance.

My code look something like this.

select xxxxx

customer, date field

1 reply, 9 years ago
Calendar table calculation Topic by ratu 06 Jan 2010

Hi All,I am a newbi to this forum so can someone help me out.I am trying to get past 6 month/year numbers from a calendar table in TD. For example I can query the calendar table on the current month and it will give me this….Yr Mo2010 01Now how can I write a query so that Results set should look like this:Yr Mo 2009 122009 112009 102009 092009 082009 07It should be a rolling looking back 6 months, such as when current month is:Yr Mo2010 04Results set should look like this:Yr Mo 2010 032010 022010 012009 122009 112009 10Can anyone please help!Thanks!

3 replies, 9 years ago
collection of tutorials and videos on Data mining using R. Topic by datakid1 09 Apr 2010 data mining, tutorials, videos.

A link to a collection of tutorials and videos on R.

1 reply, 9 years ago
Data Mining - Best Practise Topic by STARTER 20 Oct 2009

Hi,Can you give me pointers to best practise for Data Mining queries on Teradata data?Thanks in anticipation,Starter.

3 replies, 9 years ago
Record distribution in AMP Topic by teradata_guy 04 Apr 2010

Hi guys

Can anyone let me know how can i get the all kind of stats relating to record distribution on AMPs in TD. If anyone having the query please post it.


1 reply, 9 years ago
Dynamic Cursor For Topic by HaRsHaD 11 Nov 2008

[font=Verdana]Hi, I am cerating a procedure, for that I need to make a cursor's Select statment dynamic. e.g. replace PROCEDURE spSample(in P_TAB varchar(10)) begin for cploop as cpcursor cursor for select * from p_tab do processing... end for; end;But it results in error SPL1027:E(L12), Missing/Invalid SQL statement'E(3807):Object 'p_tab' does not exist.'.I have tried by creating volatile/global temporary tables also but it results in same error.So is there any workaround to embed or dynamically replace the table name in FROM clause of cursor's SELECT ??RegardsHarshad [/font]

13 replies, 9 years ago
Convert columns to rows. Topic by adash-7422 05 Feb 2010

Hi ,I have a table with data like :US 110 120 130UK 50 60 70I want data to be :US 110US 120US 130UK 50UK 60UK 70The reason i want to bring all values into a single column is that I can use rank fn based on country. Please let me know if there can be a better way. Thanks!

2 replies, 9 years ago
Citrix Server Compatibility Topic by DaveBarritt 09 Oct 2008

Will Teradata SQL Assistant v work on a Citrix Server Client that uses Win 2003 (64 bit) for an OS?

1 reply, 9 years ago
Alternate for 'Qualify ' analytical function Topic by praveenjanagam 03 Feb 2010

Hi All, Qualify function is not working in my teradata system .I am getting error while executing the following query . It is not recognizing the 'qualify' key word. so is there any alternative to "qualify" so that i can execute the query ?SELECT'Total ' as Row_One, Name, Sum (Column2) Column2_Sum, Sum (Column3) Column3_Sum, Sum (Column4) Column4_Sum, (Column2_Sum + Column3_Sum + Column4_Sum) Total_Sum, Rank (Total_Sum) Total_Sum_RankFROM [table]GROUP BY NameORDER BY Total_Sum DESCQualify Total_Sum_Rank <= 10thanks in advance.

3 replies, 9 years ago
Search Test String for Column Values Topic by ThisIsBob 03 Feb 2010

I need to search a VARCHAR(256) column for values stored in another table. Any thoughts?Something like this:select text_stringfrom table1where text_string like any (select empnum from table2)Note that table2 has something like 4MM rows...BobL

2 replies, 9 years ago
Quartile fn Topic by adash-7422 01 Feb 2010

Hi,We have a requirement where we have to show top regions based on quartile calculations. So if for example we have 48 regions, we would display 12th,24th,36th region. The no. of regions an change in can be 200 as well. In that case we need to display 50,100 and so on. Can this be done dynamically? and if so how? TIA

1 reply, 9 years ago
Getting the sum without using the columns in group by Topic by adash-7422 03 Feb 2010

Hi,We have a requirement where we need to show the total sum as well as sum grouped based on levels :eg: If there are 3000 orders , 1400 is under level 1 and 1600 under level 2, then data should be like:Level 1 - 1400Level 2 - 1600Total - 3000if we use something like select sum(order_count),level from group by level then i get the level counts, what about the overall count?

2 replies, 9 years ago
How to identify PPI columns Topic by Superflash 13 Jan 2010

Hi , Is there a way to identify columns used for partitoning either in PPIs or MLPPIs select databasename , tablename , columnposition ,columnname from dbc.indiceswhere indextype ='Q'order by 1 ,2,3 ;brings back the PI of the table , not the partitioning columns . I can't see anything else to use ??Any help much appreciated.Regards SF

4 replies, 9 years ago
Error Loading Columns Topic by jafetw 20 Jan 2010

Hi All,I was trying load columns in Data Explorer. After chosing a table from available tables, I tried to chose column from available columns, but error message pop up saying "Specified cast is not valid".Anyone has encountered this problem?Appreciate your sharing.Regards,JFR

0 replies
Teradata Material for 4th, 5th and 6th V2R5 Certification Topic by Pooja Arora 16 Nov 2009

Hi All,I have cleared first 3 certification on Teradata and I am really keen to get some good material for 4th exam onwards.for exams till 3 certification I was able to find some really good material on net but I am unable to find the same for 4th onwards.I will really be thankful to you if you could help me on this.Regards,Pooja

2 replies, 9 years ago
Inserting '?' into a TD table Topic by manjeet 15 Sep 2009

Hi all,How do i insert the special character '?' into a table.ThanksManjeet

3 replies, 9 years ago
Internal Mechanism of Ranking Topic by harimon 02 Dec 2009

Hi How does Teradata actually does the ranking internally.RegardsSreehari

1 reply, 10 years ago
Passing column name as parameter to the prepared statemnt Topic by 24 Nov 2009

[font=Tahoma]Hi ,I am trying to pass the format like 'DDMMYYYY' as parameter to the prepared statement from Java .SELECT (created_date(TIMESTAMP, FORMAT ?) (VARCHAR(10))) from tableAI get the following error .com.teradata.jdbc.jdbc_4.util.JDBCException: [Teradata Database] [TeraJDBC] [Error 3707] [SQLState 42000] Syntax error, expected something like a string or a Unicode character

2 replies, 10 years ago
How to handle invalid date fromats in teradata Topic by VinodKumar 08 Dec 2009

i have to load data from file to td table ,in file i am getting invalid formats like '333/1/2009' .how to check this please help on thisRegardsVinod

0 replies
query that provides hierarchy Topic by Nolan Madson 10 Nov 2008

Can someone give me a pointer how to write a query that provides a sorted, indented organization hierarchy given a table with child org id and parent org id.

3 replies, 10 years ago
Storing multiple values in a column Topic by adash-7422 04 Oct 2009

Hello,I want to store multiple values in a column so that when i use it in IN clause all values are picked up in it as separate entities..What I mean from above is that: Suppose I have a table T1 with column C1, the data should be present as:Record_Type C11 100,200,3002 140,500,789,334,223,657I want to use C1 as follows:select * from T2 where outlet_id in (select c1 from T1 where record_type=1);As far as i know there is no array in Teradata.Let me know how it can be achieved in Teradata.Thanks in advance.

6 replies, 10 years ago
How to calculate 90th percentile in Teradata Topic by Navnit 07 Oct 2009

Seems no build in available in tera data to calculate 90th or nth percentile for the given datasetunlike ORACLE

2 replies, 10 years ago