#DateForumTypeThreadPost
400301 Oct 2015 @ 07:42 PDTDatabaseReplyhow to use top and sample in a select statement with multiple joins@kirthi: When you look at Explain you will notice that both SAMPLE and TOP are processed after the full result set has been created, in fact this will be a bit slower than running the SELECT. The ...
400229 Sep 2015 @ 06:43 PDTToolsReplyBTEQ DELETE STATEMENT Successfull - nothing deleted in tableYou logged on using an ANSI session (your system default) and didn't COMMIT before the LOGOFF -> DELETE is rolled back. Your connection from SQL Assistant is probably explicitly set to a Te...
400129 Sep 2015 @ 04:28 PDTDatabaseReplyPrioritize SELECT based on the condition select * from tab qualify rank() over (partition by col1 -- probably needed order by case col2 when 'BC' then 1 when ...
400027 Sep 2015 @ 07:04 PDTDatabaseReplySHOW Statistics - Meaning of AvgRowsPerBlock Hi Roland, I noticed exactly the same: AvgRowSize and AvgBlockSize are both correct (vertified by Ferret and COLLECT DEMOGRAPHICS) and AvgRowsPerBlock returns values much higher or lowe...
399927 Sep 2015 @ 05:57 PDTDatabaseReplySliding Window Merge Join QuestionHi Roland, a Rowkey Based Merge Join is the best you can get on a partitioned table, exactlye the same as a PI-Join on non-partitioned tables. Did you double check if the partitioning column is a...
399825 Sep 2015 @ 03:24 PDTDatabaseReplyLOBs are not allowed to be hashed issue in Recursive queryHi John, Do you really need a CLOB as result, what's the data type of full_pr_num and the maximum result size? 
399724 Sep 2015 @ 07:39 PDTDatabaseReplyWhen I create a table with partition and compress reduce itSector size is 512 byte and block in Teradata might be be between 1 and 255 (or bigger depending on the release) sectors. One or three AMPs, any change in the range of a few KB is insignifica...
399624 Sep 2015 @ 06:12 PDTDatabaseReplyWhen I create a table with partition and compress reduce itYour example #1 shows a size reduced by 1536 bytes and for #2 both sizes match exactly, this is probably due to one differently sized block on 3 AMPs (and there's hardly more than one block per...
399524 Sep 2015 @ 05:58 PDTToolsReplyAM/PM in TIMESTAMP00:xx AM doesn't exist, it's 12:xx AM. https://en.wikipedia.org/wiki/12-hour_clock That's why there's 24 hour format :-)
399424 Sep 2015 @ 01:43 PDTDatabaseReplyWhen I create a table with partition and compress reduce itCan you show the actual DDL?
399324 Sep 2015 @ 01:29 PDTDatabaseReplyRow not returned when percentile_cont() only sees null values, even when other columns would have dataHi Jerry, Explain reveals that NULLs are filtered before calculation. This seems to be a bug, you might open an incdent with Teradata's Customer Service.   For workaround you might the...
399223 Sep 2015 @ 11:33 PDTDatabaseReplyUse aggregate function without group by caluse?Hi Harry, what's your Teradata release? "50th percentile" is the median, TD14.10 added PERCENTILE_CONT, PERCENTILE_DISC and MEDIAN as new OLAP function (in fact aggregate functions)...
399123 Sep 2015 @ 04:02 PDTDatabaseReplyIssue while converting float to time and inserting to tableThere's no implicit cast for TIME and TIMESTAMP, so you must do it explicitly: SELECT 'aaa'||cast(A.RUNTIME as char(8))
399023 Sep 2015 @ 02:10 PDTDatabaseReplyIssue while converting float to time and inserting to tableOf course TIME - 3 is wrong, should be TIME - 30000 instead.     You can't compare the TIME function (FLOAT) with the TIME dataype. It's either the corrected version of my que...
398923 Sep 2015 @ 12:23 PDTGeneralReplyRegular Expression guide for regular expression functionsThere's no Teradata-specific documentation on regex, but there are many books and online resources available, simply google for "regular expression". Caution, there are different dia...
398823 Sep 2015 @ 12:21 PDTDatabaseReplySUM OVER RESET on the previous row's SUM OVER resultI don't think you can do this with a simple OLAP function, you need recursion for it (your RESET WHEN already tries a recursive definition, SUM(Cum_Total), which is no valid syntax and seems to...
398723 Sep 2015 @ 12:14 PDTDatabaseReplyLooking for changes from previous rowsYou need the LAG function which is not implemented in Teradata, but easy to rewrite: SELECT Account, time_stamp, Location, MIN(location) OVER (PARTITION BY Accou...
398623 Sep 2015 @ 12:10 PDTDatabaseReplyIssue while converting float to time and inserting to tableTHETIME is defined as a FLOAT (due to historical reasons), same for the built-in TIME function  and you can't mix FLOAT and TIME datatypes. This should work: select cast(trim(THETI...
398521 Sep 2015 @ 07:14 PDTDatabaseReplyNumeric overflow for decimal (38,4)You get the number of digits before the decimal point easily using LOG instead of a CAST to VarChar + INDEX (which will not work correctly for negative values without adding ABS): ceiling(log(ab...
398421 Sep 2015 @ 04:03 PDTDatabaseReplyNumeric overflow for decimal (38,4)Please post a new question as a new topic.   There's a NEXT_DAY function, which returns the "next" weekday, e.g. for weeks from monday to sunday: next_day(dat -14, 'mon&...
398320 Sep 2015 @ 11:49 PDTDatabaseReplyNumeric overflow for decimal (38,4)sel 3571893687 * 3566028423 * 3558464912 * 13056923  results in 591,816,208,590,977,000,480,673,972,272,291,376 which is 36 digits as integer part, clearly above the 34 digits of a DEC(38,4...
398220 Sep 2015 @ 11:38 PDTDatabaseReplyTable StatsHi Shivkumar, of course it's not working anymore, since TD14 there's a new syntax :-) SHOW STATS VALUES COLUMN (test1) ON test;
398120 Sep 2015 @ 10:26 PDTDatabaseReplyCount rows once where value appeatrs in one or more columnsYou need an outer join using BETWEEN:   select c.id, c.EventFrom, c.EventTo, count(e.EventDate) from customers as c left join Events as e on e.ID = c.ID and e.EventDate between c.E...
398017 Sep 2015 @ 11:00 PDTDatabaseReplyData Block sizeNewer versions support block sizes up to 1MB
397917 Sep 2015 @ 09:30 PDTDatabaseReplyCase StatementOk, you want a kind of best match per application: Approved -> Declined -> In Progress  You need nested aggregates: select case minStatus when 1 then 'Approved' ...

Pages