#DateForumTypeThreadPost
432804 Apr 2016 @ 11:43 PDTDatabaseReplySQL for Deleting Data Older than n Years + current Year where Created_Date < add_months(trunc(current_date, 'y'), -12) Get January 1st of the current year and subtract one year.
432704 Apr 2016 @ 05:34 PDTDatabaseReplyExecution modes in teradataFor simple C-functions protected mode might be 5 to 10 times slower & uses much more CPU than Unprotected. http://www.info.teradata.com/HTMLPubs/DB_TTU_15_10/SQL_Reference/B035_1184_151K/...
432604 Apr 2016 @ 03:58 PDTDatabaseReplyExecution modes in teradatadbc.FunctionsV.ExecProtectionMode -> U,P
432504 Apr 2016 @ 03:57 PDTDatabaseReplyUV tableThe DBCErrorCode (MLoad MARK MISSING UPDATE) indicates that the target row was not found. If this is your actual WHERE-condition you compare to the literal string ':empid', ...
432404 Apr 2016 @ 03:51 PDTDatabaseReplyHow to identify the exact line number or SQL code which is causing bad character issue - Error Code 2620Hi Sakthi, only the position of a syntax error is returned (when you connect using .NET). But in your case it's a runtime error and then the actual column causing that is not included. When ...
432304 Apr 2016 @ 01:32 PDTDatabaseReplyJoining varchar to integerThat's the penalty for a bad datamodel :-)   What's the (P)PI of both tables? If you got bad data (and you can't clean it) you can try two things: ON CAST(A.Card_Id AS N...
432204 Apr 2016 @ 12:28 PDTDatabaseReplyRecursive Row FunctionYou could use col2 + 10000, but you will need a FIRST_VALUE or ROW_NUMBER in your seed query, so materializing your data should be the most efficient.
432103 Apr 2016 @ 11:33 PDTDatabaseReplyRecursive Row FunctionHi Raymond, you probably need recursion for this: WITH RECURSIVE prev AS ( SELECT tab.*, col3 AS col4 FROM tab WHERE col2 = 1 UNION ALL SELECT t.*, CASE ...
432003 Apr 2016 @ 02:57 PDTDatabaseReplySql to find oldest created accounts and tag it across all acountsWhat's your Teradata release? In 14.10 there's FIRST_VALUE: FIRST_VALUE(c2) OVER (PARTITION BY c1?? ORDER BY c4)  
431903 Apr 2016 @ 02:54 PDTDatabaseReplyCan't query a table with the table name containing "ALL"CHURNSEG_MODEL_INPUT_ALL is a view and one of the base tables doesn't exist (probably a UNION ALL of monthly tables), submit a SHOW VIEW CHURNSEG_MODEL_INPUT_ALL.   If you double qu...
431802 Apr 2016 @ 03:43 PDTGeneralReplyFinding the Sub String Between < and >Hi Sue, you can do that using combinations of POSITION/INSTR and SUBSTRING, but the easiest way is a regular expression: REGEXP_SUBSTR(x, '(?<=<).*?(?=>)') (?<=<) L...
431702 Apr 2016 @ 03:27 PDTDatabaseReplyStripping RTF new line values from fieldThere's translate(field using unicode_to_latin WITH ERROR) to replace invalid characters with the error character '1A'xc. But this error character could lead to untr...
431601 Apr 2016 @ 12:12 PDTDatabaseReplyUpdate current row from previous row value until hits a conditionAre you on TD14.10? Then use this Select as source for your Update: select ID ,last_value(NULLIF(COL1,'0') ignore nulls) over(order by ID) as new_val from tab qualify COL1 = &#...
431501 Apr 2016 @ 12:06 PDTDatabaseReplyImproving query performanceAssuming this is for a date/time column you can use a regex, there was a similar thread: http://forums.teradata.com/forum/enterprise/convert-varchar-to-timestamp#comment-147303
431401 Apr 2016 @ 12:00 PDTDatabaseReplyhelp optimizing GROUP BY queryHi imran, well, there's no secondary index in your table definition? Stay away from defining UPIs on all your tables, you'll get perfectly distributed tables and a horrible performan...
431301 Apr 2016 @ 03:42 PDTAnalyticsReplyPassing a string into a macro for use in a IN statementWhy don't you simply change the delimiter character to any other character which is not part of your data?
431201 Apr 2016 @ 03:30 PDTDatabaseReplyfilter numeric letters from combination of alpha numeric letters and numeric lettersI don't understand what you want, can you show example, the correct result and why? 
431101 Apr 2016 @ 03:28 PDTToolsReplyHow to Trim multiple characters from a stringRecursion is quite inefficient for tokenizing a string, better use STRTOK: WITH cte (inkey, str) AS ( SELECT OTL_ID , order_quesn_list FROM cl_otl_1 WHERE OTL_ID ='150...
431031 Mar 2016 @ 08:55 PDTUDAReplyconvert varchar to timestampWhat's your TD release? Did you try the SQL from the other thread?   TD15: REGEXP_REPLACE('2/3/2013', '\b([0-9])\b', '0\1') TD14: REGEXP_REPLACE('2/3/2013...
430931 Mar 2016 @ 08:34 PDTDatabaseReplyGroup by, distinct, PARTITION BYHi Sven, then you need a Derived Table as DISTINCT is processed after GROUP BY: SELECT par_nbr, COUNT(*), SUM(Wgt) FROM ( SELECT DISTINCT a.par_nbr, a.shp_nbr...
430831 Mar 2016 @ 03:49 PDTDatabaseReplyGroup by, distinct, PARTITION BYWell, seems like you don't need an OLAP-function but good old GROUP BY in that case: SELECT a.par_nbr, COUNT(a.shp_nbr), SUM(CASE WHEN b.wgt_typ_cd = 'L' ...
430731 Mar 2016 @ 03:30 PDTDatabaseReplyGroup by, distinct, PARTITION BYWhat's the relationship between shp_nbr and par_nbr? Are there multiple par_nbr per shp_nbr or 1:n or m:n?
430631 Mar 2016 @ 02:39 PDTDatabaseReplyhelp optimizing GROUP BY queryWhy do you create a three column UPI if customer_id is already unique?  Simply use UNIQUE PRIMARY INDEX (customer_id) and there's no redistribution needed.    Did yo...
430531 Mar 2016 @ 02:31 PDTDatabaseReplyPartitioning by row and columnruns out of CPU time and does not complete = workload CPU limit? Changing the granularity to '7' DAY will not help, it's still sorting the same number of rows.   Colum...
430430 Mar 2016 @ 03:31 PDTAnalyticsReplyTrying to ROUND UPAll your calculations use INTEGER-arithmethic, truncating the result. You're not dividing by (365/12 = 30.41667), but by 30.    Cast one of the operands to a FLOAT/NUMBER and then ...

Pages