#DateForumTypeThreadPost
27817 Jan 2008 @ 05:19 PSTToolsReplyViewing Requesttext of a TriggerHi 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....
27716 Jan 2008 @ 05:21 PSTUDAReplyAggregationHi 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
27616 Jan 2008 @ 05:14 PSTDatabaseReplyDropping Statistics on a table in teradataHi 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
27516 Jan 2008 @ 02:05 PSTDatabaseReplyupdating date valueHi 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...
27415 Jan 2008 @ 03:21 PSTDatabaseReplyHow 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
27315 Jan 2008 @ 03:19 PSTDatabaseReplyConverting number of seconds to Minutes and SecondsHi 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...
27215 Jan 2008 @ 03:08 PSTDatabaseReplyDELETE ALL vs DELETEHi 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...
27127 Dec 2007 @ 11:06 PSTDatabaseReplyTable 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...
27027 Dec 2007 @ 04:51 PSTDatabaseReplyEscape Characters, Operators & Date ConversionsYou 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...
26927 Dec 2007 @ 04:47 PSTDatabaseReplyTeradata IDENTITY columnsHi 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
26827 Dec 2007 @ 04:44 PSTDatabaseReplyDELETE ALL vs DELETEThere'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...
26718 Dec 2007 @ 10:19 PSTUDAReplyTuning Teradata SQLHi 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 ...
26618 Dec 2007 @ 10:16 PSTUDAReplyFAST PATH INSERT SELECTIf 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 ...
26517 Dec 2007 @ 04:01 PSTDatabaseReplyEscape Characters, Operators & Date Conversions1. 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 ...
26417 Dec 2007 @ 03:26 PSTDatabaseReplyReg: Partitioned Primary IndexMultiple 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...
26312 Dec 2007 @ 12:46 PSTToolsReplyNumber of lines limit in a MacroHi 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
26212 Dec 2007 @ 12:40 PSTAnalyticsReplyAnalyze explain planHi 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...
26129 Nov 2007 @ 02:07 PSTToolsReplyTrying to understand the Secondary IndexAny 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)...
26029 Nov 2007 @ 01:56 PSTToolsReplyINMOD/OUTMODHi Goutham,if those examples are too complex, you better shouldn't try to write an Inmod.Dieter
25929 Nov 2007 @ 01:52 PSTUDAReplyDate rangeHi 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
25823 Nov 2007 @ 05:15 PSTDatabaseReplyTimestamp calculation from Oracle to TeradataHi 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 ...
25722 Nov 2007 @ 04:04 PSTToolsReplyBTEQHi 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...
25622 Nov 2007 @ 03:48 PSTDatabaseReplyGaps in values for identity columnsHi 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...
25521 Nov 2007 @ 07:42 PSTToolsReplyET Record NumberIf 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
25421 Nov 2007 @ 07:39 PSTDatabaseReplyPlease help with Drop Database problemOnly empty databases might be dropped, so you have to drop that journal table:modify database finance asdrop default journal table;Dieter

Pages