#DateForumTypeThreadPost
32814 May 2008 @ 02:27 PDTDatabaseReplyTeradata QueryHi Monika,SELECT emp_id, enter_time, out_time, MIN(enter_time) OVER (PARTITION BY emp_id) AS first_entered, MAX(out_time) OVER (PARTITION BY emp_id) AS Last_out_time FROM tabDieter
32709 May 2008 @ 07:54 PDTToolsReplyHow to make use of ARCMAIN utility to take DB backup.There's a manual on ARC:Teradata Archive/Recovery Utility ReferenceDieter
32609 May 2008 @ 07:52 PDTToolsReplyTransient JournalIt will not help you, if you repeat your request for replies, the answer is still the same: You can't switch off the TS on a database level, because of possible rollbacks.Only Volatile and Global T...
32509 May 2008 @ 07:47 PDTToolsReplySHOW command shows incomplete definitionHi,if you do a "show view foo" it's the full source code (unless you run a very old Teradata version)´.Only a "SHOW select ..." has limited size, but please, don't ask me why :-)Dieter
32409 May 2008 @ 07:23 PDTUDAReplyConversion of SQL server query to teradata queryHi Rahul,ISNULL is proprietary, replace it with COALESCE, which is also supported by MS SQL Server. There are some minor differences, but you don't have to care about it in that case:COALESCE(ACC_D...
32309 May 2008 @ 07:04 PDTDatabaseReplyconvert varchar to dateHi Mohan,did you submit that query using SQL Assistant?This is using the windows locale definition of a date, you have to modify that using:Tools - Options - General: Display dates in this formatIf...
32208 May 2008 @ 10:53 PDTAnalyticsReplyWhy subqueries in case are not working in teradata?Hi Jack,Teradata supports Scalar Subqueries only in some special cases within WHERE/HAVING.You'll have to rewrite it to (Outer) Joins.Dieter
32108 May 2008 @ 10:51 PDTAnalyticsReplyequivalent of rownum in tdHi Jack,it's ROW_NUMBER() OVER (ORDER BY ...)Btw, Oracle also supports that RW_NUMBER.Dieter
32008 May 2008 @ 10:49 PDTDatabaseReplyUrgentThere are lots of solutions, i'lll show just two of them.You'll have to pick one appropriate for your problem.DieterDROP TABLE dropme;CREATE TABLE dropme( City_Id INT ,From_dt DATE ,To_dt DATE ,Lo...
31908 Apr 2008 @ 11:28 PDTDatabaseReplyWhat is the use of Partition By function while inserting data into table.Hi Jang,Over( Partition by col1, order by col1) will create a kind of randomly assigned sequence number for each col1 value.Simply repeatedly submit the select part of that query and see how the nu...
31808 Apr 2008 @ 11:24 PDTDatabaseReplyOrdering of Columns in Primary IndexThe data distribution/hashing algorithm is insensitive to the order of columns, so Performance will not change, because those joins are still AMP-local:select hashrow('a',1), hashrow(1,'a'); *** Qu...
31705 Apr 2008 @ 09:46 PDTDatabaseReplyWhat is the use of Partition By function while inserting data into table.Hi Jang,this is a totally stupid way to create a kind of random, but sequential value :-)Stupid, because all the rows will probably be processed by a single AMP:ORDER BY 1 is *not* ordering by colu...
31605 Apr 2008 @ 09:32 PDTDatabaseReplySuspends the session for a given period of timeHi Monika,AFAIK that feature is available only in BTEQ: .HANG n;where n is the number of seconds.Dieter
31505 Apr 2008 @ 09:24 PDTUDAReplywhy Stored Procedure saved in Permanent Space ? Why not Macro?Hi Ravindra,a macro is just source code like a view, whereas a SP is rewritten by the PE as a kind of embedded SQL program in C and then compiled to a Shared Object (Unix) or DLL (Windows) with a s...
31405 Apr 2008 @ 08:39 PDTUDAReplyDifference between Secondary Index and Hash IndexHi Ravindra,check the Database Design manual for all the differences/similarities.Hash Indexes might be more efficient than NUSIs if the number of rows per value is low, because access might be sin...
31305 Apr 2008 @ 08:33 PDTUDAReplyQuery select..where..in on a partitioned table: partitions are not usedIf you want to check if data exists, then better use a EXISTS instead of SAMPLE 1, this should always be sub-second instead of 15 seconds:SELECT 'exists'WHERE exists ( SELECT * FROM toutput WHERE...
31203 Apr 2008 @ 03:11 PDTAnalyticsReplyIs there a performance hit selecting rows based upon a constructed CASE column?Hi Ti,you're right, there's no difference. Btw, if you want to lower CPU usage, stay away from conversions involving type casts from char to numeric/date and vice versa.Dieter
31101 Apr 2008 @ 08:49 PDTUDAReplyData Skew on a UPI table (with HIGH volume) - How?Hi Sayee,most of the skew is due to the high number of AMPs:There are 65536 entries within the hashmap, so the average number of entries per AMP is:SELECT 2**16/1400 -> 46,81Of course there's ...
31025 Mar 2008 @ 01:46 PDTAnalyticsReplyhow to generate addition row as counterHi Dhiraj,did you try it, this will not work :-)If it's less than 73000 values:insert into tabselect day_of_calendar from sys_calendar.calendarwhere day_of_calendar <= 1000Dieter
30925 Mar 2008 @ 01:42 PDTToolsReplyhow to get rid of unreadable characters generated by fastExportYou should use FORMAT TEXT.This will get rid of the record length, but you're going to export a fixed length character with lots of trailing blanks.IMHO it would be easier (and require less bytes) ...
30825 Mar 2008 @ 01:34 PDTDatabaseReplySP: Timestamp as Host variableHi Tim,too many TIMESTAMPs:call dbc.sysexecsql('INSERT INTO logarchive.TB_DBQLObjTbl_' || table_year ||' SELECT * FROM DBC.DBQLObjTbl WHERE CollectTimeStamp BETWEEN TIMESTAMP ''' || stamp1 || ''' A...
30725 Mar 2008 @ 01:25 PDTDatabaseReplycast as dateTeradata stores dates using following formula:(year - 1900) * 10000 + month * 100 + dayIt's a bit strange, but working :-)So the easiest way to cast your Decimal is CAST(20040924 - 19000000 AS DATE...
30611 Mar 2008 @ 04:25 PDTDatabaseReplyprint statementPRINT is outdatet syntax, no longer availabe in current releases.But even in old releases it never wrote to stdout, because this would be stdout on the node of your session's PE.You needed access t...
30511 Mar 2008 @ 04:09 PDTDatabaseReplyvconfigIt's information about the logical and physical configuration of your system, e.g. which AMP runs on which node and which disks are assigned to that AMP. You'll never gonna modify that vconfig.txt ...
30411 Mar 2008 @ 03:57 PDTDatabaseReplyHow to avoid line feed?Hi bonchibuji,BTEQ in REPORT mode will not export any cr/lf, but replace those non-printable charcters with blanks.If you export using DATA mode you'll have to replace the cr/lf, the best way to ac...

Pages