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

Hi!

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

6681 views
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.

23325 views
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

9603 views
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

ROW_NUMBER() OVER(PARTITION BY date_field
ORDER BY
customer, date field

9686 views
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!

13813 views
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.
Tutorials: http://www.dataminingtools.net/browsetutorials.php?tag=rdmt
Videos: http://www.dataminingtools.net/videos.php?id=8

8807 views
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.

8800 views
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.

Cheers

8133 views
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]

62324 views
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!

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

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

14684 views
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.

14496 views
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

9390 views
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 future..it 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

11375 views
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?

10411 views
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

10634 views
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

7394 views
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

8178 views
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

13063 views
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

7830 views
1 reply, 10 years ago
Passing column name as parameter to the prepared statemnt Topic by tutika_shilpa@yahoo.com 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 13.00.00.07] [Error 3707] [SQLState 42000] Syntax error, expected something like a string or a Unicode character

11940 views
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

9632 views
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.

22339 views
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.

13170 views
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

19534 views
2 replies, 10 years ago

Pages