278 | 17 Jan 2008 @ 05:19 PST | Tools | Reply | Viewing Requesttext of a Trigger | Hi Viswanath,the RequestText column in dbc.tvm is a VARCHAR(12500). If the source code doesn't fit into that column then RequestTxtOverflow is set to 'R' and one or more rows are inserted into dbc.... |
277 | 16 Jan 2008 @ 05:21 PST | UDA | Reply | Aggregation | Hi Marc,strange update but there it is:UPDATE marc FROM (SELECT SUM(m1) + SUM(m2) AS x FROM marc WHERE m1 = 1) dtSET m2 = dt.x WHERE m1 = 1;Dieter |
276 | 16 Jan 2008 @ 05:14 PST | Database | Reply | Dropping Statistics on a table in teradata | Hi Vineet,all manuals are available at www.info.teradata.comI'd recommend to download the documentation CD, so you can search across all manuals.Dieter |
275 | 16 Jan 2008 @ 02:05 PST | Database | Reply | updating date value | Hi Rock,update tabset col = add_months(col, 12)where col between date '2007-03-01' and date '2007-03-31'or where extract(year from col) = 2007 and extract(month from col) = 3orwhere col / 100 = 10... |
274 | 15 Jan 2008 @ 03:21 PST | Database | Reply | How to find some of the table properties in Datadictionary? | Hi Manu,those options are not stored anywhere within the data dictionary, it's within the table header.Using SQL the only way to get that info is a SHOW TABLE.Dieter |
273 | 15 Jan 2008 @ 03:19 PST | Database | Reply | Converting number of seconds to Minutes and Seconds | Hi Fabio,this will probably result in a "7453: Interval field overflow".If the average phone call is not more than 9999 (a bit less than 7 days): (SUM(Call_Length)/COUNT(Call_ID)) * INTERVAL '000... |
272 | 15 Jan 2008 @ 03:08 PST | Database | Reply | DELETE ALL vs DELETE | Hi Andrew,which training material you're referring to?It's covered correctly within the "Teradata SQL" and the "Teradata Application Design and Development" training.And those "previous" versions m... |
271 | 27 Dec 2007 @ 11:06 PST | Database | Reply | Table Copy | "CREATE new_table AS existing_table" preserves all column attributes and indexes (just Triggers and Foreign Keys are removed), whereas "CREATE new_table AS (SELECT * FROM existing_table)" resets ev... |
270 | 27 Dec 2007 @ 04:51 PST | Database | Reply | Escape Characters, Operators & Date Conversions | You place the escape character before the searched char.Any fname with a "%":fname like '%\%%' escape '\'A tablename with two "_":select * from dbc.tableswhere trim(tablename) like '%\_%\_%' escape... |
269 | 27 Dec 2007 @ 04:47 PST | Database | Reply | Teradata IDENTITY columns | Hi Andrew,of course this will work..For any unused column it's DEFAULT or NULL will be used, a NOT NULL column will result in an error message,Dieter |
268 | 27 Dec 2007 @ 04:44 PST | Database | Reply | DELETE ALL vs DELETE | There's definitely *no* difference between DELETE and DELETE ALL.A FastPath Delete without transient journal just requires:- a target table without Permanent Journal, Delete Trigger, Join Index- th... |
267 | 18 Dec 2007 @ 10:19 PST | UDA | Reply | Tuning Teradata SQL | Hi Rajiv,without further information (DDL + row counts + query + explain) nobody can provide reliable help.Is the query running for hours or just the Create Table As?Maybe skewed table and lots of ... |
266 | 18 Dec 2007 @ 10:16 PST | UDA | Reply | FAST PATH INSERT SELECT | If the target table is emtpy at the begin of the transaction, then that Insert/Select doesn't need a Transient Journal, because in case of a Rollback it's just emptied again.This is called a "Fast ... |
265 | 17 Dec 2007 @ 04:01 PST | Database | Reply | Escape Characters, Operators & Date Conversions | 1. Mapping of Text data type for TeradataLong Varchar *** This is equal to VarChar(64000), if there are more chars better use CLOB2. Is varchar in Teradata Unicode compliant? If not, is there any ... |
264 | 17 Dec 2007 @ 03:26 PST | Database | Reply | Reg: Partitioned Primary Index | Multiple partitions are to be probed only when the partitioning expression is not based on PI columns.Only for that case it's not allowed to create a UPI:the row has to be inserted into one partiti... |
263 | 12 Dec 2007 @ 12:46 PST | Tools | Reply | Number of lines limit in a Macro | Hi Nancy,where did you hear about that limit?The performance of a macro is the same as the performance of the base statements.The only limit should be the 1MB source code limit.Dieter |
262 | 12 Dec 2007 @ 12:40 PST | Analytics | Reply | Analyze explain plan | Hi newtera,this isn't an explain of an insert into a temp table, but a "replace macro".But maybe that insert is part of that macro, so you better "explain exec AB28880.sample_MACRO;"The first inser... |
261 | 29 Nov 2007 @ 02:07 PST | Tools | Reply | Trying to understand the Secondary Index | Any SI-subtable is just a table.CREATE UNIQUE INDEX USI(id int) ON tab;creates a table exactly likeCREATE TABLE USI( id int, overhead byte(7), -- might be > 7 baseROWID byte(8) -- or byte(10)... |
260 | 29 Nov 2007 @ 01:56 PST | Tools | Reply | INMOD/OUTMOD | Hi Goutham,if those examples are too complex, you better shouldn't try to write an Inmod.Dieter |
259 | 29 Nov 2007 @ 01:52 PST | UDA | Reply | Date range | Hi Nitin,"it's not working for me" is not helpfull at all, at least provide the error code/message.It's working for me :-)Dieter |
258 | 23 Nov 2007 @ 05:15 PST | Database | Reply | Timestamp calculation from Oracle to Teradata | Hi Joe,this substracts 43.2 seconds from that timestamp-string:1 = 1 day -> 0.0005 = 1/2000 of a day = 0.0005 * 86400This calculation works for < 10000 days:SELECT '11/23/2007 11:11:55' AS ... |
257 | 22 Nov 2007 @ 04:04 PST | Tools | Reply | BTEQ | Hi Anil,BTEQ might be used for import/export (but not for a large number of rows) on a Teradata system.So you can't logon to Oracle et al to export to a flat file.You might use the OLE-DB Access Mo... |
256 | 22 Nov 2007 @ 03:48 PST | Database | Reply | Gaps in values for identity columns | Hi Rajan,identity values might be used for unique values without UNIQUE constraint (if defined using NO CYCLE and ALWAYS) but might have gaps and don't reflect the chronological order of inserted r... |
255 | 21 Nov 2007 @ 07:42 PST | Tools | Reply | ET Record Number | If you started that MLoad you can access that ET.If you didn't start it, then ask a dba to grant you access rights.Dieter |
254 | 21 Nov 2007 @ 07:39 PST | Database | Reply | Please help with Drop Database problem | Only empty databases might be dropped, so you have to drop that journal table:modify database finance asdrop default journal table;Dieter |