Teradata Forums / Teradata / Teradata Advanced Analytics User Group

Names of Journal/magazine/conferences to publish a paper

SGK — Fri, 25 Apr 2008 01:15:46 GMT

Hi Experts,We are planning to publish a paper,can someone give me pointers about some standard Journals/magazines w.r.to teradata/SQL/DWH.Also how do i ensure my paper was not published already in any journals.Kindly guide me guys.Regards,SGK

sql Help need

satyapal — Wed, 16 Apr 2008 20:18:14 GMT

I have a table with following data: carrier first_reason_cde first_qty second_reas_cde second_qty third_reas_cde third_qty111 10 2 0 0 0 0111 0 0 11 1 0 0111 0 0 0 0 11 2222 0 0 12 4 0 0222 11 1 0 0 0 0222 0 0 0 0 13 4so on ----- --- --- --- -- ---- --- Desired Output: carrier first_reason_cde first_qty second_reas_cde second_qty third_reas_cde third_qty111 10 2 11 1 11 2222 11 1 12 4 13 4---- ----------- -- ------------------THANKSSATYA

Foreign Key Problem

Superflash — Tue, 29 Jan 2008 08:48:30 GMT

I'm running an insert select into a new table with a foreign key check constraint and getting an error 3807 Object Spool 2 does not exist. when I run the select part of the query it works fine. when I take the foreign key off the table being inserted to it works fine.I can't even run an explain on the insert query - which implies its not my data.Has anyone come across this before or have any immediate ideas ?

Is there a performance hit selecting rows based upon a constructed CASE column?

TimManns — Wed, 02 Apr 2008 17:35:59 GMT

Hi,Quick question. I think there is no difference, but I need to be sure.In a SELECT or INSERT statement if I use a CASE statement to create a new column and then select rows based upon the value of that new column, is it any slower (or faster) than simply repeating the logic of the CASE statement as a direct WHERE condition?I'm processing a *lot* of data with nasty string functions and I need every ounce of optimisation I can get :)ThanksTim

Code help needed to summarise table

stan_lee — Mon, 17 Mar 2008 12:07:40 GMT

Hi,I'm working with this table that has account numbers and event time stamps from call centre history logs. I want to be able to summarise the table to show the min and max timestamp for call (the example data shows two calls for account number 12345). Any ideas ?Account Timestamp12345 08:21:32 (call 1 start)12345 08:21:3512345 08:22:4912345 08:23:0012345 08:23:35 (call 1 end)12345 12:59:32 (call 2 start)12345 12:59:5912345 13:02:17 (call 2 end)

Analyze explain plan

newtera — Tue, 04 Dec 2007 03:25:48 GMT

Hi,We have the below explain plan for inserting into a global temp table. i have few questions on the same. Kindly help me understanding the same.1) what does "pseudo table" mean ? is this a temp table create for global temp table ?2) what does DBC.TVM,DBC.DBase mean ?3) i do not see any insert into the global temprary table. can someone explain how data is inerted into global temporary table 1) First, we lock a distinct AB28880."pseudo table" for exclusive use on a RowHash to prevent global deadlock for AB28880.sample_MACRO. 2) Next, we lock AB28880.sample_MACRO for exclusive use. 3) We lock DBC.TVM for write on a RowHash. 4) We execute the following steps in parallel. 1) We do a single-AMP ABORT test from DBC.DBase by way of the unique primary index. 2) We do a single-AMP ABORT test from DBC.TVM by way of the unique primary index. 3) We do a single-AMP DELETE from DBC.TVFields by way of the primary index with no residual conditions. 4) We do a single-AMP RETRIEVE step from DBC.TVM by way of the unique primary index with no residual conditions into Spool 1 (group_amps), which is redistributed by hash code to all AMPs. 5) We do a single-AMP DELETE from DBC.TVM by way of the unique primary index with no residual conditions. 5) We do a group-AMP MERGE into DBC.TVM from Spool 1 (Last Use). 6) We spoil the parser's dictionary cache for the table. 7) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request. -> No rows are returned to the user as the result of statement 1. thanks,teradata developer

Single Amp SQL Issue

bknightly — Tue, 26 Feb 2008 14:13:51 GMT

I'm trying to create a flat file, with a delim of ;, and without spaces between each column. The query I'm using below doesn't work in Fast Export due to it running on a single amp. When I remove the trim criteria, it runs without issue. What would cause the single amp error to occur when running this sql below? Thanks in advance for your help!select substring(trim(col1) from 1 for 10) || ';', substring(trim(col2) from 1 for 10)||';', substring(trim(col3) from 1 for 1)||';', substring(trim(col4) from 1 for 5)||';' FROM where col1 in (criteria);

Select wholly number values from a varchar column

TimManns — Wed, 13 Feb 2008 17:42:35 GMT

I am trying to select the value of a varchar(32) column if it contains entirely numeric characters. The varchar column contains values of that could be a mixture of numeric and alphanumeric characters (no special characters). The rows I need to select are the ones that are entirely numeric, and I was thinking a CAST function could be used t return rows that could successfully be CAST as float. Something like;WHERE NOT((CAST( as FLOAT) IS NULL)This doesn't work, and I'm going to be processing millions of rows of data, so I'd like to avoid LIKE functions or anything that would take a long time :)Any ideas?ThanksTim

how to compute the kpi using sql

tong — Sat, 18 Aug 2007 09:32:08 GMT

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

Integer to date conversion - Error

tera_cert — Mon, 29 Oct 2007 19:14:38 GMT

Here is the query i am trying to run :select cast(cast(cast(cal.cal_id as integer format '9999999999') as varchar(10) ) as timestamp(0) format 'YYYYMMDDHH') from miesa01.calen cal Here cal_id is an integer column.. i have tried all the variations for this .. the strange part is the following query works perfectly fineselect cast(cast(cast(2007090909 as integer format '9999999999') as varchar(10) ) as timestamp(0) format 'YYYYMMDDHH') from miesa01.D_CAL_CALENDAR cal Please try to pur your ideas and experience. Thanks,Laxmi

QUANTILE(100) x 50 times...

TimManns — Thu, 08 Nov 2007 03:41:00 GMT

Hello,I'm not a great SQL coder (a lot of my analysis is automatically generated SQL), but occaisionally I have to write some SQL by hand to perform tricky analysis. I'm trying to figure out an effcient method to create percentiles for each of 50 columns (within the same table). By this I mean that I have 50 columns (stuff like call_count, sms_count, voice_count etc). I need to allocate an integer percentage to each row. The rows containing the lowest value a column will have a value 1%, whilst the highest values will have 100%. I will be creating 50 additional columns, one percentile column for each existing column. There are 3.5 million rows. I thought I could perfrom QUANTILE(100, ) upon the 50 columns in a single statement. Running the analysis against just one column gives me a result in 30 secs. EXPLAIN for 50 columns quotes millions of hours to completion... I presumed I will run out of spool space long before that process completed.So, I tried running 50 separate QUANTILE(100 insert queries, which all succeed very quickly. I now have 50 tables, each containing 3.5 million rows and two columns (key and the percentile). But now I can't seem to join the 50 tables by the inner join unique non null key (EXPLAIN seems to have hung).So, I have two questions; - Any suggestions for another way to create percentiles for 50 columns? - Any reason why an inner join against 50 tables, all with the same unique indexed primary key, would be a problem?ThanksTim

spool variation question

jeff_o — Fri, 02 Nov 2007 14:11:05 GMT

I am comparing two queries for performance. Version "A" is the old one and version "B" is new and improved.I've run version "A" 10 times and version "B" ten times. All the runs from version "A" are identical sql and all of version "B" are identical. When I look in the DBQL spool usage I see that version "A" always has exactly the same spool use but version "B" has a varying spool use. It varies between 317,575,168 and 317,705,728 whereas version "A" is always 1,194,763,776.Does anybody know what might account for the varying spool use?Thanks

how to extract only mondays in 2007

chinnababu — Thu, 01 Mar 2007 17:03:05 GMT

Needs extract only mondays in 2007.

how can i achevie using Teradata SQL

Thanks
Chinna

Naming Primary Indexes

Select * — Thu, 27 Sep 2007 14:21:30 GMT

I need some helpe to name primary indexes.

select column1, column2, column3
from table name
with data
primary index (column1, column2, column3) How do I name this index

If I only insert into this table and collect statistics on the index name, is the table going to be skewd or is there a definition stored?

fastload perform better that multiload while loading empty tables--reason?

syam — Tue, 11 Sep 2007 09:31:50 GMT

Just i would like to know the reason, in what way fast load will perform better than multiload when we are loading empty tables?

Internal error

toni — Fri, 10 Aug 2007 02:47:42 GMT

Hi,

Can anyone tell me what this error means and when it happens.

FnName: Execute Direct -- [NCR][ODBC Teradata][Teradata Database] Internal error: Please do not resubmit the last request. SubCode, CrashCode:
]

Regards,
Antony

Problem with starting Terdata thrgh Teradata Service Control

sashi — Mon, 23 Jul 2007 19:33:22 GMT

Hi All,

Whenever I tried to start terdata thrgh Teradata Service Control, I am getting Gatreway must be started. And its asking me to reboot or reinstall.

In services I could see only Bynet which is running fine.

Details:
OS - Windows XP sp2
Teradata Version: V2R6.1

I would really appreciate if someone gives me the solution.

Thanks,
SK

PARTY DEMOGRAPHIC HIST in CLDM

JorgeCarrasco — Tue, 15 May 2007 15:58:29 GMT

Hi all,

I would like to know what is the reason for modeling the demographics of a party in the CLDM, FSLDM with the posible values already set in the DEMOGRAPHIC VALUE entity vs creating a column in the PARTY DEMOGRAPHIC HIST like Party_Demographic_Val VARCHAR(50) and put any value in there. Maybe similar to FEATURES in the FSLDM.

Thanks
Jorge

Data Miners - please participate in this survey

Karl Rexer — Sun, 29 Apr 2007 21:41:24 GMT

We think that Teradata Advanced Analytics discussion members will be interested in participating in this survey and also in seeing the results.

Data Miners - please participate in this survey: http://www.rexeranalytics.com/Data-Miner-Survey-Intro2.html.
A free summary of the results is available to anyone who wants it.

More information:
Rexer Analytics, a data mining consulting firm, is conducting a survey of the analytic behaviors, needs and preferences of data mining professionals. We would greatly appreciate it if you would:
1) Participate in this survey, and
2) Tell other data miners about the survey (send them the survey link).

We hope to get as wide a representation of those in the field as possible, and your assistance is invaluable.

In advance, we would like to thank you for your time. We hope the results from this survey provide useful information to the data mining community.

-- Karl Rexer

how to select part of string

sham — Tue, 03 Apr 2007 18:52:37 GMT

In the foll Eg... , I want to select part of the string before numeric. If the first word numeric, want to select 2nd word to before numeric.

O PREMIUM WATER ACH 070228 DIR PAYMENTXXXX YYYYY
94009 NASE PAYMENT 94009 9544537498XXXX YYYYYY
NATIONWIDE INS CUST-AGNT 070312 43000-934483250 AGENT CENTER
Sprint PCS Telcom 070302 0584245200xxxx yyyyy
WEST COAST LIFE INS. PREM. 070228 WC1Z02418843xxxx y zzzz
STATE FARM RO 08 SFPP 08 S 043840zzzz tttt x
THE LIFESTYLE CLUB DRAFT 030507 28802xxxx yyyy
PRIMERICA LIFE INS. PREM MAR,07 000002343Yx y zzzz

I want to be able to select Only

O PREMIUM WATER ACH
NASE PAYMENT
NATIONWIDE INS CUST-AGNT
Sprint PCS Telcom
WEST COAST LIFE INS. PREM.
STATE FARM RO
THE LIFESTYLE CLUB DRAFT
PRIMERICA LIFE INS. PREM

Thanks very much in advance.
Sham

Masking commas within a macro parameter

Scott — Thu, 29 Mar 2007 21:47:05 GMT

Hi,

I was wondering if anyone knows a simple way of masking a comma within a given macro parameter so that Teradata doesn't interpret the parameter as multiple parameters.

eg:
CREATE MACRO test(prodno) AS
(
SELECT *
FROM products
WHERE product_code IN (:prodno)
;
);

EXEC test(12, 13);

The number of products could be one or more (ie a variable number).

Obviously the macro and execution, as is, produces an error as Teradata is only expecting one parameter, but sees two.

Is there a simple way around this?

Thanks in advance for your help.

Cheers,
Scott.

need to Compare header date in file 1 with system date in file 2

chinnababu — Tue, 27 Feb 2007 15:31:57 GMT

I have 2 input files i.e file 1 and file2
file 1 is header file which contains date as 02/27/2006
file 2 contains system date
i have to compare file1 and file2 dates if they match i need to process otherwise needs to abend
how can i write Teradata sql

Help me in this regard
Thanks in advance and also thanks for earlier replys to my questions

Regards
Chinnababu

Teradata DBA-- CISCO

rohitpuri — Thu, 15 Mar 2007 19:50:18 GMT

Hi everyone.I am a technical recruiter working in Akraya Inc. in San Jose. I have an urgent requirement for a Teradata DBA with CISCO in San Jose//RTP,North Carolina. Would someone be kind enough to give me leads or any references for the position.

Cisco wants the candidate to have specific skills in
1. Priority Scheduler
2. TDWM
3. TASM (preferred)
4. Backup/ Recovery
5. Knowledge on Performance tuning techniques
6. Good understanding on Physical Modelling
7. Clear idea on Teradata Architecture

Kindly respond back to me @ rohit.puri@akraya.com
Tel: 408-512-2358.

How to optimize queries with grouping sets?

teddyreyes — Mon, 26 Feb 2007 03:35:23 GMT

I have a query which uses grouping sets that takes a lot of time. What indexes can I define and on which fields of the table?

For discussion purposes, below is my query:

select
COL1 ,
COL2,
COL3 ,
COL4,
COL5,
COL6 ,
COL7 ,
COL8,
COL9,
COL10 ,
COL11,
COL12,
count(distinct(COL13 ))
from TABLE_ONE
group by rollup(1,2,3), rollup(4,5,6), rollup (7,8), grouping sets ((12,11),(12,10),(12,9),());

Thanks in advance...

need to Compare header date in file 1 with system date in file 2

chinnababu — Mon, 26 Feb 2007 21:40:52 GMT

How do i drop multiple tables

letangc — Thu, 22 Feb 2007 17:08:19 GMT

I am new to the treadata environment, and would like to delete tables in my work environment (temporary work space)
For example
drop table s_mrktng.cletang_1486386770;
drop table s_mrktng.cletang_1486409292;
drop table s_mrktng.cletang_1486409363;
drop table s_mrktng.cletang_1486409456;
drop table s_mrktng.cletang_1486409527;

but instead of type the file name over and over can i do
drop table s_mrktng.cletang_1486* ;

If * is not the wild card is there one that i could use.

Thanks

to Obtain the 5-character code (translated into upper-case) following the fourth comma

chinnababu — Wed, 07 Feb 2007 12:40:58 GMT

I want to write a teradata SQL to retrieve the 5th digit value followed by 4th comma and needs to translate it to upper case

and if the string does not contain any comma OR less than 4 commas, then the query should come back with nulls.

see below wbp98 in 1st row after 4th comma and needs to translate to uppercase
x1vqm-2nd row after 4th comma needs to translate to uppercase
VLHHV-3rd row after 4th comma needs to translate to uppercase

ex rows in a colomn
P09-2001365000042108792,09262006,08022006,,wbp98,wbp98,,22,4,6,2,20,Landscaping,2,,1,2,,3,,5,2,,2,2,2,2,2,,-1,0,1,1,,0,0,2,,,2,,,2,559464948,545804394,,,,CM-N/C

P09-2005120107000029245,10262006,10262006,,x1vqm,f46cr,,3,12,6,2,20,Health,2,,1,2,,3,,7,2,,2,2,2,2,2,,-0.5,0,1,1,,0,0,2,,,2,2,2,2,132708155,,,,,CM-N/C

P19-2005181104000993310,01252007,11142006,91000088687315,VLHHV,W1KFN,,6,14,6,2,20,Non-Profit,2,,2,2,,10,,4,2,,2,2,2,3,2,,-1,0,1,1,,0,0,8,,,2,,,,,,,,,CM-N/C
Please help me in this issue

Thanks in advance who helped me earlier
Chinna Babu

delete rows older than 2 years from current date

chinnababu — Tue, 06 Feb 2007 21:40:12 GMT

i am running job today 2007-02-06...i want to delete data prior to 2005-02-06
please help me in this regard

Thanks in advance
chinna

Customer Value - What & How?

Curious UT — Thu, 28 Sep 2006 05:01:44 GMT

I've heard that "Customer Value" analysis is the hottest thing in advanced analytics these days.

Can anyone tell me:

What are the different aspects of customer value and which decisions do they support?

What are the best practices in the creation of customer value assessments?

What components are required to build understanding of customer value?

Do you have any war-stories to share from your experience with Customer Value?

Need datatime information

kuldeepsingh — Mon, 11 Dec 2006 14:57:17 GMT

I am a new user of Teradata sql and etl process. I need some information regarding creating tables and datatypes. Here is my issue. I have data in sql server that I am trying to bring into teradata. The table is pretty straight forward in sqlserver and looks like this,

CREATE TABLE SensorData (
RecordId int IDENTITY(1000,1),
ServerId int NULL,
SensorId int NULL,
ServerDate datetime NULL,
CreateDate datetime NULL DEFAULT CURRENT_TIMESTAMP,
ValueA money NULL,
ValueB money NULL,
State varchar(15) NULL,
Humidity money NULL,
Temperature money NULL,
Analog money NULL,
SensorType tinyint NULL,
InHrOfOp bit NOT NULL DEFAULT 0
)

in Teradata I am trying to use the following,

CREATE TABLE edwdev.Counter_SensorData, NO FALLBACK,
NO BEFORE JOURNAL,
NO AFTER JOURNAL
(
RecordId INTEGER NOT NULL,
ServerId INTEGER,
SensorId INTEGER,
ServerDate DATE FORMAT 'YYYY-MM-DDBHH:MI:SSBT',
CreateDate DATE FORMAT 'YYYY-MM-DDBHH:MI:SSBT' DEFAULT CURRENT_TIMESTAMP,
ValueA varchar(5) CHARACTER SET LATIN NOT CASESPECIFIC,
ValueB varchar(5) CHARACTER SET LATIN NOT CASESPECIFIC,
State varchar(15) CHARACTER SET LATIN NOT CASESPECIFIC,
Humidity varchar(4) CHARACTER SET LATIN NOT CASESPECIFIC,
Temperature varchar(4) CHARACTER SET LATIN NOT CASESPECIFIC,
Analog varchar(4) CHARACTER SET LATIN NOT CASESPECIFIC,
SensorType char(1) CHARACTER SET LATIN NOT CASESPECIFIC,
InHrOfOp char(1) CHARACTER SET LATIN NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX XPKCOUNTER_SENSORDATA (RecordId);

When I run the above I get an error,

Invalid FORMAT string 'YYYY-MM-DDBHH:MI:SSBT' (3530)

The data in the source system is in the format,

11/29/2006 11:50:00 PM

and that is how I plan on bringing the data into the target (teradata).

Does anyone see why I am getting the above error? I looked in SQL Reference and it seems that I can use the above date format.

I would appreciate any help here and if I solve this, I will post my findings on here as well.
Thank you,
Kuldeep