176 | 30 Aug 2007 @ 07:01 PDT | Third Party Software | Topic | Teradata with Java CAPS | Has 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... |
175 | 26 Jul 2007 @ 01:00 PDT | Database | Reply | What 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 ... |
174 | 20 Jul 2007 @ 03:30 PDT | Analytics | Reply | Format 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.... |
173 | 20 Jul 2007 @ 03:21 PDT | Database | Reply | Time 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! |
172 | 20 Jul 2007 @ 08:24 PDT | Database | Reply | Time difference in seconds | Feroz,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... |
171 | 20 Jul 2007 @ 07:56 PDT | Database | Reply | Variables and Bteq | You 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. |
170 | 20 Jul 2007 @ 07:53 PDT | Database | Reply | index selection | Ragav,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... |
169 | 19 Jul 2007 @ 09:59 PDT | Analytics | Reply | how to remove '-' in number by using case statement | Kumar,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 |
168 | 19 Jul 2007 @ 09:55 PDT | Database | Reply | index selection | Ragav,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... |
167 | 18 Jul 2007 @ 07:20 PDT | Database | Reply | index selection | Ragav,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... |
166 | 17 Jul 2007 @ 07:46 PDT | Third Party Software | Reply | GUI 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 ... |
165 | 17 Jul 2007 @ 07:42 PDT | Database | Reply | index selection | Ragav,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... |
164 | 11 Jul 2007 @ 06:54 PDT | Training | Reply | Query Problem | That'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... |
163 | 11 Jul 2007 @ 06:48 PDT | Database | Reply | rows unbounded preceding | It 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 ... |
162 | 10 Jul 2007 @ 11:27 PDT | Database | Reply | rows 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... |
161 | 10 Jul 2007 @ 11:19 PDT | Database | Reply | Using 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,... |
160 | 10 Jul 2007 @ 11:12 PDT | UDA | Reply | Case Statement 2 | In 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... |
159 | 09 Jul 2007 @ 01:20 PDT | UDA | Reply | Case Statement 2 | If 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... |
158 | 02 Jul 2007 @ 12:17 PDT | Database | Reply | Selecting 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:... |
157 | 28 Jun 2007 @ 01:04 PDT | UDA | Reply | product Join | I 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... |
156 | 15 Jun 2007 @ 03:11 PDT | Database | Reply | Date validation in Teradata | If 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... |
155 | 13 Jun 2007 @ 07:38 PDT | Database | Reply | Predicate pushing in Teradata | Can't you do the grouping that you want in the macro itself? |
154 | 11 Jun 2007 @ 08:14 PDT | Database | Reply | AWT and db performance | There'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)... |
153 | 11 Jun 2007 @ 07:59 PDT | Database | Reply | Predicate pushing in Teradata | No, 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? |
152 | 11 Jun 2007 @ 07:56 PDT | Tools | Reply | Fastload DateTime, Timestamp | It 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... |