4578 | 04 Aug 2016 @ 01:31 PDT | Database | Reply | Retrieve Rows where a change in values (intwo columns) occured | Your 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... |
4577 | 02 Aug 2016 @ 09:58 PDT | Database | Reply | ODBC: 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... |
4576 | 02 Aug 2016 @ 09:02 PDT | Aster | Reply | Alternative to Strtok in Teradata Aster | As 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... |
4575 | 02 Aug 2016 @ 06:03 PDT | Database | Reply | ODBC: 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... |
4574 | 01 Aug 2016 @ 02:09 PDT | Database | Reply | Calculating min and max column values in Teradata | You 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
(... |
4573 | 29 Jul 2016 @ 12:00 PDT | Database | Reply | Crosstab query help in Teradata | To 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... |
4572 | 23 Jul 2016 @ 09:52 PDT | Database | Reply | Create a table with a date column that has a default | TO_DATE exists in Teradata since TD14 (2012), before it was available as a C-UDF.
|
4571 | 22 Jul 2016 @ 06:50 PDT | Database | Reply | Consequential inserts into large fact table performing very slow inside stored Procedure | As 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 ... |
4570 | 21 Jul 2016 @ 10:53 PDT | Database | Reply | Equivalent function for DATEADD | What'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... |
4569 | 21 Jul 2016 @ 08:07 PDT | Database | Reply | Problems installing in TDExpress15.00.02_Sles11_40GB | When you succeed to upgrade SLES to 11.2 Teradata will not start because SLES 11.1 is a dependency :-)
|
4568 | 20 Jul 2016 @ 11:56 PDT | Analytics | Reply | How 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 :)
|
4567 | 20 Jul 2016 @ 03:43 PDT | Database | Reply | Data Dictionary | The 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... |
4566 | 19 Jul 2016 @ 02:06 PDT | Database | Reply | Consolidate rows | What'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... |
4565 | 19 Jul 2016 @ 12:00 PDT | Database | Reply | Consolidate rows | Is "2016-02-01" Feb. 1st or Jan. 2nd, i.e. do you want to combine only periods without gaps?
|
4564 | 19 Jul 2016 @ 09:58 PDT | Tools | Reply | In 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... |
4563 | 19 Jul 2016 @ 05:45 PDT | Database | Reply | Function quantile throws a numeric overflow error in a large table | Check 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... |
4562 | 19 Jul 2016 @ 03:58 PDT | Database | Reply | Data Dictionary | There'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 ... |
4561 | 19 Jul 2016 @ 03:51 PDT | Tools | Reply | In 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... |
4560 | 17 Jul 2016 @ 04:53 PDT | Database | Reply | Assigning a value of 1 only to the lowest value for an address type for each customerID in a table | This should return the expected result:
case
when address_type
= min(address_type)
over (partition by customerID)
then 'Y'
else 'N'
end
|
4559 | 17 Jul 2016 @ 04:48 PDT | Database | Reply | Text 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}','£')
|
4558 | 17 Jul 2016 @ 04:42 PDT | General | Reply | Converting Time stamp in UTC including Day light Saving | You 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... |
4557 | 14 Jul 2016 @ 08:29 PDT | Database | Reply | how 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;
|
4556 | 14 Jul 2016 @ 07:52 PDT | Database | Reply | date variable not parsing proerly in Stored Procedure | This 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')... |
4555 | 13 Jul 2016 @ 06:16 PDT | Database | Reply | extending MLPPI tables | You can't use ALTER to add a new partitioning column, you must create a new table.
|
4554 | 08 Jul 2016 @ 02:41 PDT | Database | Reply | oTranslate issue | Your 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... |