#DateForumTypeThreadPost
457804 Aug 2016 @ 01:31 PDTDatabaseReplyRetrieve Rows where a change in values (intwo columns) occuredYour example is not showing the correct rows to be returned. You're searching for changes in two columns and two directions (previous/next row), thus you need multiple OLAP functions: quali...
457702 Aug 2016 @ 09:58 PDTDatabaseReplyODBC: ERROR [42000] Data Type "Account_Num" does not match a Defined Type name. Do the modifications I wrote (and add a final Group By), this results in a query like this: with t1(Account_Num, Acct_Status_End_Dt) As ( Select Account_Num, Acct_Status_End_Dt from dp_vie...
457602 Aug 2016 @ 09:02 PDTAsterReplyAlternative to Strtok in Teradata AsterAs long as there's only a single character delimiter you can get a similar result using split_part('Text1,Text2,Text3,Text4,Text5',',',1)  Caution: multiple consecuti...
457502 Aug 2016 @ 06:03 PDTDatabaseReplyODBC: ERROR [42000] Data Type "Account_Num" does not match a Defined Type name. There are multiple problems with your query, the error message is caused by Select t2(Account_Num this should probaby be Select t2.Account_Num, but there's no alias t2 defined, y...
457401 Aug 2016 @ 02:09 PDTDatabaseReplyCalculating min and max column values in TeradataYou want to combine consecutive rows, this is usually done by assigning a group number using an OLAP function followed by aggregation: select cust_name, min(month), max(month) from (...
457329 Jul 2016 @ 12:00 PDTDatabaseReplyCrosstab query help in TeradataTo pivot data you need to apply MAX to the CASEs like MAX(case when HM = 'COAFunctional' then 1 else 0 end) and add a final GROUP BY KeyId. If KeyId is the PI of the table this should be...
457223 Jul 2016 @ 09:52 PDTDatabaseReplyCreate a table with a date column that has a defaultTO_DATE exists in Teradata since TD14 (2012), before it was available as a C-UDF.
457122 Jul 2016 @ 06:50 PDTDatabaseReplyConsequential inserts into large fact table performing very slow inside stored ProcedureAs the first insert is fast and the following geting slower you might have a SET table with lots of rows per NUPI. And you seem to run those inserts sequentially, the first will be using FastPath ...
457021 Jul 2016 @ 10:53 PDTDatabaseReplyEquivalent function for DATEADDWhat's the datatype of orig_ord_dt_tm, does it include WITH TIME ZONE? This returns exactly what you did in SQL Server: orig_ord_dt_tm - INTERVAL '7' HOUR You might also get a...
456921 Jul 2016 @ 08:07 PDTDatabaseReplyProblems installing in TDExpress15.00.02_Sles11_40GBWhen you succeed to upgrade SLES to 11.2 Teradata will not start because SLES 11.1 is a dependency  :-)
456820 Jul 2016 @ 11:56 PDTAnalyticsReplyHow to create churn data for customers by transaction in month?Answered on StackOverflow: How to by pass case not when exists in teradata? Btw, recursive is not bad in Teradata if it's done the right way :)
456720 Jul 2016 @ 03:43 PDTDatabaseReplyData DictionaryThe size of the actual Data Dictionary tables (excluding TransientJournal, AccessLog, QueryLog, ResUsage) is way less than 1% of the system's perm space. Best practices recommend to reduce the...
456619 Jul 2016 @ 02:06 PDTDatabaseReplyConsolidate rowsWhat's your Teradata version? 14.10 has a quite unknown syntax using NORMALIZE over PERIODs: SELECT CATEGORY, TYPE, -- split the period in start and end again BEGIN(pd), LAST(pd) F...
456519 Jul 2016 @ 12:00 PDTDatabaseReplyConsolidate rowsIs "2016-02-01" Feb. 1st or Jan. 2nd, i.e. do you want to combine only periods without gaps?
456419 Jul 2016 @ 09:58 PDTToolsReplyIn List Blues - How to make large In List Queries More Efficient Yep, this doesn't need to be a Stored Procedure, a Teradata Macro will also work.   But literal strings are limited to 31000 characters in Teradata, you will hit this limit. You might p...
456319 Jul 2016 @ 05:45 PDTDatabaseReplyFunction quantile throws a numeric overflow error in a large tableCheck if you can avoid two OLAP-steps, SUM(CAST(1 AS BIGINT)) OVER (...) might be ok (if the data is not unique rows with the same value might be assigned to two quantiles)   Btw, TD15.10 fi...
456219 Jul 2016 @ 03:58 PDTDatabaseReplyData DictionaryThere's no need to increase the Data Dictionary size. Teradata's DD are the tables located in dbc and there's usually a large perm space assigned to it (it also includes the Transient ...
456119 Jul 2016 @ 03:51 PDTToolsReplyIn List Blues - How to make large In List Queries More Efficient What's a "tremendous amount", 100s, 1000s or 10000s of values? You might investigate usage of STRTOK_SPLIT_TO_TABLE, either as part of the Select or as Insert/Select in a Macro (hope...
456017 Jul 2016 @ 04:53 PDTDatabaseReplyAssigning a value of 1 only to the lowest value for an address type for each customerID in a tableThis should return the expected result: case when address_type = min(address_type) over (partition by customerID) then 'Y' else 'N' end  
455917 Jul 2016 @ 04:48 PDTDatabaseReplyText Manipulation-Identify a 6 Character String and Replace with Ideal text Simply use a regular expression, this will replace exactly 6 digits: REGEXP_REPLACE (description, '[0-9]{6}','£')
455817 Jul 2016 @ 04:42 PDTGeneralReplyConverting Time stamp in UTC including Day light SavingYou get a timestamp with daylight savings but without the time zone? How do you know the actual time for '2016-10-30 01:30:00'?   You might try SET TIME ZONE  'europe w...
455714 Jul 2016 @ 08:29 PDTDatabaseReplyhow to make Stored Procedure with dynamic SQL to display the records?You forgot to add WITH RETURN ONLY to the cursor definition: DECLARE cur1 CURSOR WITH RETURN ONLY FOR S1;  
455614 Jul 2016 @ 07:52 PDTDatabaseReplydate variable not parsing proerly in Stored ProcedureThis is the resulting string: SELECT '2016-02-01' AS max_avail_date, 'create multiset volatile table ttt as (select '||cast(max_avail_date as date format 'yyyy-mm-dd')...
455513 Jul 2016 @ 06:16 PDTDatabaseReplyextending MLPPI tablesYou can't use ALTER to add a new partitioning column, you must create a new table.
455408 Jul 2016 @ 02:41 PDTDatabaseReplyoTranslate issueYour query will remove the characters not in the list, only the first character (a space) will be replaced by a space.  As oTranslate replaces each character in the 2nd parameter with the cha...

Pages