#DateForumTypeThreadPost
100306 Jun 2011 @ 02:43 PDTDatabaseReplyNeed help optimizingWho wrote that query? This is one of the most nonsens CASEs if ever seen :-) It's just two simple age group calculations: CASE WHEN T1."AGE"
100206 Jun 2011 @ 02:25 PDTUDAReplyErr0r 5781- cannot collect stats on more than 32 groups of non-indexed columns teradataAccording to the error message there seem to be a lot of stats on that table already. Be careful, DIAGNOSTIC HELPSTATS might suggest too many stats which are not really useful. And a re-colle...
100102 Jun 2011 @ 10:31 PDTDatabaseReplyHow to sleep/pause in a Stored Procedure?It should be easy to write a C-UDF which only executes a sleep(x). But could you elaborate on what you actually try to accomplish? "a loop which checks table contents" sounds like you're waitin...
100002 Jun 2011 @ 08:57 PDTDatabaseReplyIssue while adding identity to a column in Alter tableHi Chirag, the only solution is probably: - determine the maximum value of TEST_ID in your current table - create a copy of the table with TEST_ID INTEGER NOT NULL GENERATED BY DEFAULT AS ...
99902 Jun 2011 @ 08:47 PDTDatabaseReplyObject access separationOf course, you do it exactly as your proposed: Only grant the CREATE, but not the DROP right. The creator of an object automatically gets all access rights. Dieter
99831 May 2011 @ 01:32 PDTToolsReplyError [3105] Dispatcher internal error: Please do not re-submit request. in .Net Client for TeradataI don't know what's causing the problem, but you should immediately open an incident with your Teradata support. Dieter
99731 May 2011 @ 01:08 PDTDatabaseReplyCreating a Group IDHi Marty, if the values in B are ascending parallel to A (as your sample data) it's simple: SELECT a,b,MAX(b) OVER (ORDER BY a ROWS UNBOUNDED PRECEDING) FROM tab Else in TD13: SELECT a,b...
99627 May 2011 @ 12:46 PDTDatabaseReplyFULL OUTER JOIN syntax and problems with correct outputFor Inner Joins there's no difference where you put the search conditions, but for Outer Joins there is. According to Standard SQL the order of evaluation is: First there's the JOIN based on the O...
99523 May 2011 @ 06:59 PDTDatabaseReplyTotal Sum of all columnsWITH is only supported in BTEQ, but extended grouping can be used instead. Just modify your existing GROUP BY and ORDER BY: GROUP BY GROUPING SETS((grp,name), ()) ORDER BY GROUPING(grp), grp ...
99418 May 2011 @ 12:44 PDTDatabaseReplyPrepare statement in a stored procedureUnicode shouldn't be any problem, could you be more specific about "an issue" or share the full source code? In Manika's sql_str the single quotes should be doubled: 'SELECT customer_id from te...
99318 May 2011 @ 12:38 PDTDatabaseReplySQL Color resultsOf course you could use use a CASE to add something like an HTML-tag to a returned value, but the color coding must be done in the client. E.g. Excel is quite powerful for formatting result set. ...
99218 May 2011 @ 12:34 PDTDatabaseReplyPrimary Index on VARCHAR columnThere should be no difference in CHAR vs. VARCHAR for PI, both hash the same, as trailing blanks are ignored during hashing. The values stored within a statistic are always fixed length, so there...
99117 May 2011 @ 02:53 PDTDatabaseReplyBIGINT QUESTIONNone of the bigint columns causes that error, but you try to fit 163,830,427,146,319,210 into an integer :-) QueryID should be DEC(18,0) Dieter
99016 May 2011 @ 08:58 PDTDatabaseReplyInserting values to a created table - syntax helpThis is not due to the insert, the select is failing. You just have to name the Derived Table/Inline view, only Oracle allows to omit it :-) Dieter
98913 May 2011 @ 10:40 PDTDatabaseReplyEmpty rows...In TD13.10 there's a new EXPAND ON query clause which is exactly what you need. Whithout this feature is a typical solution based on a LEFT OUTER JOIN to a calendar table. Dieter
98813 May 2011 @ 10:37 PDTDatabaseReplypivoting data from an existing table data please assistSELECT person, CASE WHEN test1 >= test2 AND test1 >= test3 THEN 'test1' WHEN test2 >= test3 THEN 'test2' ELSE 'test3' END, CASE WHEN test1 >= test2 AND test1 >= tes...
98713 May 2011 @ 10:32 PDTDatabaseReplyRUN SQL QUERYHi Saroop, you need to name the Derived Table/Inline View, only Oracle allows to omit the name in some cases :-) AND bnft_typ_cd = 'FMIA') AS x Dieter
98628 Apr 2011 @ 07:05 PDTDatabaseReplyCollect Stats - How Performance gets improved?You probably didn't work with Teradata before. Of course there's an overhead for joining on multiple columns or large varchars, but it's low compared to Oracle et al. In Teradata you don't have ...
98528 Apr 2011 @ 03:14 PDTGeneralReplyNeed input : How to avoid dirty read without waiting for the table to be refreshedIf locking the table is not acceptable because it's delaying too much, then mnylin's suggestion using view switching is the way to go: Create a copy of the target table once. Now you got mytab#...
98427 Apr 2011 @ 03:33 PDTDatabaseReplyRegular Expressions?You could wrap a C-grep into a UDF, but don't expect high performance. Dieter
98327 Apr 2011 @ 03:31 PDTDatabaseReplyCollect Stats - How Performance gets improved?Hi Sanjeev, your update query doesn't match the table definitions, there's no cease_time_key and entry_time_key. Your update will not work as there will be a huge intermediate spool because the...
98227 Apr 2011 @ 02:59 PDTDatabaseReplyCase When Statement in TeradataIf this is used to normalize a repeating group you can replace the UNIONs with a single CROSS JOIN to a table with 7 rows in it and then use a CASE like this: SELECT FCC.FEC_PROC AS FEC_PROC ,...
98127 Apr 2011 @ 02:50 PDTDatabaseReplyavoid exclusive lock during inserts on a tableThere's no way to downgrade a WRITE lock. If you want parallel execution of your queries you should put multiple SELECTs into a MultiStatement Request. Dieter
98027 Apr 2011 @ 02:41 PDTGeneralReplyNeed input : How to avoid dirty read without waiting for the table to be refreshedIn your first example there are 103 requests sent across the network, most of the runtime is just waiting for the result of the previous request before sending the next. Whereas the MSR is just a ...
97927 Apr 2011 @ 02:10 PDTDatabaseReplyROW_NUMBER performance on large datasetsThere's no faster way to achieve this. Is there a large number of additional columns, i.e. a large row size? OLAP functions keep all the data twice in spool. What's the datatype of the PRI_INDE...

Pages