All Forums

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

You must login to post to the forums.
JSON Shredding Topic by dae 10 Jan 2016

Hi,
I am trying to shred a JSON field with "nested array" but I am not able to perform that operation (below is the source code with the result I would like to get and the results I got).
Any kind of help would be greatly appreciated,
Thanks a lot, Didier.

297 views
0 replies
Looking for broken views in a database Topic by DaniGn 18 Dec 2015

Hi.
I have one database with several view, the problem is that i want make a script for see if some of  the 1700 views are broken, i see one article by Rob Paller "http://robpaller.com/archive/2012/09/finding-broken-views-in-teradata/" and the first query give me an error."the query is invalid"
I am using TD 14.10 how server.
 

236 views
1 reply, 3 years ago
Failure 7453 Interval field overflow Topic by kuldeepsingh 02 Aug 2006

I have a teradata procedure -- Cheking that FULFILLMENT_LINE has finished updatingsel ((Current_Timestamp - Process_Timestamp) hour(2) to second(6)) TIMEPROCfrom EDWPROD.ETL_Control_Table WHERE Process_Timestamp in (sel max(Process_Timestamp) from EDWPROD.ETL_Control_Table WHERE Target_Tablename = 'FULFILLMENT_LINE' ) and Target_Tablename = 'FULFILLMENT_LINE' AND extract(MINUTE from TIMEPROC) <= -60 AND extract(HOUR from TIMEPROC) = 0;.if errorcode <> 0 then .exit 8.if activitycount <> 0 then .exit 8that returns with Failure 7453 I am not sure what happened. Everything was fine until about two hours back. I am not sure how to proceed and fix this. I looked at the max and min for the process_timestamp and do not show anything abnormal. Any help would be appreciated. When I just do the first part of the sql,sel ((Current_Timestamp - Process_Timestamp) hour(2) to second(6)) TIMEPROCfrom EDWPROD.ETL_Control_Table I get the same error,When I do,sel ((Current_Timestamp - Process_Timestamp) hour(2) to second(6)) TIMEPROCfrom EDWPROD.ETL_Control_Table WHERE Process_Timestamp in (sel max(Process_Timestamp) from EDWPROD.ETL_Control_Table WHERE Target_Tablename = 'FULFILLMENT_LINE' )it returns,TIMEPROC -0:29:14.200000 -0:29:14.200000 -0:29:14.200000 -0:29:14.200000 -0:29:14.200000 -0:29:14.200000 -0:29:14.200000 -0:29:14.200000 -0:29:14.200000 -0:29:14.200000I am not sure why the criteria after that might be causing an issue. Any help would be appreciated.Thanks,

44164 views
13 replies, 4 years ago
TeradataR Question Topic by gbindra 21 Sep 2012 analytics, r, teradatar

Hi,
 
I am trying to run some functions in R use TeradataR. IT seems like some of the funcitons does not work or limit the number of rows to top 50. HEre is one of the error which I am receiving 

3702 views
2 replies, 4 years ago
RANK() Tiebreaker? Topic by Eady 24 Oct 2015 #sql
CREATE VOLATILE TABLE MYTBL  
 (
  PERIOD_START DATE NOT NULL,
  PERIOD_END DATE NOT NULL,
  EMPID INT NOT NULL,
  LOCID INT NOT NULL,
  LOCNME VARCHAR(50) NOT NULL,
  LOC_VST_DT DATE NOT NULL
 ) ON COMMIT PRESERVE ROWS;

INSERT INTO MYTBL VALUES ('2015-01-01', '2015-01-31', 12345, 98765, 'PLACE1', '2015-01-10');
522 views
1 reply, 4 years ago
What, no REPEAT(X,n) function for Teradata? Topic by wood0323 19 Nov 2007

Hello,I wants to log some simple ascii bar charts.e.g.5 *****6 ******9 *********3 ***2 **1 *every other db has either a REPEAT, REPLICATE, RPAD funtion to get the job done.any suggestions for TD?

28425 views
5 replies, 4 years ago
How strip of the non numeric data from VARCHAR field Topic by prudhviy2k 31 Oct 2012

Hi,
 We have a situation like we need to strip of non numeric values and collect only numeric values from VARCHAR(30) field. 
Below is the sample data and VARCHAR field can have any of the below values
1) A12345
2)AB12345
3)AB-12345
4)ABCD12345

7946 views
7 replies, 4 years ago
Teradata In-database R and Python Analytics with the SCRIPT Table Operator Topic by Alexander K 03 Apr 2015 r, python, script, Table Operator, analytics, in-database, programming

The R and Python programming languages are very popular for statistical and mathematical tasks, and notable examples of open source software for analytics. Both are enjoying continuing development by means of new packages that add to specific functionality, and both have been gaining significant popularity and influence outside their traditionally academic boundaries of application.

4492 views
2 replies, 4 years ago
Finding MODE of multiple variables Topic by barthouston 26 Aug 2015

Hi Experts,
How do I find the MODE for multiple varaiables?
For example,
SELECT a, b, MODE(d), MODE(e), MODE(f)
From tbl
group by??
 
Thanks!
Carey

735 views
0 replies
Analytical Output - grouping Topic by Ind73 20 Aug 2015

Hi,
I have provided below the sample data (section 1), for which we need to get the output given underneath that (section 2). You help would be greatly appreciated.
Section-1

887 views
1 reply, 4 years ago
Trend analysis Topic by Tnewbee 17 Jul 2015

Hi all

1074 views
1 reply, 4 years ago
SparseSVMTrainer Topic by Nishantha.Hetti 27 Jul 2015 SparseSVMTrainer Pegasos

I have been going through the Aster_Analytics_Foundation_Guide_0511 document to understand how exactly Aster SparseSVMTrainer pegasos algorithm works. I have been trying to rewrite the SparseSVMTrainer's Pegasos algorithm in R, but haven't yet been successful.
Could anyone, interprit
1)the definition of loss function used in here?  

597 views
0 replies
Transpose Rows to columns with 1....N columns Topic by dmbass 08 Aug 2011 row, recursive sql, columns, transpose

In the past I have used Recursive and Rank Partition By to convert rows to columns. Now I have a new situation. I have data:
AccT_id Cust_id
10XXXXX1 85314587
10XXXXX1 92458714
5XX0XXX5 2587452
5XX0XXX5 4258702
5XX0XXX5 54782142
5XX0XXX5 67842521
5XX0XXX5 85745872

I need to place the cust_id into a colmun as:
Acct_id Cust_id1 Cust_id2 Cust_id3 Cust_id4 Cust_id5 Cust_id(N)
10XXXXX1 85314587 92458714
5XX0XXX5 2587452 4258702 54782142 67842521 85745872

20720 views
3 replies, 4 years ago
week over week growth amount query Topic by cca8380 22 Jul 2015

Can anyone show me an example of a week over week growth amount query. My table layout is below.
Table Name:Sample_Table
Columns: Date, Product, Region, Sales
I need to generate a query that would give me daily sales growth over previous day.
 
Thanks
 

649 views
0 replies
Teradata & RapidMiner Topic by eddie.baldo 12 Sep 2012

Hello,

4069 views
1 reply, 4 years ago
Loss of Precision in LEAST() Function Topic by StWallace 07 Jul 2015 LEAST, greatest, precision, LEAST FUNCTION, greatest function

Hello,
I am trying to use the LEAST() function to take the minimum of several decimal arguments, but the return value is rounded to the nearest 1. Is there a way around this?
Thanks,
Stephen

1225 views
2 replies, 4 years ago
Cast Seconds (Data type HS) too Hour:Minutes:Seconds Time data type Topic by Johnmghenry 29 Apr 2015

I have a time stored in HS format. So the time will look like '0 20:50:00.000000'.
I need to convert this to the data type time.  When I simply try to 

1508 views
1 reply, 4 years ago
"Query is Invalid"error Topic by deepanker.anand 30 Jun 2015

Hi All,
 
When i execute following sql query using ODBC (Teradata SQL Assistant), it tells me query is invalid, but i am not sure what is incorrect here. please help.
 
view table tablename;
 
Note:
I want to display all the columns of a table.
 

1395 views
1 reply, 4 years ago
Teradata - need retention data for all DBC.TABLES Topic by fshpige 17 Jun 2015

Hello,
I am new to SQL/Teradata so please forgive me if this is a dummy question. 
I've been asked to write SQL that will report retention period ( along with number of columns etc) on all DBC.TABLES.
I've put together an SQL to best of my ability to display this information for one table but I need to be able to do it for all the tables.

1316 views
1 reply, 4 years ago
can we have Rank function using rows between 1 preceding 1 preceding Topic by keyabhatt@yahoo.com 18 Sep 2014

Hi,
Userid , date ,channeltype and want to create seq (rank)
A      1/jan/2014   email    1
A      2/JAN/2014    chat      2
A      3/jan/2014      email   3 
A       10/jan/2014     email   1 -- since the previous record date and current record date difference is > 72 hr  

2944 views
3 replies, 4 years ago
Finding common connections Topic by nehcil2003 05 May 2015

Hi, I'm trying to solve for a question where I need to find common connection between May and Frank in a database containing million rows. The data is presented in the way below.  How to sollve for finding the common connections between May and Frank? Thanks!

981 views
0 replies
REGEXP_SPLIT_TO_TABLE doesn't work Topic by Qionglinnewbie 07 Apr 2014 REGEXP_SPLIT_TO_TABLE, 3706 : 42000

Dear All,
 
I'm trying to parse a string into small pieces using REGEXP_SPLIT_TO_TABLE, but after trying different syntaxs I still cannot find out how it works. Can you please have a look and tell me what's wrong?

query :

SELECT TD_SYSFNLIB.regexp_split_to_table('bnp,&abc&,dbc,@dbc@,NULL', ',', 'i');
 

4123 views
9 replies, 4 years ago
Find average time of day Topic by Johnmghenry 25 Apr 2015 timestamp sql

I have code with hours of day. I want to find the average of day but if one time is hour 22 another is 2 then the average with be 12 noon but I want to get 0 midnight as it is the closest time between the two hours. How do I find the average and take midnight wrap around into account???
 
select purchase,

1795 views
5 replies, 4 years ago
Error Number : 50612 Error Message : Cannot create process with image file Topic by Noman Riaz 24 Apr 2015

hi Fellows,
 
recently we have started getting following erros in our ETL jobs. we are using data services 14.2.1.568 as ETL tool:
Error Number : 50612 Error Message : Cannot create process with image file <> and command line <F:/Teradata/BulkLoader/DS_TERADATA_DP_EDW_DIM_OUTLETLOCATIONTEXT_122.temp.bat>, due to error <0>.

961 views
0 replies
if and else condition in bteq Topic by vazeer 23 Jul 2008

hi , can any one help me,, how to use if and else condition in bteq,,,

27337 views
3 replies, 4 years ago

Pages