All Forums

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

You must login to post to the forums.
airlines query - cross value duplicate elimination Topic by satyanarayanatera 22 Nov 2014 cross value duplicates elimination

Hi Experts, Can you please help me logic on below -
a table has source , destination and distance.
There are some rows where destination was already there as source in some other record with source as destination. both records has same distance. (i.e., There are records with "to and from" and "from and to" both with same distance)

939 views
2 replies, 4 years ago
HOW ? Vertical Merger Turn Standard SQL? Topic by andersom 18 Nov 2014 HOW ? Vertical Merger Turn Standard SQL?

HOW ? Vertical Merger Turn Standard SQL?
1 AAAA 1030101
1 BBBB 1030101
1 CCCC 1031201
1 DDDD 1030901 
--------------------------------------
1 AAAA 1030101,BBBB 103010,CCCC 1031201,DDDD 1030901
 

841 views
1 reply, 5 years ago
NTILE Function in TOAD Topic by Parvathi.patnaik 12 Nov 2014

HI - I'm trying to create deciles by using NTILE function in TOAD using the below syntax.
Code :
 SELECT      *,
            NTILE(10) OVER (ORDER BY SERVER_PREDICT) AS [Partition]   FROM [dbo].[scores]
Error :

1436 views
1 reply, 5 years ago
meaning of " ('0A'X) is not a valid Teradata SQL token. " Topic by NMadson-3173 02 Jan 2007

In writing a stored procedure I received the above error message. I can't find a single reference to either "valid token" or "'OA'X" in any of the V2R6.1.x documentation on NCR.com.Does anyone have any ideas as to what this might mean?Nolan

41228 views
5 replies, 5 years ago
Bteq script error handling not working Topic by archanag 09 Oct 2014

bteq <<EOF > $LogfileName 2>&1
.logmech LDAP;
.logon $SRVR/$ID,$PSWD;
.set errorlevel on;
 
SELECT
STATUS FROM
(
SELECT status,start_date,END_DATE, RANK() OVER ( PARTITION BY CAST( END_DATE AS DATE FORMAT 'MM-DD-YYYY') ORDER BY END_DATE DESC) RNK
FROM MKT.MARTTRANSACTION WHERE REPORT_MART_NAME='x'

1531 views
2 replies, 5 years ago
Overlapping Date SQL Topic by kkoontz 08 Nov 2007

I need some help with SQL to deal with overlapping dates. Here's the table I have
 

Customer ID Communication ID Start Date End Date
100     9     1/1/2006   3/30/2006
100    10    2/10/2006   4/10/2006
100    11    5/10/2006   6/30/2006
200     9     1/1/2006   3/30/2006
200    12    1/15/2006   3/15/2006
300     9     1/1/2006   3/30/2006
400     9     1/1/2006   3/30/2006
400    10    2/10/2006   4/10/2006
400    12    1/15/2006   3/15/2006

I need counts of customers with counts of the communication IDs where the start date and end dates overlap.

Results from above would be
 

count of customers   count of communication IDs
   2    2
   1    3

Thanks!

Thanks!

40232 views
4 replies, 5 years ago
Output to be concantenated & transposed Topic by RanjaniKartik 29 Sep 2014

Hello Gurus,
 
We are on TD14.0 and do we have any function that can derive the below o/p
 

Group

Product

1

A

1

B

1

C

2

B

2

D

 
I want to have the output as
 

Group

Prod_Lst

1

933 views
3 replies, 5 years ago
Merge a variable number of rows with one column, into a single row Topic by dfranks44 20 Aug 2014

I am trying to merge a variable number of rows with one column into a single row.  I find examples all over the place ordering by an id or some other value, but what if you don't care what the order is.  You just the rows all flattened into one row regardless of order.
Basically I am trying to get the following
column1
Red
Blue
Green
 

1606 views
3 replies, 5 years ago
Teradata OLAP functions in SQL Topic by Sue73 18 Sep 2014

Hello all,
I need your advise on writing the following query in a more efficient way using OLAP functions like sum() over partition by. Instead of using 2 queries, can I use just one query to accomplish the same results?
btw, query p returns 2 rows and query q has 10 rows but the final result should render only 2 rows (with the common prd_id from the 1st query).

2971 views
5 replies, 5 years ago
Outlier Treatment in Teradata Topic by abhpathak 15 Sep 2014

Hi guys, 

1288 views
5 replies, 5 years ago
using "CONCAT" function in BTEQ causes 3706 Syntax error but works through SQL Assistant Topic by voleary 03 Sep 2014 concatenate, bteq, 3706, syntax error, concat

Here is the query I am running:

3714 views
2 replies, 5 years ago
Generate a automated Nested CASE STATEMENT using entries from a table Topic by manmarirama 28 Jul 2014

I have a situation where i am trying to automate a BTEQ script where it has 100ds of conditional statements like 

CASE WHEN

WHEN

CASE WHEN

 WHEN

CASE WHEN

 WHEN

END

WHEN 

END

WHEN

WHEN

END AS COLUMN1. 

4576 views
10 replies, 5 years ago
Total records from a Case when substring statement Topic by BankingExpert 18 Aug 2014

Hi,
 
Im attempting to create a TOTAL colums for the number of returns from each case statement for the below, but it keeps totalling all columns in the table opposed to just my case when statements, any ideas how to resolve this?
Select
TRANSACTION_DT AS "Transaction Date",

1035 views
1 reply, 5 years ago
Help with the query Topic by gayatri.patel 05 Aug 2014

Here is the query. i wanted to add logic - discount the same caller multiple times within a rolling three day period.

920 views
0 replies
Python in Database Topic by bobdoss 02 Aug 2014 Python in-database udf

This press release http://www.computerweekly.com/news/2240217675/Teradata-Database-15-adds-JSON-addresses-internet-of-things says Teradata 15 supports "in the core database for scripting languages Perl, Ruby, Python and R" 
It would be great to be able to do things in Teradata with Python like this: http://www.postgresql.org/docs/8.2/static/plpython-funcs.html

1263 views
2 replies, 5 years ago
Why subqueries in case are not working in teradata? Topic by JACKSON 08 May 2008

Please look at the query belowselect ename from emp e where not exists(select 'x' from dept d where d.deptno=case when d.deptname in (select deptname from xdepartments ) then 20 else 50 end );In the place of subquery "select deptname from xdepartments" , if I use hardcoded values like 100,200,3000 then it is working fine but I don't know why It is not working if I use subquery like above It's giving an error message..Please Help me out...I am using teradata SQL Assistant 7.1and getting Error message as : Illegal expression in when clause of case expression.Please help me outThanx Regards,jack

34093 views
15 replies, 5 years ago
For AMP Number HASHMAP()+1 Topic by Sankalp.C 10 Dec 2013

To find the number of Amps the query we use is -
Sel Hashmap()+1.
But why we add one saying "+1".

3074 views
6 replies, 5 years ago
Parameterizing Work, Log, Error database names in Informatica Topic by Rakesh.g 27 Sep 2012 teradata, teradata parallel transporter, work table

Hello,
I need help with this issue.

4578 views
2 replies, 5 years ago
Regarding Space Management In Teradata Topic by Praneet Kulkarni 19 Nov 2007

I am unable to Create the Table is Edwtest_Ads_Temp .but it is allowing to insert the new rows. I have checked the space .Still 879 Mb Space is available.And even no amp is full.I have used following queries to get the conclusion.1)sel sum(maxpermspace),from DBC.DataBaseSpace where Databaseid='00007408'XB;( '00007408'XB is Database id for the database I am referring) Result:=2122317760.00 Bytes 2) sel sum( currentperm) from DBC.TableSize where databasename='Edwtest_ads_Temp' ;Result= 1200550400.00 BytesSo Sum Of Current Perm Space is Less than Maxpermspace3)sel max(currentperm) from DBC.TableSize group by vproc where databasename='Edwtest_ads_Temp' ;In above query Max Of currentpermspace of each amp is less than the Maxpermspace/80(80 is number of amps)Amp is full only when it reaches =26528972=2122317760.00(Max Perm Space)/80Amp number which is occupied highest space is 50 I.e. 10132992.00 Bytescould you please clarify

56276 views
9 replies, 5 years ago
UV checks Topic by shashvat 29 Jun 2014

How to check error records from UV Table?

Hi,I am using MLOAD to load records into a table.The Mload rejects few records and makes the corresponding entries into the UV table.How can I find out which records have been rejected, so that I can fix them?None of the UV table columns give me any details about the actual record that has been rejected.Please help

 

801 views
0 replies
Qualify and group by statement conflict Topic by achalasawa 18 Jun 2014 Windowing function, qualify, group by

/*
Hi,  I am trying to run the below query, in vain. I was able to get the results by using a different logic.
However I am not able to understand why my query for search didn’t work, while my query for sales worked (they are both essentially the same query with different column names)
*/

3379 views
2 replies, 5 years ago
How to handle exceptions 'Call failed' in procedure? Topic by luckylh 11 Jun 2014 procedure, exception

Hi, All
I have a main procedure which is used to invoke other sub-procedures. I've added the 'DECLARE EXIT HANDLER FOR SQLEXCEPTION' in the main procedure. But when any exception raised in sub-procedure, the HANDLER in main procedure doesn't work. So how can I catch exceptions generated in all sup-procedures?

1139 views
2 replies, 5 years ago
Fast Load Vs Multiload Topic by Sankalp.C 16 Dec 2013

In case of ideal senario for FastLoad why do we consider Fastload instead of Multiload where both are Bulkload(64kb).
Correct me if I am wrong, the populare answer I got is is Fast Load use 2 phaze where Multiload consists of 5, which I really doubt.
Cause the  phase of Multiload -
- Initialization of Layout
- DML
- Acquisition
- Application

15862 views
12 replies, 5 years ago
3706: Syntax error: expected something between ')' and ','. For derived Topic by dsad 09 May 2006

Hi TD newbie here,am getting the 3706: Syntax error: expected something between ')' and ','.for the following querySELECT P.SRCSYS_CODE,P.SUP_NM,L.SRC_LOC_CD,L.LOC_NAME,D.DEST,COUNT(ITM_NUM),COUNT(ORD_NUM),COUNT(ORD_LN)FROM (SELECT ORD_NUM, ORD_LN, ORD_LN_ST,ITM_NUM, ORD_QTY,LOC_NAME AS DESTFROM ORD_DET A, LOC BWHERE A.LOC_ID = B.LOC_IDAND A.ORD_LN_ST = 'CLOSED') D,ORD O, PAR P, LOC LWHERE D.ORD_NUM = O.ORD_NUMAND O.PAR_ID = P.PAR_IDAND O.LOC_ID = L.LOC_IDGROUP BY 1,2,3,4,5 ;am not sure where the problem is?any help appreciated.

75440 views
16 replies, 5 years ago
Exit handling routine Topic by Tnewbee 08 Apr 2014

I have to drop tables looking at commentstring of the table. If the commentstring is not correct, I need to send an email out with the tablename.
Can someone help me with this error handling part?

1222 views
4 replies, 5 years ago

Pages