All Forums

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

You must login to post to the forums.
OLAP SQL - running count/total Topic by deven_rk 05 Sep 2016

Hi All,

 

We have the below data and using an SQL not procedural code, I would like to get the output as given below for 'out_count', 

it would help if we could do it using OLAP functions with Preceding and Following keywords, thank you.

 

cal_date, holiday_flag, out_count

'01-Aug-2016', 0,0

'02-Aug-2016', 0,0

38 views
0 replies
IS-NUMERIC functions Topic by flaska 06 Jul 2006

Hello,this is my problem:I need to find out with a query, all the non numeric charaters in a field. The field is a varchar but should contain only telephone number, does exist a ISNUMERIC funtions????select * from table where not field is numericThank you regards

128177 views
16 replies, 1 year ago
Query_Discussion_HELP Topic by eejimkos 02 Mar 2012

Hello,

 

First of all thanks for your time.

I am very new in Teradata and I would like your opinion about this query.. (= execution plan)

The query just joins 10 times my fact table(1 Billion rows).The dimension tables are really small.

9739 views
12 replies, 1 year ago
Release Lock from a Table Topic by ANIMESH.DUTTA 04 Aug 2009

I'm selecting rows from a table and then Inserting a row to that table.But after selecting - the table get locked - I don't know what kind of lock!And the Insert got hanged - neither failing nor showing any errors.If I abort that Insert - and going from another select - the query got hanged - means locked table!!!What should I do to release lock from a table - and why it's getting locked whenever select is happening?Any Idea - please help....If I do RELEASE LOCKING TABLE_NAME - showing "The USER doesn't have DUMP or RESTORE access to Table_Name"BT;SELECT * FROM TABLE_NAME;.IF ERRORCODE <> 0 THEN .GOTO ERRORS.IF ACTIVITYCOUNT <> 0 THEN .GOTO LOADFAILINSERT INTO TABLE_NAMEVALUES( VAL1);.IF ERRORCODE <> 0 THEN .GOTO ERRORSET;.LOGOFF;.QUIT 0.LABEL ERRORS.QUIT ERRORCODE.LABEL LOADFAIL.QUIT 100In this Insert is not happening - even after this simple select also not happening... though only the following select is running... But No Insert....LOCKING TABLE TABLE_NAME FOR ACCESS NOWAITSEL * FROM TABLE_NAME

29952 views
3 replies, 1 year ago
QUERY IN ASTER Topic by zakyw 21 Jul 2016

Hi Guys 
I'm Zaky 
In here , i've a problem with query in aster , because this the first moment for me , 
so , my question is , can you give me references or tell me how to example type of query in the aster ? 
 
thanks a lot , sorry if my english are less 
 

122 views
9 replies, 1 year ago
Coverting Char to Date Topic by roy_soumya 16 Apr 2009

Hi,I am new to Teradata.In oracle if we want to convert char to date, we write like this e.g. to_date('20090417','YYYYMMDD').Could you please tell me how to do that in teradata.Thanks & Regards.Roy

46158 views
6 replies, 1 year ago
How to create churn data for customers by transaction in month? Topic by basshead 20 Jul 2016

So I have created a table that has the following columns from a transaction table with all customer purchase records: 1. Month-Year, 2.Customer ID, 3. Number of Transactions in that month.

81 views
2 replies, 1 year ago
MAX() and MAX() OVER (PARTITION BY ....) in the same query produces error 3504 Topic by dneaster3 22 Jun 2010 aggregate functions, max()

I am trying to produce a results table with the last completed course date for each course code, as well as the last completed course code overall for each employee. Below is my query:

SELECT employee_number,
MAX(course_completion_date) OVER (PARTITION BY course_code) AS max_course_date,
MAX(course_completion_date) AS max_date
FROM employee_course_completion
WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
GROUP BY employee_number

This query produces the following error:
3504 : Selected non-aggregate values must be part of the associated group

60393 views
5 replies, 1 year ago
Replacing duplicates while transpose in Teradata Topic by Karthikeyan_dsk 20 Jul 2016

Hi,
I have two tables as below.
TBL_RULES

TBL_NM

VLD_ID

COL_NM

TABLE1

1000

COL_1

TABLE1

1001

COL_2

TABLE1

1002

COL_2

TABLE1

1003

COL_3

TABLE_LOG

TBL_NM

VLD_ID

REC_NBR

36 views
1 reply, 1 year ago
Replacing missing value with next vaules Topic by ssinha 06 Jul 2016

I have a data like
Attmpt_dt
1/1/2016
?
?
?
?
1/10/2016
?
?
?
1/31/2016
 
 
I want it to covert 
 
 
Attmpt_dt
1/1/2016
1/10/2016
1/10/2016
1/10/2016
1/10/2016
1/10/2016
1/31/2016
1/31/2016
1/31/2016
1/31/2016

69 views
1 reply, 1 year ago
Help extracting select time period records ( rank / any other way) Topic by sri67 08 Jun 2016

Have the following records,

COLA COLB COLC 

AA   11   10:00:00

AA   11   10:01:00

AA   12   10:01:20

BB   11   10:00:00

BB   11   10:02:00

BB   11   10:03:10

BB   12   10:03:35

AA   11   11:00:00

AA   11   11:01:00

AA   11   10:02:00

AA   12   10:02:40

DD   11   10:02:40

135 views
4 replies, 1 year ago
CPU disk ratio error from 'like' statements Topic by snreyes 21 Jun 2016

We are parsing out URL strings in code and are receiving a CPU disk ratio error message.  Can someone help identify the major contributors to CPU disk ratio error?
 
The below are all included in case statements creating new variables.  For example:
 
CASE WHEN URL LIKE ANY ( '%help%',’%travel%’ ) THEN 1 else 0 end AS variable_travel
 
Questions:

101 views
2 replies, 1 year ago
Transpose rows to columns Topic by chill3che 20 Jan 2014 Transpose rows to columns

Greetings Experts,
Can you help me in the following scenario with the SQL without UDF's.
input:
sales_month    sales_amnt
jan                    100
feb                    200
mar                   300
apr                    400
may                   500
....                      ....
dec                   1200
 

4030 views
4 replies, 1 year ago
stored proc to insert multiple tables into single table Topic by TDMGUSER 18 May 2016

Is there a way to insert records from multiple tables (20) into a single table using a macro or stored procedure. This is a recurring activity.
 
Thanks

130 views
1 reply, 1 year ago
concatenate multiple rows Topic by salla.ravinder 06 Nov 2015

Hello All,
Below is the sample data of what I have.
ID      desc
1        AB
1        AF
1        DF
2        wer
2        try
3       qw
4        yu
I want to summarize at ID level and the result should look something like this.
ID       Summary
1         AB,AF,DF
2         wer,try
3         qw,yu
 

1323 views
2 replies, 1 year ago
Teradata - calculate KPI every month for last 24 months Topic by TDMGUSER 03 May 2016

Hello
At the end of every month I need to create a report calculating some KPIs for the last 24 months. For example, for april 2016, calculate KPI1, KPI2,KPI3 etc from '2016-04-30' to 2014-05-01'. Again for May 2016, calculate KPIs from '2016-05-31' to '2014-06-01' and so on.
Is there a way to do this in teradata recursively? Thanks.

 

219 views
5 replies, 1 year ago
Single row to multiple row Topic by adin.causevic 11 Jan 2016 convert, single row, multiple rows

Hello everyone
 
I would like some help to convert a single row into multiple rows
I have something like this
store | item | quantity
001 | A | 4
001,002 | B | 5
004,006,003 | C | 12
 
And i would like to do something like this
 
001 | A | 4
001 | B | 5
002 | B | 5
004 | C | 12
006 | C | 12

371 views
2 replies, 1 year ago
Splitting of one to may rows using date range with the help other table's date range columns. Topic by sagarmokashi 04 May 2016

Hello every one, 
I want to split the rows of one table based on date range of another table..
If end_dt of 1st table is greater than end_dt of 2nd table then end_dt should be the second table's end date and then split the records monthwise
as given in the below example.
e.g. I have 2 tables as below
Table 1
ENROLLMENT_VERSION

272 views
1 reply, 1 year ago
Extract numeric values from string Topic by AndreyAlex 29 Apr 2016 REGEXP, data type, numeric

Hi!
I can't find solution, may be somebody can help me.
Example:

SELECT
'ABC123D666656-522815EF!@55#' AS COL1

I need to extract 666656-52281. But other string chartes can be different. Also can vary their number. I need XXXXXX-XXXXX (where X=[0-9]) from any kind of text.
I stoped on this step:

212 views
4 replies, 1 year ago
Stored procedure conversion oracle to TD Topic by Natu 19 Apr 2016

Hi All,
I am trying to convert the stored procedure from oracle to TD, below is the cursor used in Stored procedure 
 

CURSOR pnr_od_cur IS

      SELECT s.pnr_sk,

             REGEXP_SUBSTR(true_od, '[^;]+', 1, level) true_od_split

        FROM cdw_pnr_od_const_wrk s, cdw_od_pnr o

       where processed = 'Y'

142 views
3 replies, 1 year ago
Trying to ROUND UP Topic by dbmagnus 30 Mar 2016 rounding, Round

I am having a problem with Rounding.  I am subtracting two dates, then dividing by average months in a year.  But Teradata is giving me an integer and is rounding down.  For example, if I’m subtracting 9/9/15 from 2/1/16, I get 145 days, which divided by Avg.

201 views
2 replies, 1 year ago
Passing a string into a macro for use in a IN statement Topic by jimturn7ts 09 Jan 2015

Hello,
 
I've set a macro which i'd like to pass a string into to use in a in statement.
 
So for example.  my query code snipet reads -
where item_nbr in :my_string  (where item_nbr is a decimal)
and i am passing in
exec my_marco(my_string='(1,2,3,4,5)').

2327 views
8 replies, 1 year ago
Teradata Value Analyzer - Activity Based Costing? Topic by Flora 16 Jul 2007

Hello,I'm a new member here and don't know much about Teradata applications. For that reason, I would like to know if there is a possibility for Activity Based Costing / Management in Value Analyzer?Thanks to all of you who can give any comments!

25180 views
3 replies, 1 year ago
Having trouble using teradatar's td.tapply function Topic by blweeden 22 Feb 2016

Hello,
I'm having trouble using the td.tapply function right now.  All I get is a null result.  I can get (with different syntax) this concept to work with the regular tapply function on a regular dataframe, but not with the "td" function on a "td" dataframe.  Here's my R syntax:

my_array <- td.tapply(sops["A"], sops["B"],

130 views
0 replies
Increase Performance of ADS Topic by lukelake 04 Feb 2016

Hi,
Configuraton of the machine is out of my control. So how can I increase the performance of ADS and other alogorithms please?
Ta
Luke Lake
 
 
 
 
 
 

118 views
0 replies

Pages