4328 | 04 Apr 2016 @ 11:43 PDT | Database | Reply | SQL 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.
|
4327 | 04 Apr 2016 @ 05:34 PDT | Database | Reply | Execution modes in teradata | For 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/... |
4326 | 04 Apr 2016 @ 03:58 PDT | Database | Reply | Execution modes in teradata | dbc.FunctionsV.ExecProtectionMode -> U,P
|
4325 | 04 Apr 2016 @ 03:57 PDT | Database | Reply | UV table | The 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', ... |
4324 | 04 Apr 2016 @ 03:51 PDT | Database | Reply | How to identify the exact line number or SQL code which is causing bad character issue - Error Code 2620 | Hi 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 ... |
4323 | 04 Apr 2016 @ 01:32 PDT | Database | Reply | Joining varchar to integer | That'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... |
4322 | 04 Apr 2016 @ 12:28 PDT | Database | Reply | Recursive Row Function | You 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.
|
4321 | 03 Apr 2016 @ 11:33 PDT | Database | Reply | Recursive Row Function | Hi 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 ... |
4320 | 03 Apr 2016 @ 02:57 PDT | Database | Reply | Sql to find oldest created accounts and tag it across all acounts | What's your Teradata release?
In 14.10 there's FIRST_VALUE:
FIRST_VALUE(c2)
OVER (PARTITION BY c1??
ORDER BY c4)
|
4319 | 03 Apr 2016 @ 02:54 PDT | Database | Reply | Can'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... |
4318 | 02 Apr 2016 @ 03:43 PDT | General | Reply | Finding 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... |
4317 | 02 Apr 2016 @ 03:27 PDT | Database | Reply | Stripping RTF new line values from field | There'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... |
4316 | 01 Apr 2016 @ 12:12 PDT | Database | Reply | Update current row from previous row value until hits a condition | Are 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 = ... |
4315 | 01 Apr 2016 @ 12:06 PDT | Database | Reply | Improving query performance | Assuming 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
|
4314 | 01 Apr 2016 @ 12:00 PDT | Database | Reply | help optimizing GROUP BY query | Hi 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... |
4313 | 01 Apr 2016 @ 03:42 PDT | Analytics | Reply | Passing a string into a macro for use in a IN statement | Why don't you simply change the delimiter character to any other character which is not part of your data?
|
4312 | 01 Apr 2016 @ 03:30 PDT | Database | Reply | filter numeric letters from combination of alpha numeric letters and numeric letters | I don't understand what you want, can you show example, the correct result and why?
|
4311 | 01 Apr 2016 @ 03:28 PDT | Tools | Reply | How to Trim multiple characters from a string | Recursion 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... |
4310 | 31 Mar 2016 @ 08:55 PDT | UDA | Reply | convert varchar to timestamp | What'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... |
4309 | 31 Mar 2016 @ 08:34 PDT | Database | Reply | Group by, distinct, PARTITION BY | Hi 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... |
4308 | 31 Mar 2016 @ 03:49 PDT | Database | Reply | Group by, distinct, PARTITION BY | Well, 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'
... |
4307 | 31 Mar 2016 @ 03:30 PDT | Database | Reply | Group by, distinct, PARTITION BY | What's the relationship between shp_nbr and par_nbr?
Are there multiple par_nbr per shp_nbr or 1:n or m:n?
|
4306 | 31 Mar 2016 @ 02:39 PDT | Database | Reply | help optimizing GROUP BY query | Why 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... |
4305 | 31 Mar 2016 @ 02:31 PDT | Database | Reply | Partitioning by row and column | runs 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... |
4304 | 30 Mar 2016 @ 03:31 PDT | Analytics | Reply | Trying to ROUND UP | All 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 ... |