#DateForumTypeThreadPost
42807 Jan 2009 @ 04:35 PSTUDAReplySpool 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 ...
42707 Jan 2009 @ 04:26 PSTDatabaseReplyTricky Date ManipulationHi 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...
42607 Jan 2009 @ 04:12 PSTToolsReplyCapturing Help stats resultInstead 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...
42507 Jan 2009 @ 04:07 PSTToolsReplyHow to convert Ordinal (Julian) Date to Calendar DateJust use a format string specifying the julian date:select '032' (date, format 'ddd');select '2009032' (date, format 'yyyyddd');Dieter
42427 Dec 2008 @ 06:43 PSTDatabaseReplyCombining Parameters of a MacroWhat about adding some wildcards? ;-)REQUESTTEXT LIKE '%' || TRIM(:Dbname) || '.' || TRIM(:Tbname) || '%';Dieter
42322 Dec 2008 @ 03:56 PSTDatabaseReplyLinking a Table name to a table IdHi 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...
42219 Dec 2008 @ 03:24 PSTToolsReplyFacing Issue while Using Import in BTeqHi 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...
42119 Dec 2008 @ 03:13 PSTDatabaseReplyExplain Plan : 1=1 join meaningThis is a dummy join condition, usually for a product join as a result of an unconstraint cross-join within your SQL statement.Dieter
42019 Dec 2008 @ 03:10 PSTDatabaseReplyAdding URN or ID columnHi 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...
41917 Dec 2008 @ 03:46 PSTUDAReplycombining columsnYou 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 ...
41817 Dec 2008 @ 11:40 PSTDatabaseReplyJoin with Different Data FormatsIf 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...
41715 Dec 2008 @ 11:01 PSTDatabaseReplyRegarding Identity numbers generation by PE/AMPSHi 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...
41615 Dec 2008 @ 10:48 PSTDatabaseReplyHow to make date logic/math use a date partitionHi 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...
41515 Dec 2008 @ 10:41 PSTDatabaseReplySub-Query Join and Union ProblemHi 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...
41412 Dec 2008 @ 04:07 PSTDatabaseReplyStatistics information retrieved in dbc for Teradata 12Hi 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
41312 Dec 2008 @ 04:05 PSTToolsReplyusing 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....
41205 Dec 2008 @ 04:02 PSTDatabaseReplyCoalesce with Nested Case Statement - Error3707I 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...
41104 Dec 2008 @ 04:25 PSTDatabaseReplyHow 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...
41003 Dec 2008 @ 07:29 PSTDatabaseReplyNested table in teradataHi Koushik,sorry, but there's no inheritance in Teradata, neither TD12 nor TD13.Dieter
40903 Dec 2008 @ 07:27 PSTDatabaseReplyHow can we find dipendency in Teradata databaseHi 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...
40802 Dec 2008 @ 07:29 PSTDatabaseReplyUse of order byAll 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...
40726 Nov 2008 @ 09:33 PSTDatabaseReplyHow 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.....
40626 Nov 2008 @ 03:47 PSTDatabaseReplyHow 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
40526 Nov 2008 @ 03:27 PSTDatabaseReplyUse of order byHi 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...
40422 Nov 2008 @ 03:43 PSTUDAReplySQL problem - selecting an earlier date in a joinHi 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...

Pages