#DateForumTypeThreadPost
17630 Aug 2007 @ 07:01 PDTThird Party SoftwareTopicTeradata with Java CAPSHas anyone used Teradata with Java CAPS? Specifically we're trying to use the Java CAPS OTD (Object Type Definition) to define a Teradata macro to Java CAPS. The Java CAPS OTD interface allows yo...
17526 Jul 2007 @ 01:00 PDTDatabaseReplyWhat is meant by "SPOIL" in explain plan?Sakthi,That means that any plans that access that table that are in the plan cache will be flushed from cache. That's done so that the plan can be re-generated using the new statistics, which may ...
17420 Jul 2007 @ 03:30 PDTAnalyticsReplyFormat to provide comma separators?If you are using SQL Assistant, the reason that the format was ignored was because ODBC will ignore format statements. It instead will send back each field based on the data type in the resultset....
17320 Jul 2007 @ 03:21 PDTDatabaseReplyTime difference in seconds"EXTRACT" is probably faster and is more straightforward. I tend to forget about some of the newer functions. That'll teach me for getting old!
17220 Jul 2007 @ 08:24 PDTDatabaseReplyTime difference in secondsFeroz,You'll have to do "minute(4) to second" or "hour(4) to second" and then break it up and multiply the minutes by 60 or if you use "hour(4) to second", multiply the hours by 3600, the minutes b...
17120 Jul 2007 @ 07:56 PDTDatabaseReplyVariables and BteqYou can't, but you can use the IMPORT statement to import the fields into a volatile table. You can then join in your volatile table to get the values.
17020 Jul 2007 @ 07:53 PDTDatabaseReplyindex selectionRagav,In order to help determine the optimal path, you'll want to know the following things:Number of rows in table1?Number of rows in table2?Number of rows in table3?Expected number of rows when t...
16919 Jul 2007 @ 09:59 PDTAnalyticsReplyhow to remove '-' in number by using case statementKumar,This should do it:select case when substr(acct_nbr, 8, 1) = '-' then substr(acct_nbr, 1, 7) || substr(acct_nbr, 9, 4) else acct_nbr endRegards,Barry
16819 Jul 2007 @ 09:55 PDTDatabaseReplyindex selectionRagav,If you are avoiding the redistribution of your large table, then your query is about as efficient as it's going to get without doing something like creating a join index. Do you have a requi...
16718 Jul 2007 @ 07:20 PDTDatabaseReplyindex selectionRagav,If table1 and table3 are small and also have the same NUPI, I would think that the optimizer would be joining those two first, and then redistributing and sorting the result to join to table2...
16617 Jul 2007 @ 07:46 PDTThird Party SoftwareReplyGUI front end ?Andrew,Teradata does not have a tool for this (unless you're talking about a specific application like CRM), but they partner with Microstrategy, Cognos, Business Objects, and others for this type ...
16517 Jul 2007 @ 07:42 PDTDatabaseReplyindex selectionRagav,By specifying a NUPI of either "(col1, col2)" or "(col3,col4)" on table1, you can get it to join table1 to either table2 or table3 without a redistribution of the table. What are the sizes o...
16411 Jul 2007 @ 06:54 PDTTrainingReplyQuery ProblemThat's an invalid syntax for Teradata. You cannot code a subselect within the SET clause of the UPDATE statement. Instead, you need to create a derived table and then use the column from the deri...
16311 Jul 2007 @ 06:48 PDTDatabaseReplyrows unbounded precedingIt doesn't make any sense to partition on the same column that you're summing on. The partition field should be what you want the running sum to be for. For instance, if I have a bunch of orders ...
16210 Jul 2007 @ 11:27 PDTDatabaseReplyrows unbounded preceding"Rows unbounded preceding" is used in an ordered analytical function to tell it to include all of the preceding rows in the partition in the calculation being performed. When you specify it, it wi...
16110 Jul 2007 @ 11:19 PDTDatabaseReplyUsing the 'Right' Keyword in a query?Use "substr":Select substr(columnname,1,xx)...where 'xx' is the length you want. If you want the right substring, it's a little more difficult:select substr(columnname,length(columnname) - xx + 1,...
16010 Jul 2007 @ 11:12 PDTUDAReplyCase Statement 2In that case, you can just repeat your existing code down to where it's different and create another column, like the following:SELECT top 5 (CASE WHEN r9.rated_driver_id Is Not Null AND t9.rated_P...
15909 Jul 2007 @ 01:20 PDTUDAReplyCase Statement 2If your intent is to calculate a column called "tr_single_driver" without actually selecting the column, then you can do something like this:WHEN t9.rated_P_driver_code IS NULLTHEN (coalesce(r9.rat...
15802 Jul 2007 @ 12:17 PDTDatabaseReplySelecting ODD numbered rows from a Table!Balwant,There is no concept of row "numbering" within Teradata, so you can't get the odd or even numbered rows.If you want to get every other row from a particular query, you can do something like:...
15728 Jun 2007 @ 01:04 PDTUDAReplyproduct JoinI think that this will do the equivalent:SELECT count(distinct a.claim_ref_id) FROM fat_bse_cl_pay_cre_trans aJOIN RRtv_lu_day bON (a.Rep_reported_date_id = b.Rep_reported_date_id)WHERE (a.claim_re...
15615 Jun 2007 @ 03:11 PDTDatabaseReplyDate validation in TeradataIf you're on a version of Teradata that supports UDF's (v2r6 or v2r6.1, I believe), then an example "IS_DATE" UDF is available for download at the teradata web site along with many other Oracle fun...
15513 Jun 2007 @ 07:38 PDTDatabaseReplyPredicate pushing in TeradataCan't you do the grouping that you want in the macro itself?
15411 Jun 2007 @ 08:14 PDTDatabaseReplyAWT and db performanceThere's really not much tuning that can be done in regard to AWT's. You can change the number of them and you can also reserve some to be used for "quick-running" queries (PI or USI type accesses)...
15311 Jun 2007 @ 07:59 PDTDatabaseReplyPredicate pushing in TeradataNo, the column has to be in the view in order for you to use it in the WHERE clause in your macro.What is the reason that you don't want it in the view?
15211 Jun 2007 @ 07:56 PDTToolsReplyFastload DateTime, TimestampIt looks like you have a few issues:1) Your data is in a format of 'mm/dd/yyyybhh:mi:ss' but your format has 'yyyy-mm-ddbhh:mm:ss'. Your "nullif" statement is also in a format that doesn't match y...

Pages