Teradata Forums / Teradata / Teradata Advanced Analytics User Group

what is best way to query huge table primary partitioned by date and indexed by id and date

TimManns — Mon, 15 Jun 2009 23:36:02 GMT

I'm in telecommunications.I can run a query which completes in several hours (against 1 month of data), but I am looking for a way to optimise my query to make it run faster. I have a generic-ish question about how I should index a cross-join table or optmise my query. - bit of background - I'm working on a huge detail table with billions of rows, containing call detail rows (cdr's). The table is primary partitioned by date and primary indexed by id (phone number) and date. There are many rows with the same id and date (multiple phone calls in a single day).At the moment in one single query I create a cross-join with a few million id's (unique) each duplicated by 30 days (from sys_calendar). This cross-join part then inner-joins to the huge detail table. The query completes in several hours, although Explain looks great and says it should take a few minutes :) I'm using all the correct indexes etc and no DBA can fault the query.I tried to break this query down so that the cross-join was a separate step and outputted the data into a table with primary index of id (phone number) and date. I hoped the query would run faster if I index the cross-join table to match the huge detail table. Creating the volatile cross-join table takes a few minutes. I then inner join this volatile cross-join table to the huge detail table, but after 8 hours (out of normal office hours) it hadn't completed and i killed it.- question -Any advice on how I might get a huge cross-join query against several billion rows to run faster?ThanksTim

How to calculate 90th percentile in Teradata

Navnit — Wed, 07 Oct 2009 03:04:28 GMT

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

Difference between select * from TABLE & select individual columns

az_maverick — Thu, 20 Aug 2009 14:18:15 GMT

Hello all, I am having trouble understanding difference select * from TABLE X or select a , b, c ( columns) from table X ( same table ) above.I was wondering if someone actually knows what is the difference at the Teradata DB Architecture level.Thanks,Aditi

Teradata admin

skumar1 — Thu, 09 Jul 2009 18:23:22 GMT

I have created a role and granted this role to the user but the user can't access the database that is granted to the role.I have executed the follwoing.create role x;grant select on edw_tb to x;grant x to user1;What I am doing wrong here?

Teradata coding standards, review checklist, performance checklist - Help me

premgnc1983 — Sat, 11 Jul 2009 06:48:27 GMT

Hi ,I am now preparing coding standards, review checklist, performance checklist for teradata. If anybody has these checklists please provide me so that it will be of great help for me. If not atleast please provide me the points which you all have taken care during coding which helped in improving the performance of the code and also the standards you all followed while coding.Regards,Prem

Generate errors for intersection of queries

az_maverick — Mon, 30 Mar 2009 19:29:20 GMT

How do i generate a error ,when a set of records ina query do not intersect a set of records in another query.For eg,if I have a query ,select A,b from ABC INTERSECT select B,d from XYZI want to display all records in ABC which do not intersect the b,d in XYZ. What logic do I use ?Thank you,az_maverick

To know roll access

MDF — Mon, 15 Jun 2009 13:46:03 GMT

Hi,I m new to teradata.I don't have access on DBC. Can anyone tell me, how come i will know which are the database have given me the access in Teradata.Thanks

Target Date issues in warehouse miner...

umairulhassan — Thu, 15 Jan 2009 00:44:10 GMT

I am using Target Date literal in Variable Creation analysis in Where Clause. But when I use same variable creation in Refresh Analysis, the Target Date does not change accordingly.Any suggestion...

DEMOGRAPHIC COMPLIANCE in cLDM

cremen — Wed, 13 May 2009 05:39:53 GMT

I am not clear the definition in this entity "Designates the degree upon which a DEMOGRAPHIC is met. Examples:1 - Completely met,2 - Partially met,3 - Not met."Have anybody give me the example data or any cases to explode my imagination please?

Teradata Optimization needed on query.

lucky_ipsu — Thu, 02 Apr 2009 02:20:37 GMT

Hi All,Below is the explain plan for the query which needs tobe optimized. Sorry could not attach it due to some security problems. Do let me know which part of the explain needs to be modified to optimize the query.Explanation 1) First, we lock aedwprd1.membership_fact_ for access, we lock aedwprd1.src_sys_dim_ for access, we lock aedwprd1.prod_var_ernng_cmpnt_dim_ for access, we lock aedwprd1.mbrshp_covg_mth_strt_dim_ for access, we lock aedwprd1.mbrshp_acct_mth_dim_ for access, we lock aedwprd1.firm_dim_ for access, and we lock aedwprd1.chrtfld_dim_ for access. 2) Next, we do an all-AMPs RETRIEVE step from aedwprd1.mbrshp_covg_mth_strt_dim_ by way of an all-rows scan with" a condition of (""(aedwprd1.mbrshp_covg_mth_strt_dim_.yr_mth_nbr <=" 200810.) AND (aedwprd1.mbrshp_covg_mth_strt_dim_.yr_mth_nbr >=" 200801.)"") into Spool 4 (all_amps) (compressed columns allowed)," which is duplicated on all AMPs. Then we do a SORT to order Spool 4 by row hash. The size of Spool 4 is estimated with low confidence to be 2,310 rows. The estimated time for this step is 0.00 seconds. 3) We execute the following steps in parallel. 1) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an all-rows scan, which is joined to aedwprd1.membership_fact_ by way of a traversal of index # 12 without accessing the base table extracting row ids only. Spool 4 and aedwprd1.membership_fact_ are joined using a" nested join, with a join condition of (""dt_dt ="" aedwprd1.membership_fact_.covg_mth_strt_dt""). The input" table aedwprd1.membership_fact_ will not be cached in memory. The result goes into Spool 5 (all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 5 by field Id 1. The size of Spool 5 is estimated with low confidence to be 7,149,465 rows. The estimated time for this step is 2.59 seconds. 2) We do an all-AMPs RETRIEVE step from aedwprd1.src_sys_dim_ by way of an all-rows scan with no residual conditions into Spool 6 (all_amps) (compressed columns allowed), which is duplicated on all AMPs. The size of Spool 6 is estimated with high confidence to be 4,830 rows. The estimated time for this step is 0.01 seconds. 4) We execute the following steps in parallel. 1) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of an all-rows scan, which is joined to aedwprd1.membership_fact_ by way of an all-rows scan with no residual conditions. Spool 5 and aedwprd1.membership_fact_ are joined using a row id join, with a join condition of (" ""(1=1)""). The input table aedwprd1.membership_fact_ will not" be cached in memory. The result goes into Spool 7 (all_amps) (compressed columns allowed), which is built locally on the AMPs. The size of Spool 7 is estimated with low confidence to be 7,149,465 rows. The estimated time for this step is 2.79 seconds. 2) We do an all-AMPs RETRIEVE step from aedwprd1.mbrshp_acct_mth_dim_ by way of an all-rows scan with no residual conditions into Spool 8 (all_amps) (compressed columns allowed), which is duplicated on all AMPs. The size of Spool 8 is estimated with high confidence to be 149,730 rows. The estimated time for this step is 0.10 seconds. 3) We do an all-AMPs RETRIEVE step from aedwprd1.prod_var_ernng_cmpnt_dim_ by way of an all-rows scan with no residual conditions into Spool 9 (all_amps) (compressed columns allowed), which is duplicated on all AMPs. Then we do a SORT to order Spool 9 by row hash. The size of Spool 9 is estimated with high confidence to be 12,754,560 rows. The estimated time for this step is 1.47 seconds. 5) We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of an all-rows scan, which is joined to Spool 7 (Last Use) by way of an all-rows scan. Spool 6 and Spool 7 are joined using a product" join, with a join condition of (""src_sys_key = src_sys_key""). The" result goes into Spool 10 (all_amps) (compressed columns allowed), which is built locally on the AMPs. Then we do a SORT to order Spool 10 by row hash. The size of Spool 10 is estimated with low confidence to be 7,149,465 rows. The estimated time for this step is 1.45 seconds. 6) We execute the following steps in parallel. 1) We do an all-AMPs JOIN step from Spool 9 (Last Use) by way of a RowHash match scan, which is joined to Spool 10 (Last Use) by way of a RowHash match scan. Spool 9 and Spool 10 are joined using a merge join, with a join condition of (" ""prod_var_ec_key = prod_var_ec_key""). The result goes into" Spool 11 (all_amps) (compressed columns allowed), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 11 by row hash. The size of Spool 11 is estimated with low confidence to be 7,149,465 rows. The estimated time for this step is 3.78 seconds. 2) We do an all-AMPs JOIN step from Spool 8 (Last Use) by way of an all-rows scan, which is joined to aedwprd1.chrtfld_dim_ by way of an all-rows scan with a condition of (" ""((aedwprd1.chrtfld_dim_.cf_covg_cd = '100') OR" ((aedwprd1.chrtfld_dim_.cf_covg_cd = '130') OR (aedwprd1.chrtfld_dim_.cf_covg_cd = '140'))) AND (((aedwprd1.chrtfld_dim_.cf_fndg_arr_cd = '10') OR (aedwprd1.chrtfld_dim_.cf_fndg_arr_cd = '50')) AND" (aedwprd1.chrtfld_dim_.cf_bus_unit_cd = '01800'))""). Spool 8" and aedwprd1.chrtfld_dim_ are joined using a product join," with a join condition of (""(1=1)""). The result goes into" Spool 12 (all_amps) (compressed columns allowed), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 12 by row hash. The size of Spool 12 is estimated with high confidence to be 10,072,551 rows. The estimated time for this step is 1.46 seconds. 7) We do an all-AMPs JOIN step from aedwprd1.firm_dim_ by way of a RowHash match scan with no residual conditions, which is joined to Spool 11 (Last Use) by way of a RowHash match scan. aedwprd1.firm_dim_ and Spool 11 are joined using a merge join," with a join condition of (""aedwprd1.firm_dim_.firm_key = firm_key""). " The result goes into Spool 13 (all_amps) (compressed columns allowed), which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 13 by row hash. The size of Spool 13 is estimated with low confidence to be 7,149,465 rows. The estimated time for this step is 1.47 seconds. 8) We do an all-AMPs JOIN step from Spool 12 (Last Use) by way of a RowHash match scan, which is joined to Spool 13 (Last Use) by way of a RowHash match scan. Spool 12 and Spool 13 are joined using a" merge join, with a join condition of (""(cf_key = cf_key) AND"" (dt_dt = acct_mth_dt)""). The result goes into Spool 3 (all_amps)" (compressed columns allowed), which is built locally on the AMPs. The size of Spool 3 is estimated with low confidence to be 7,149,465 rows. The estimated time for this step is 0.60 seconds. 9) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by way of an all-rows scan, and the grouping identifier in field 1. Aggregate Intermediate Results are computed globally, then placed in Spool 14. The size of Spool 14 is estimated with no confidence to be 5,362,099 rows. The estimated time for this step is 4.75 seconds. 10) We do an all-AMPs RETRIEVE step from Spool 14 (Last Use) by way of an all-rows scan into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with no confidence to be 5,362,099 rows. The estimated time for this step is 0.37 seconds. 11) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request. -> The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 17.80 seconds.

Unable to COnfigure Teradata Manager

dsnrhyd — Sat, 18 Apr 2009 15:03:37 GMT

Hi frnds!I have download teradata express verison. Installed done properly.All tools are working expect Teradata Manager.Host name :- 127.0.0.1 or Localhost1.Database set up Failure system name 127.0.0.1super user name = dbc / pass = dbcconsole = emptyuncheck create teradata mngr USerUncheck Migrate TDWM Databse error==========================================================4/19/2009 12:31:22 AM Database Setup started.Logging on to 127.0.0.1 as DBC... *** Logon successfully completed.__________Setting up DBCMNGR__________Create profile pDBSSETUPDBA as password = (expire = 0); *** Query acceptedCreate profile pDBSSETUPDBA as password = (expire = 0); *** Query accepted*** TDWM setup and migration failed. See previous entries in the log (C:\Documents and Settings\saurav1\My Documents\NCR\Teradata Manager\data\TDWMMigration0904190031.log).Logging off... *** You are now logged off from the database.Database Setup terminated.Thanks in Adv

Participate in 3rd Annual Data Miner Survey

Karl Rexer — Thu, 26 Mar 2009 16:38:49 GMT

Please participate in our 3rd Annual Data Miner Survey Survey Link: www.RexerAnalytics.com/Data-Miner-Survey-Intro2.html Access Code: SL4TA Please also help us spread the word to more data miners. This link and access code can be emailed to others.We think that this research will be of interest to all members of this group. A Research Summary Report will be free and available to all. Contact krexer@RexerAnalytics.com if you have any questions about this research, or to request a copy of last year's Summary Report.Thank you!

Teradata DBA position in Ashburn,VA and Atlanta,GA.

Raad — Wed, 25 Mar 2009 18:02:15 GMT

Hi!!I am writing to see if you are currently available for a position in Ashburn,VA. I am looking for a Teradata DBA for a 8+ months contract. If you or anyone you may know of is interested, please email me at your earliest convenience.Location: Ashburn,VADuration: 8+ monthsJob Description:At least 6 years software design/development experience. Teradata DBMS. Data Warehousing. Teradata data modeling Teradata certified master developer with minimum of 2 years experience in reporting database development (star/snow flake schema), adept in use of AJIs. Strong communication skills along with process oriented development backround are a must. Ability to Interact with business users in gathering requirement for business intelligence reporting Design and develop a database schema and surrounding support elements for meeting #1’s output (requirement) Support and enhance #3 for high performance of #2’s output(database) Bachelors degree or higher, preferably in Computer Science or related field.Please send resume with contact details, rate, location, availability status. Sincerely,RaadGlobal Resource Management, Inc. (GRMI)678 935 0437 (Direct)770 729 9222 (Fax)raad@grmi.netwww.grmi.net"GRMI- not just a name...A COMMITMENT TO EXCELLENCE"

SAS/ACCESS to TERADATA

preeti1234 — Fri, 27 Feb 2009 23:45:12 GMT

Hi,I want to know how can we join two tables in two different database in teradata server using PROC SQL.Inside PROC SQL I am connecting to teradata usingconnect to teradata (user ='user pwd = 'xxxxx' server ='SERVER' connection = 'global');I am trying something like thisproc sql;connect to teradata (user ='user pwd = 'xxxxx' server ='SERVER' connection = 'global');execute (create volatile table abc as (select distinct COLUMN1 from DATABASE1.TABLE1 UNIONselect distinct COLUMN2 from DATABASE2.TABLE2)) with data on commit preserve rows)by teradata;execute (commit work) by teradata;quit;Now how do I define two different database in same server in connect to teradata statementI am getting the error:ERROR: Teradata execute: Request aborted by TDWM. Exception criteria exceeded: CPU Time, CPU Disk Ratio.

Reporting Data

navs8603 — Thu, 12 Feb 2009 08:49:33 GMT

reporting

Neeraj shukla — Thu, 12 Feb 2009 04:58:49 GMT

hi........ i am tring to build a commercial report in teradata.can any one help me to tell how to create it .please tell steps in all the steps. or plz send me any tutorial. neeraj shukla

How to calculate a distance between point a to b by using Longitude and Latitude

nguyent02 — Thu, 29 May 2008 17:42:13 GMT

Hi, i need help to use Teradata SQL function to calcuation a distance from point a to point b which are using Longitude and latitude.Please helps...thanks,

date computation from weekno and a column value

sunithapr — Wed, 11 Feb 2009 14:36:32 GMT

Hi,I have a table with the following structureabcwknosun_1mon_2tue_3wed_4thu_5fri_6sat_7I need to select abc for a particular date but don't have column that would specify the date. It just a weekno and split up of days. Based on the column value for sun_1,mon_2,tue_3,wed_4,thu_5,fri_6, sat_7 i need to frmulate the date. For example if i have a value >0 for any of the days then the weekno+the day(sun_1 for ex) will give a date.For example weekno 10000 indicates 1 week of 2009then if Sat_7 has a value >0 the date would be 3-Jan-2009.How do i write a sql to do this? Also i would have tod o this for a 200 million rows so the performance is also a major consideration. Is there any function that could do this???

10065 WSA E HOST UNREACH:

abparker — Thu, 22 Jan 2009 18:14:14 GMT

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!

Can I use CASE in aggregate JOIN INDEX?

chakripn — Wed, 21 Jan 2009 17:33:07 GMT

Hi,I am trying to create AGGREGATE JOIN INDEX with CASE expression in select and group by clause and its throwing an error.what should be the work around for this issues?I am using Teradata 12 version.Thank you,Chakri.

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)

technologies

allpubs — Sun, 23 Nov 2008 18:07:53 GMT

We like to know if there are technologies like those posted at http://morsi.org/projects/atep/? Thanks

cast teradata

lalitha — Wed, 26 Nov 2008 12:15:05 GMT

can anyone help me understand a part of the script i have it is a bteq script and it is executed in unix as a back ground process SELECT a.orig_acct_num,b.curr_mort_trd_cnt delq_mot_trd_cnt, -----------------------------------------------------DELINQUENT TRADE b.optrd_bal tot_opn_tr_balFROM hib534_trig_stg_accounts a, pcdw.cr_rpt_efx bWHERE a.orig_acct_num = b.acct_idAND b.cr_rpt_yymm_val = case when cast((cast(cast('$2' as date)-$1 as date) (format 'MM')) as char(2)) = '01' then cast((cast(cast('$2' as date)-$1 as date) (format 'YYYYMM')) as char(6)) - 89 else cast((cast(cast('$2' as date)-$1 as date) (format 'YYYYMM')) as char(6)) - 1 end;how does this cast function ? i run this script in unix as a background process passing the parameter date everyday. any more details i can give for u r clear understanding plz ask?bye

TERADATA TRAINING SESSION STARTED IN USA THRU PHONE OR PERSONAL..

gopinadh — Sat, 08 Nov 2008 13:43:05 GMT

Hi all,,TERADATA IS VERY HOT IN USA MARKET.TERADATA TRAINING SESSION HAS STARTED IN USA THRU PHONE OR PERSONAL WITH LIVE PRACTICE.If any body intrested ,please drop the mail to : gopi.mainframe@gmail.comThank youG o p i

Enterprise Miner 5.2 "Unable to locate column value in array "

TeraJaguar — Thu, 25 Sep 2008 14:15:28 GMT

I'm running E-Miner 5.2 on a version 12 database and I get the above message when performing a decision tree analysis on a dataset. Has anyone else received this error?

Using aggregate functions with the having clause in a subquery.

NARAYANA — Tue, 08 Jul 2008 15:02:47 GMT

I am trying to execute a query that has several sub-queries embedded in it. The funny thing is, as a stand alone query on it's own, the query works fine. It's very quick and has no problem with the having clause. I was wondering if you have either had this problem using aggregate functions with the having clause in a subquery.

TeradataV2R5 Certification Books for Sale

kumar633@rediffmail.com — Thu, 24 Jul 2008 05:50:36 GMT

Dear,Hope you are in great spirit.I have completed all certifications in Teradata using Coffing Books.Hope you all knew that passing teradata certification exams by reading CoffindDW books are pretty easy.I have below list of booking published by coffindDW.comNR-001 Teradata Basics( Now coffing selling @ $199.00 )NR-002 Teradata Physical Implementation ( Now coffing selling @$199.00 )NR-003 Teradata SQL ( Now coffing selling @$199.00 )NR-004 Teradata Database Administration ( Now coffing selling @$199.00 )NR-005 Teradata Design ( Now coffing selling @$199.00 )NR-006 Teradata Application Development ( Now coffing selling @$199.00)i would like to sell them at a reasonable price.If anyone intrseted you can mail me - kumar633@rediffmail.com ( kumar633 at rediffmail)currently am in bangalore and i can send them in a parcel if anyone stays out of bangalore.ThanksKumarTeradata Certified Master Bangalore INDIA

Standardize

ScottEE — Tue, 20 May 2008 02:48:25 GMT

Hi, Has anyone discovered a function in Teradata that is Equivalent for MS Excel standardize function in Teradata? ie : STANDARDIZE See AlsoReturns a normalized value from a distribution characterized by mean and standard_dev.SyntaxSTANDARDIZE(x,mean,standard_dev)X is the value you want to normalize.Mean is the arithmetic mean of the distribution.Standard_dev is the standard deviation of the distribution.RemarksIf standard_dev ≤ 0, STANDARDIZE returns the #NUM! error value. The equation for the normalized value is:

Names of Journal/magazine/conferences to publish a paper

GopiKrishnan S — 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

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