All Forums

Share best practices, knowledge, successes and issues in Analytics, and discuss Analytical Applications.

You must login to post to the forums.
teradataR package and R 3.0.0 Topic by gstoel 03 May 2013 R teradataR

Hi All,
 
I am new to Teradata and trying to get my connection from R to work... I am running R 3.0.0 and installing the package through 
install.packages("~/Downloads/teradataR_1.0.1.zip", repos = NULL)
throws a warning that package ‘~/Downloads/teradataR_1.0.1.zip’ is not available (for R version 3.0.0)

8869 views
23 replies, 4 years ago
Failed [5484 : HY000]No ordered analytical function allowed in search condition for a joined table Topic by vidya@s 16 Mar 2015

Hi
From the PHRM_TBL.EFF_DT,  i want to derive END_DT (next (Max (EFF_DT)-1) and check if CLM_TBL.FILLD_DT  is between EFF_DT and END_DT .
I want to derive END_DT as below and compare with Filld_dt from CLM_TBL.
EFF_DT             END_DT 
2015-02-10       2015-02-24
2015-02-25       2015-02-28
2015-03-01       9999-12-31
 

1616 views
2 replies, 4 years ago
Help with lag/lead function in Teradata Topic by teresacase 16 Mar 2015

Hi,
 
I have been trying to produce a lag function to determine latest activity date.  I have only 2 columns -
custid and activity_date
 
I have tried the following code:

2961 views
1 reply, 4 years ago
SELECT Failed. 3706: Syntax error: expected something between '(' and the 'select' keyword. Topic by bdwebman 31 Mar 2011 syntax, 3706 error

I am troubling over this error as I am not sure what the cause might be. Any help would be greatly appreciated.

select Id, (select
case
when Fall_score = b.MaxScore then 'Fall'
when Spring_score = b.MaxScore then 'Spring'
from sandbox.testtable t where t.Id = b.Id
) as MaxScoreCategory,MaxScore
from (select Id, max(Score) as MaxScore
from (select Id, 'Fall', Fall_score from sandbox.testtable union all
select Id, 'Spring', Spring_score from sandbox.testtable)
a (Id, Category, Score)

58994 views
20 replies, 4 years ago
Connecting to Teradata in R via the teradataR package-Teradata Express VM version Topic by Alexander K 28 Feb 2014 teradatar, odbc, teradata express, TEVM, r

I am adding another article about connecting to Teradata through R by using the teradataR package. The following comes from my experience working on a 64-bit Teradata Express Virtual Machine (TEVM), and complements the existing forum topic at:
http://forums.teradata.com/forum/analytics/connecting-to-teradata-in-r-via-the-teradatar-package

5417 views
1 reply, 4 years ago
Concat and case when statement Topic by Mooli 21 Feb 2015

I need to concatenate 3 fields but the last one may contain "NA" as a value and I do not want that concatenated when found, but rather just left out. I tried this line of code but not working (error says cannot find DB). Any suggestions?
 
C.GRP_NBR||C.SUBGRP_NBR||C.P_NBR ( CASE WHEN C.P_NBR = 'NA' THEN ' ' ELSE C.P_NBR END) AS ABC_GROUP

Thanks.

1550 views
2 replies, 4 years ago
Adding count to a case when sum statement Topic by Mooli 16 Feb 2015 count, sum, case when multiple columns

I have the following CASE WHEN statement in my query and would like to get a count of each "category", so output would look something like this:
 

3654 views
10 replies, 4 years ago
Getting an average for multiple date ranges Topic by Mooli 21 Feb 2015

My apologies for posting this again, but I am still have some difficulties..
 

1676 views
4 replies, 4 years ago
Using Between with >= is this possible? Topic by Mooli 21 Feb 2015

SUM (CASE WHEN d.DCG < 0.50 THEN 1 ELSE 0 END)AS "<0.5",
SUM (CASE WHEN d.DCG between >=0.50 and <1.0 THEN 1 ELSE 0 END) AS "0.5-1.0",
SUM (CASE WHEN d.DCG between >=1.0 and <2.0 THEN 1 ELSE 0 END) AS "1.0-2.0",
SUM (CASE WHEN d.DCG >=2.0 THEN 1 ELSE 0 END)AS ">2.0"
 

1536 views
4 replies, 4 years ago
How to convert a 32 Bit Integer Number to its negative values as same like C Concepts Topic by kirankrj12 10 Feb 2015 numeric overflow

Hi,
I've scenario where I need to convert an Integer value to its Negative range
As we know that, range of Integer(32 Bit) is - 231 to 231-1
When I try to get the negative value, for example 3434340000 should return -1286856352
But below return with numeric overflow, which is an expected error
SEL CAST (3434340000 AS INTEGER)

1207 views
3 replies, 4 years ago
Include counting QTR year in SUM statement Topic by Mooli 16 Feb 2015 QTR in Sum statement

SELECT
MNTH_END_DT(format'YYYYMM') (char(6)) as RPT_MNTH,
NBR,
SUM(MMBR_CVRG_CNT)
FROM MBRSHP_MNTH_CNT MMC
WHERE MMC.CLNDR_MNTH_END_DT between '2010-01-01' and '2010-06-30'
GROUP BY 1
 

1525 views
8 replies, 4 years ago
How to join faster Topic by yunfeizhao 12 Feb 2015

I have four tables, each has 1+ million rows, and I only need to pull one row out (where tb3.id=12345). How should I do to get the result faster? I have two ways, either use on, or use where, but I do not know which one is faster. What comes first in the process, on, or where?  Can you please share the rules/guideline?
Thanks
Yunfei
method 1:
select tb1.*

1053 views
2 replies, 4 years ago
How to make multiple joins? Topic by yunfeizhao 12 Feb 2015

How to make multiple joins?
I have four tables (tb1-tb4), and each table has the same one column (id). I want an inner join between tb1 and tb2, then left join other tables.
select tb1.id
tb2.id
tb3.id
tb4.id
from tb1, tb2
left join tb3
on tb2.id=tb3.id
left join tb4
on tb2.id=tb4
where tb1.id=tb2.id
;

1026 views
2 replies, 4 years ago
How to use Column alias in Teradata Topic by yunfeizhao 10 Feb 2015

How to use Column alias in Teradata
My understanding is that we can use column alias in select, where, group, having,order by, and we can use it directly. Right?
Here is my example code below
Select 
sum(a) as asum,
asum+b as bsum 
from table
where bsum > 10

2967 views
2 replies, 4 years ago
How to get previous quarter total (SQL) Topic by ivot 10 Feb 2015 sql, teradata, partition by

Hello,
I have a table with Customer, Quarter, Month and Sales (montly) figures.
I need to calculate Quarter Sales and Previous Quarter Sales per Customer, while also showing Montly sales (see desired table format below).
For quarter sales I use sum() over partition by

1730 views
3 replies, 4 years ago
Parallel Loads to same target table Topic by Noman Riaz 04 Feb 2015

hi Guys,
 
i have target dimension table DIM_OUTLET which we are loading with TPT on upsert mode.
we have 10 different data sources which are loading data to this dim_outlet.
we want to update this Dimension in every hour, but the bottleneck is that this table get stuck in Mload as more than one source try to load data in this table.

1674 views
4 replies, 4 years ago
Using Case When on Teradata Topic by wiyanaananta 01 Feb 2015

Dear Master,
 
I have data 
Date,Col1, Col2
20150101, A, 23
20150101, B, 20
20150102, A, 19
20150102, C, 5
 
If I want get result
Col2, 20150101, 20150102
A, 23, 19
B, 20,
C, ,5
 
How to using "case when" on teradata? I confiused when its work for join col2
 
Thank you before,
 

895 views
2 replies, 4 years ago
row concatenation for dummies? AKA oracle wm_concat() teradata equivalent Topic by mphipps 10 Feb 2011

I've been looking for a solution to this, and I found this: http://forums.teradata.com/forum/enterprise/concatenating-different-values-in-a-field but I'm just not able to figure out how to make it work. I'm a complete noob at teradata, and I've never used a recursive query before.

I've seen the examples where you do a cast() against a known number of child values, but that isn't possible- I don't know how many child values are out there.

Here's what I'm essentially trying to do:

12003 views
11 replies, 4 years ago
Select Failed 3706: Syntax error: expected something between the word 'sasnref' and the word 'EXCEPTION' Topic by wiyanaananta 12 Jun 2013

I am troubling over this error as I am not sure what the cause might be. Any help would be greatly appreciated.
 
select sasnref.trx_date, count(OCS.SERVED_MOBILE_NUMBER)

from

(

select sasn.trx_date,  sasn.SERVED_MOBILE_NUMBER

from

(

5563 views
2 replies, 4 years ago
standard deviation function with parition by Topic by fox 20 Jul 2006

I want to use the STDDEV_SAMP() with the OVER (Partition by) clause.Teradata's standard deviation functions do not allow this.

32701 views
8 replies, 4 years ago
Syntax error due to Stored Proc Parameter Topic by zskuza 07 Jan 2015 stored procedure, Syntax Error 3706

The following procedure can be called with no errors when the Procedure input parameter is not used in the WHERE clause, but gives the following Syntax error when the input parameter is used:

 

3706: Syntax error: expected something between a string or a Unicode character literal and the 'AT' keyword.

 

Can someone help?

 

1413 views
8 replies, 4 years ago
create SDC from a history table Topic by aamer123 08 Jan 2015

Please help me convert the data FROM table to To table
FROM: 

817 views
2 replies, 4 years ago
Please help : Query to count number of voucher cards used Topic by tsukmana 04 Jan 2015

Hi All,
I am still new in Teradata.  Is anybody can help  how to write SQL to count number of voucher cards have been used.
I have two tables :
table A : contain Sequences of voucher card sold to market
From_seq_nbr        to_seq_nbr
------------------    ------------------
100                        999
5000                      6000
etc..

834 views
2 replies, 4 years ago
865 views
7 replies, 4 years ago
-----Aggregate Designer---- Topic by Raja_KT 11 Nov 2013

Hi,

What are the advantages of having Teradata OLAP Connector,Aggregate Designer, Schema Workbench when I can use other reporting tools that come handy with all features, including middle tier OLAP server?

I know only performance and data availability.

 

Thanks and regards,

Raja

2035 views
5 replies, 4 years ago

Pages