#DateForumTypeThreadPost
47806 Mar 2009 @ 10:02 PSTDatabaseReplyCharacter Set TranslationYou can't use TRANSLATE for that purpose, but the Oracle Translate UDF (OTRANSLATE) might be what you're looking for.Dieter
47706 Mar 2009 @ 09:59 PSTDatabaseReplyError: 3977: The Parent Key is not unique or not validCould you please elaborate after RTFM?3977 The Parent Key is not unique or not valid.Explanation: The referenced column(s) (Parent Key)specified in the referential constraint must either be avalid ...
47604 Mar 2009 @ 01:52 PSTToolsReplyWhat is .Export Reset in bteqCan you elaborate, why you're not satisfied?Just try to open the export file wihout closing it :-)Dieter
47504 Mar 2009 @ 01:31 PSTUDAReplyProblem Using UPSERT5565 The UPDATE specified in the UPSERTstatement is a complex update.Explanation: The user specified the UPDATE in theUPSERT statement as a complex update. Examples ofcomplex update are UPDATEs tha...
47404 Mar 2009 @ 01:43 PSTDatabaseReplyNOT inAny comparison to NULL results in UNKNOWN (the only way to check for NULLs is IS [NOT] NULL). A "set subtraction operator" like EXCEPT would return (3) for (1,2,3) EXCEPT (1,2,?), but NOT IN follow...
47303 Mar 2009 @ 05:30 PSTDatabaseReplyNOT inThis behaviour is one of the reasons why NULLs are evil.Never ever use NOT IN on NULLable columns, the result set might be empty and even if it's correct, there's a lot of work for the database.Alw...
47203 Mar 2009 @ 02:58 PSTAnalyticsReplyDecoding and Filtering MonthsWhat's the meaning of month 1310? Looks like the number of months since 1900-01-01?SELECT ADD_MONTHS(DATE '1900-01-01' , 1310) returns 2009-03-01You might check the month_of_calendar column in sys...
47113 Feb 2009 @ 03:56 PSTUDAReplyPARTITION INFORMATIONAs a partition might be defined usng some cmplex calculation based on several columns, there's no column information, but if you query dbc.indexconstraints you'll find the definition in column Cons...
47013 Feb 2009 @ 03:46 PSTUDAReplydate computation from weekno and a column value - SQL HelpYour example uses Scalar Subqueries, in most cases it's easy to re-write them using (Outer) Joins, this is what i did:select a,b,b.normal_date,sum(c)from table a join cal bon a.weekid = b.week_idan...
46913 Feb 2009 @ 03:33 PSTDatabaseReplyUSI in multiloadA USI is similar to a table with a UPI on the indexed column, e.g.create index usi (i int) on tab;creates a subtable which is exactly the same ascreate table usi(i int, -- indexed columnoverhead by...
46813 Feb 2009 @ 03:25 PSTDatabaseReplyNEED PK, FK, and index of table narrowed down by Schema, table and KEY name if possibleYep,there's dbc.all_ri_children and dbc.all_ri_parentsDieter
46712 Feb 2009 @ 03:30 PSTUDAReplydate computation from weekno and a column value - SQL HelpWho can be blamed for that **** data model? Is there only a single value <> 0 for each row or multiple?single value <> 0:select ....from tab join calon tab.weekid = cal.weekidand case...
46612 Feb 2009 @ 06:44 PSTAnalyticsReplyGROUP BY ROLLUPThis is exactly what ROLLUP is ment for:select ReportingMonth,Product,Type,Group,count (distinct (case when Product_Subscriber = 1 then customer_id else null end)) as Customers,sum(Activation) as A...
46512 Feb 2009 @ 06:41 PSTUDAReplydate computation from weekno and a column value - SQL Help???How do you know that "weekno 10000 indicates 1 week of 2009"?"if Sat_7 has a value >0 the date would be 3-Jan-2009." And if it's <=0? What range of values is possible in Sat_7?How are week...
46412 Feb 2009 @ 06:07 PSTUDAReplySummarizing the data on date fieldCalculate the weekday using "(datecol - a_known_weekday) mod 7" and substract that.For weeks ending on saturday it's:date_col - ((date_col - DATE '0001-01-07') MOD 7) Dieter
46312 Feb 2009 @ 05:57 PSTUDAReplySummarize data based on weeksWEEK is not a Teradata SQL function, it's ODBC SQL.It will only work using ODBC in SQL Assistant if you switch on Tools - Options - Allowe Use of ODBC SQL Extensions in Queries And it's based on so...
46212 Feb 2009 @ 05:45 PSTUDAReplyWhy this SQL is not filtering recordsIf there's any B.Start_Dt which is not equal to '2008-11-30' a row will be returned in codeA but not in codeB.All conditions in ON are part of the join condition, but if the join condition is not T...
46112 Feb 2009 @ 05:30 PSTDatabaseReplyRedistribution and duplication of records across all AmpsMaybe the optimizer is confused because of the previous product join.But if you want to update all rows to the same value, i'd recommend two separate steps in a Stored Proc:DEFINE AppIn INTEGER?;SE...
46012 Feb 2009 @ 05:16 PSTDatabaseReplyNEED PK, FK, and index of table narrowed down by Schema, table and KEY name if possibleIndex: dbc.IndicesFK: dbc.ri_distinct_children and dbc.ri_distinct_parentsPK: Teradata replaces any PK definition with a unique index, UPI or USIBut PrimaryKeyIndexId in dbc.Tables keeps track of t...
45912 Feb 2009 @ 05:03 PSTDatabaseReplyUSI in multiloadIn MLoad's Application phase each AMP processes it's data locally.NUSI-rows are AMP-local, so any modification is handled within that AMP.But USI-rows are stored on a different AMP, thus that AMP h...
45812 Feb 2009 @ 04:51 PSTDatabaseReplyimport dataIt's nice that you post all that interesting data, could you please add even more sensible information like account and credit card numbers, too?Dieter
45712 Feb 2009 @ 04:46 PSTToolsReplyIncrease the log level or tracing using control filesWhat do you mean by "tracing level or log level"?Dieter
45612 Feb 2009 @ 04:44 PSTToolsReplyBest file format for TPUMP / MultiloadThe best format is always internal Teradata format, because then there's no need for any data type conversion.DATA in BTEQFORMAT FASTLOAD in MLoad/TPump/FExpAnd if there are any NULLable columns us...
45512 Feb 2009 @ 04:39 PSTToolsReplyObtain value in variable saved in ksh during fast export.ACCEPT ... FROM ENV VAR allows you to access an Environment Variable.Instead of counting lines you might use grep to filter FExp output forUTY8722 xxx total records written to output file.Of cours...
45412 Feb 2009 @ 04:29 PSTToolsReplyAmbiguous results by fastexport..FORMAT TEXT doesn't mean it's actually characters.Those are no junk characters, but a two byte unsigned integer indicating the length of the following VarChar.If you use any literal it's a VarChar ...

Pages