4003 | 01 Oct 2015 @ 07:42 PDT | Database | Reply | how 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 ... |
4002 | 29 Sep 2015 @ 06:43 PDT | Tools | Reply | BTEQ DELETE STATEMENT Successfull - nothing deleted in table | You 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... |
4001 | 29 Sep 2015 @ 04:28 PDT | Database | Reply | Prioritize 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 ... |
4000 | 27 Sep 2015 @ 07:04 PDT | Database | Reply | SHOW 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... |
3999 | 27 Sep 2015 @ 05:57 PDT | Database | Reply | Sliding Window Merge Join Question | Hi 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... |
3998 | 25 Sep 2015 @ 03:24 PDT | Database | Reply | LOBs are not allowed to be hashed issue in Recursive query | Hi John,
Do you really need a CLOB as result, what's the data type of full_pr_num and the maximum result size?
|
3997 | 24 Sep 2015 @ 07:39 PDT | Database | Reply | When I create a table with partition and compress reduce it | Sector 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... |
3996 | 24 Sep 2015 @ 06:12 PDT | Database | Reply | When I create a table with partition and compress reduce it | Your 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... |
3995 | 24 Sep 2015 @ 05:58 PDT | Tools | Reply | AM/PM in TIMESTAMP | 00: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 :-)
|
3994 | 24 Sep 2015 @ 01:43 PDT | Database | Reply | When I create a table with partition and compress reduce it | Can you show the actual DDL?
|
3993 | 24 Sep 2015 @ 01:29 PDT | Database | Reply | Row not returned when percentile_cont() only sees null values, even when other columns would have data | Hi 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... |
3992 | 23 Sep 2015 @ 11:33 PDT | Database | Reply | Use 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)... |
3991 | 23 Sep 2015 @ 04:02 PDT | Database | Reply | Issue while converting float to time and inserting to table | There's no implicit cast for TIME and TIMESTAMP, so you must do it explicitly:
SELECT 'aaa'||cast(A.RUNTIME as char(8))
|
3990 | 23 Sep 2015 @ 02:10 PDT | Database | Reply | Issue while converting float to time and inserting to table | Of 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... |
3989 | 23 Sep 2015 @ 12:23 PDT | General | Reply | Regular Expression guide for regular expression functions | There'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... |
3988 | 23 Sep 2015 @ 12:21 PDT | Database | Reply | SUM OVER RESET on the previous row's SUM OVER result | I 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... |
3987 | 23 Sep 2015 @ 12:14 PDT | Database | Reply | Looking for changes from previous rows | You 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... |
3986 | 23 Sep 2015 @ 12:10 PDT | Database | Reply | Issue while converting float to time and inserting to table | THETIME 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... |
3985 | 21 Sep 2015 @ 07:14 PDT | Database | Reply | Numeric 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... |
3984 | 21 Sep 2015 @ 04:03 PDT | Database | Reply | Numeric 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&... |
3983 | 20 Sep 2015 @ 11:49 PDT | Database | Reply | Numeric 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... |
3982 | 20 Sep 2015 @ 11:38 PDT | Database | Reply | Table Stats | Hi Shivkumar,
of course it's not working anymore, since TD14 there's a new syntax :-)
SHOW STATS VALUES COLUMN (test1) ON test;
|
3981 | 20 Sep 2015 @ 10:26 PDT | Database | Reply | Count rows once where value appeatrs in one or more columns | You 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... |
3980 | 17 Sep 2015 @ 11:00 PDT | Database | Reply | Data Block size | Newer versions support block sizes up to 1MB
|
3979 | 17 Sep 2015 @ 09:30 PDT | Database | Reply | Case Statement | Ok, you want a kind of best match per application: Approved -> Declined -> In Progress
You need nested aggregates:
select
case minStatus
when 1 then 'Approved'
... |