428 | 07 Jan 2009 @ 04:35 PST | UDA | Reply | Spool Space - savings tricks? | Hi Tim,the optimizer doesn't check if you spool space is large enough (although this would be a nice feature), it just tries to get the fastest plan.So the best/easiest solution is to ask your DBA ... |
427 | 07 Jan 2009 @ 04:26 PST | Database | Reply | Tricky Date Manipulation | Hi Sam,i usually use following apporach for joining on "closest date":UNION dates from both tables, add a marker to remember from which table is a specific date.Use an OLAP function to look for the... |
426 | 07 Jan 2009 @ 04:12 PST | Tools | Reply | Capturing Help stats result | Instead of capturing HELP STATS output you might extract that date from some dbc tables.You'll find a query extracting some info about collectet stats at:http://www.teradataforum.com/attach.htm"New... |
425 | 07 Jan 2009 @ 04:07 PST | Tools | Reply | How to convert Ordinal (Julian) Date to Calendar Date | Just use a format string specifying the julian date:select '032' (date, format 'ddd');select '2009032' (date, format 'yyyyddd');Dieter |
424 | 27 Dec 2008 @ 06:43 PST | Database | Reply | Combining Parameters of a Macro | What about adding some wildcards? ;-)REQUESTTEXT LIKE '%' || TRIM(:Dbname) || '.' || TRIM(:Tbname) || '%';Dieter |
423 | 22 Dec 2008 @ 03:56 PST | Database | Reply | Linking a Table name to a table Id | Hi Kent,you're right, it's because since TD12 a system might be set up to use a larger hashmap, 2^20 instead of 2^16.Hashmap() returns the maximum hash bucket, 65.535 or 1.048.575, thus the calcula... |
422 | 19 Dec 2008 @ 03:24 PST | Tools | Reply | Facing Issue while Using Import in BTeq | Hi Ajai,you can't do nested IMPORTs in BTEQ.You might create a file for a RUN by EXPORTing a string concatenating the SQL and parameters.Or concatenate both inputs into a single file prior to execu... |
421 | 19 Dec 2008 @ 03:13 PST | Database | Reply | Explain Plan : 1=1 join meaning | This is a dummy join condition, usually for a product join as a result of an unconstraint cross-join within your SQL statement.Dieter |
420 | 19 Dec 2008 @ 03:10 PST | Database | Reply | Adding URN or ID column | Hi Andrew,"csum(1,0)... or similar variations" probably means that you used x as ordering.This is not column x, but the value x and results in *all* rows to be sent to a single AMP, thus "no more s... |
419 | 17 Dec 2008 @ 03:46 PST | UDA | Reply | combining columsn | You shouldn't use MS SQL Server syntax in Teradata :-)"+" is a numerical operator, but no string operator, thus there's an automatic typecast to FLOAT, which causes that error.Don't use LEFT, it's ... |
418 | 17 Dec 2008 @ 11:40 PST | Database | Reply | Join with Different Data Formats | If a string is supposed to be an integer, it should be defined as an integer.Do you really need to cast it to an integer? Can't you keep it as a char(9)?If you still want to cast it to an integer a... |
417 | 15 Dec 2008 @ 11:01 PST | Database | Reply | Regarding Identity numbers generation by PE/AMPS | Hi Annal,each AMP/PE requests a bunch of values (by default 100000) and then assignes those values one after the other, just like a sequence in Oracle.But sequence values are assigned by muliple AM... |
416 | 15 Dec 2008 @ 10:48 PST | Database | Reply | How to make date logic/math use a date partition | Hi Dennis,you must be running a release pre-TD12, because this version should work the way you want it.You might try a Derived Table/View with calculated columns, maybe the optimizer does a product... |
415 | 15 Dec 2008 @ 10:41 PST | Database | Reply | Sub-Query Join and Union Problem | Hi Tim,first of all you should check if you can replace the UNION with a UNION ALL.UNION is automatically DISTINCT, which is a huge overhead.This should help already.If it's still not fast enough p... |
414 | 12 Dec 2008 @ 04:07 PST | Database | Reply | Statistics information retrieved in dbc for Teradata 12 | Hi Koen,there changed a lot in TD12, especially on 64bit.You'll find the latest version of that stats query on the TeradataForum:http://www.teradataforum.com/attach.htmDieter |
413 | 12 Dec 2008 @ 04:05 PST | Tools | Reply | using OREPLACE function | '1A' is an error character which is used when a character cannot be translated.Is your example data the result of a TRANSLATE WITH ERROR?Your screenshot is showing ascii/latin but no unicode chars.... |
412 | 05 Dec 2008 @ 04:02 PST | Database | Reply | Coalesce with Nested Case Statement - Error3707 | I don't no of any limit regarding nested CASEs (i got a script which queries a table to create 100 kilobytes of nested CASEs for the next step).But some older Teradata releases somtimes required pa... |
411 | 04 Dec 2008 @ 04:25 PST | Database | Reply | How to Group by dates, Location and then sum? | You'd better apologize again, source data and expected result set don't match at all :-)But i think i know what you try to achieve:Whenever the SORCE_CLAIM_PMNT_DET_ID changes compared to the "prev... |
410 | 03 Dec 2008 @ 07:29 PST | Database | Reply | Nested table in teradata | Hi Koushik,sorry, but there's no inheritance in Teradata, neither TD12 nor TD13.Dieter |
409 | 03 Dec 2008 @ 07:27 PST | Database | Reply | How can we find dipendency in Teradata database | Hi Koushik,by default Teradata doesn't store any information about those dependencies and there are no DDL-Triggers in Teradata.But a part of the Teradata software is Metadata Services (MDS). If th... |
408 | 02 Dec 2008 @ 07:29 PST | Database | Reply | Use of order by | All rows with the same PI-value will be stored on the same AMP sequentially in the order of inserts.If there's no ORDER BY the rows are returned in that order.An answer set is returned *after* it's... |
407 | 26 Nov 2008 @ 09:33 PST | Database | Reply | How to sum records where a particular value changes within a grouping? | And now for something completely different :-)You just want all the data for the row with the latest PaymentFrom per SorceClaimID?This is neither GROUP BY GROUPING SETS nor SUM OVER (PARTITION BY..... |
406 | 26 Nov 2008 @ 03:47 PST | Database | Reply | How to sum records where a particular value changes within a grouping? | You post is a bit confusing, it would be helpful to get some more details what you're actually trying to achieve.This sounds like a GROUP BY GROUPING SETS or a SUM OVER (PARTITION BY...)Dieter |
405 | 26 Nov 2008 @ 03:27 PST | Database | Reply | Use of order by | Hi prakhar,accordign to Relational Data Model/Standard SQL an answer set is an unordered set of rows unless you specify ORDER BY.Teradata is a parallel DBMS based on hashing, so there's no built-in... |
404 | 22 Nov 2008 @ 03:43 PST | UDA | Reply | SQL problem - selecting an earlier date in a join | Hi Chris,sorry for the late reply, i just forgot about it.CREATE TABLE T1 (clientid INT, d DATE) UNIQUE PRIMARY INDEX(clientid, d);CREATE TABLE t2 (clientid INT, d DATE, val DEC(10,2));INS t1(1,DAT... |