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
|
220 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
|
128841 views
|
16 replies,
5 years 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.
|
9920 views
|
12 replies,
5 years 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
|
30402 views
|
3 replies,
5 years 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
|
288 views
|
9 replies,
5 years 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
|
46283 views
|
6 replies,
5 years 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.
|
217 views
|
2 replies,
5 years 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
|
60755 views
|
5 replies,
5 years 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
|
134 views
|
1 reply,
5 years 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
|
180 views
|
1 reply,
5 years 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
|
245 views
|
4 replies,
5 years 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:
|
298 views
|
2 replies,
5 years 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
|
4190 views
|
4 replies,
5 years 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
|
239 views
|
1 reply,
6 years 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
|
1450 views
|
2 replies,
6 years 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.
|
338 views
|
5 replies,
6 years 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
|
502 views
|
2 replies,
6 years 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
|
416 views
|
1 reply,
6 years 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:
|
595 views
|
4 replies,
6 years 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'
|
267 views
|
3 replies,
6 years 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.
|
406 views
|
2 replies,
6 years 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)').
|
2591 views
|
8 replies,
6 years 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!
|
25329 views
|
3 replies,
6 years 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"],
|
235 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
|
220 views
|
0 replies
|
Pages
|
|