#DateForumTypeThreadPost
237801 Nov 2013 @ 09:49 PDTDatabaseReplyHow to simplify query that runs out of spool space?A m:n-join might result in a huge intermediate spool causing the no more spool error. Without knowledge about your data model it's impossible to tell exactly how to rewrite your query, but man...
237701 Nov 2013 @ 09:36 PDTDatabaseReplyhow to get maxmum/minimum number between multiple columns in a rowYou didn't specify your TD release. In TD14 there's a GREATEST/LEAST, but it's only defined up to 10 columns, so you need to nest it: GREATEST(GREATEST(col11,col2,col3,col4,col5,col...
237601 Nov 2013 @ 09:25 PDTDatabaseReply3806: Table/view/trigger name is ambiguousYes and Yes :-) Dieter 
237501 Nov 2013 @ 09:20 PDTDatabaseReplyregexp_replace woesThe documentation on the manuals seems to be buggy (or the implementation doesn't match it). By default REGEXP_REPLACE replaces only the first occurance, you have to add optional parameters: ...
237401 Nov 2013 @ 08:06 PDTDatabaseReply3806: Table/view/trigger name is ambiguousThere can be more than one default database :-) When you use a qualified tablename like DB2.T2, DB2 will be added to the "search path" and now the parser finds T1 in both DB1 and DB...
237301 Nov 2013 @ 06:28 PDTToolsReplyMultiload with date in where conditionYou need to compare the CHAR field1 to another CHAR.  If Field1 is formatted like 'yyyy-mm-dd hh:mi:ss, then this should work: APPLY LabelA WHERE FIELD1 >= '2013-01-01 00:00:00...
237201 Nov 2013 @ 05:47 PDTGeneralReplyGeneral feedback, Regarding publishing of the first post: When it will be published depends on when a moderator logged in and marked your post as "no spam". Newly registered users need to be approved as 9...
237130 Oct 2013 @ 12:26 PDTDatabaseReplyNeed to Remove Duplicates from a datasetHi Rakesh, it's ROW_NUMBER not ROW_NUM.   Dieter
237029 Oct 2013 @ 11:08 PDTDatabaseReplyUPDATE Teradata Field with NULL valueInstead of a parameter you might try a literal NULL: STR_STRING = " Update " & TBLNAME & " set Expiration_Date = NULL;" Dieter  
236928 Oct 2013 @ 04:06 PDTDatabaseReplyPE and AMP setupThe number of AMPs in the system is easy to get: SELECT HASHAMP() + 1; When you got access to dbc.Resusage you will find the number of AMPs for each node using: SELECT DISTINCT NodeID, Vproc...
236828 Oct 2013 @ 03:10 PDTDatabaseReplyUnion alternative in TeradataThis is hardly readable code, you should add it using the "code snippet" icon. After a quick format you seem to need two levels of aggregation, which is easily done using GROUPING SETS: ...
236728 Oct 2013 @ 03:09 PDTDatabaseReplyqualify rank() over (partition.....questionSorry, but this is not readable/understandable. Dieter
236628 Oct 2013 @ 11:59 PDTDatabaseReplyNeed to Remove Duplicates from a datasetHi Rakesh, you can simply add another ORed condition to check for change in another column.   Dieter
236528 Oct 2013 @ 11:49 PDTDatabaseReplyCompiling Stored Procedures with No SPL (No Stored Procedure Language)#1: there's no way to recompile an SP without source code. #2: this is an option during CREATE, in BTEQ .COMPILE FILE .. WITH NOSPL, in ODBC it's ProcedureWithSPLSource, etc.   ...
236426 Oct 2013 @ 10:58 PDTDatabaseReplyProblem with CASE WHEN statement What is [Posting text removed based on formal request from Owner of the associated Intelectual Property.]?   If it's created based on the query of your previous post (http://for...
236326 Oct 2013 @ 09:37 PDTDatabaseReplyCreate Table Failed [3076] Problem between the ")" and the AS.Posting text removed based on formal request from Owner of the associated Intelectual Property.
236226 Oct 2013 @ 09:33 PDTDatabaseReplyCount Days in a date fieldEXTRACT(DAY) extract the day part of a date, e.g. for 2013-10-26 it's 26. But i doubt this is what you need, what business question would be based on that? Could you clarify what you actually...
236126 Oct 2013 @ 05:57 PDTDatabaseReplyDELETE statement taking 2 hours.20 million rows is not a large number, could you post the explain?   Dieter
236026 Oct 2013 @ 05:54 PDTDatabaseReplyHow to make substring with the second or third occurrence of a charaterTo split a string there's also STRTOK in TD14: STRTOK(EQN,'-',3) This is also available as UDF pre-TD14 using the Ebay function: http://developer.teradata.com/blog/madmac/2010/03/a...
235926 Oct 2013 @ 05:41 PDTDatabaseReplyPeformance tuning Query taking more time Hi Vivek, what's the PI/partitioning of both tables? Are there any Secondary Indexes on TIC? And you're using the wrong datatype for column B: according to the explain it's a (VAR)CH...
235826 Oct 2013 @ 04:59 PDTDatabaseReplyTranspose rows to columnsHi Khurram, you'll find all functions for a release in the SQL Functions and Operators manual.   Dieter
235726 Oct 2013 @ 04:40 PDTDatabaseReplyTranspose rows to columnsBtw, in TD14.10 there's a new table UDF TD_UNPIVOT to transform rows to columns. I didn't test it yet, but i assume it's the fastest way.   Dieter
235625 Oct 2013 @ 12:18 PDTDatabaseReplyNo More Spool SpaceHi Jana, when you read the explain you will see there's a RETRIEVE step including "redistributed by hash code of PI": The spool will have the target's primary index, i.e. all ro...
235525 Oct 2013 @ 06:56 PDTDatabaseReplyTranspose rows to columnsHi Sarah, of course this will result in a product join, that's what it should do :-) There are not many cases where a cross join is actually useful or needed, but this is one.   Dieter
235425 Oct 2013 @ 12:59 PDTDatabaseReplyNeed to Remove Duplicates from a datasetAssuming this should be based on update_date simply add QUALIFY COALESCE( criteriaMIN(status) OVER (PARTITION BY id ORDER BY update_date R...

Pages