All Forums

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

You must login to post to the forums.
Showcase query: most recent check dates Topic by swildman 13 May 2013 ibm, showcase, query

I am building a query to show VendorID, Check#, and CheckDate
Vendors have many check numbers  and many check dates, I just want to pull the most recent check number and date per given vendor. 
Help?

2497 views
1 reply, 6 years ago
Row counts for every table in a given database Topic by JustMe 02 Feb 2011

Is there a macro or stored procedure anywhere that gives row counts for every table in a given database?

23857 views
9 replies, 6 years ago
difference between distinct and group by Topic by anadi.bhalla 26 May 2009

Hi,When we fetch unique rows from a table which is better distinct or group by??

24046 views
4 replies, 6 years ago
equivalent of rownum in td Topic by JACKSON 08 May 2008

Can you tell me what is equivallentof rownum in teradata in oracle I can useselect rownum,ename from empWhat is its equivalent query in teradata?

41951 views
6 replies, 6 years ago
Convert Text to Proper Case is huge problme Topic by nguyent02 06 Mar 2008

I need your help so badly, i have a requirement to convert text into proper case such as following:RAW data City Name to City NameWESTMINSTER CITY Westminster City (Upper case W and C)COSTA MESA CITY Costa Mesa City (Upper C, M, and C).Please teach me some trick here.... please.... Plese

42146 views
10 replies, 6 years ago
Difference between Timestamp Topic by GopiKrishnan S 17 Apr 2009

Hi,Can some one help me how to calculate the Difference of Timestamp fields/columns?Thanks in AdvanceGopi

52265 views
6 replies, 6 years ago
Using parameters in a derived table Topic by GrahamS 28 Mar 2013 derived table, parameters, ssrs

Hello,
I am trying to use SSRS to pass parameters into a derived table in Teradata. The parameters represent a SQL Server table that is about 1600 rows. 
The join I'm trying is
 JOIN (Select ? (varchar(4)), ? (varchar(4))) as derivedtable (Col1Name, Col2Name) on (Col2Name=TDcol)
 

1782 views
0 replies
How to import data from Excel file Topic by milulove04 04 Dec 2007

Hi all,I need to import data from Excel file in Teradata.Can you help me?Thanks,Anthony

74477 views
5 replies, 6 years ago
Timestamp Topic by rao_jagannath 21 Mar 2013

In teradata, how to get the timestamp up to 9 digit miliseconds?

1615 views
1 reply, 6 years ago
Please give syntax of scalar subquery to join three tables Topic by macktd 13 Mar 2013 scalar

Please give syntax of scalar subquery to join three tables
regards,-mack

2651 views
2 replies, 6 years ago
conversion of datatype Topic by bhaskaran 01 Nov 2006

Need help for the below questionI have numerical column, but that has been changed to VARCHAR(21)table structure:table x(xyz integer,abc varchar (21) -- this was previously a numeric column with dec(18,2))I am selecting the values from the above table and inserting it into a target tabletable y(xyz integer,abc varchar(50))Insert into table ySel xyz,sum(abc)Fromtable xgroup by 1;When I populate the table y the value has been populated in exponential format.Example:value from sel is : 41900000where as it is populated as 4.19000000000000E 007 Kindly let me know as how to solve this problem

45983 views
9 replies, 6 years ago
How to not to skip a RANK ? Topic by KS42982 05 Feb 2013

Hi,
I have a table with store #, item # and the sales with billions of records. I need to rank by the sales to find out the top 100 selling items. I can use the RANK function and find it out. However, the issue is, user wants top 100 items without skipping a rank.

3580 views
7 replies, 6 years ago
ADS installation Topic by EJ186001 07 Feb 2013 sas, ads

excerpt from Starbucks ADS user:
I installed a newer version of ADS, and then tried to revert back to the version all other Starbucks users have installed: 5.3.1.  Those efforts have failed.

1898 views
0 replies
[PROBLEM] Teradata ADS Generator... Topic by karmakoma 27 Feb 2007

Hi all, i have just installed teradata ADS generator (5.0.x.x) and i have tried to connect it by ODBC, with the PROD machine...but i have always the same problem "The user does not have any access to DBXYZ.yyyxxx", where DBXYZ is a prod db on the prod machine and "yyyxxx" is a table of this DB...SO i have to lel u know that i have also installed Teradata Admin by which i use this db normally, but i need to set at first the role ("set role all")...so i have to contact the administrator DBC to make the role setting "all" default by every ODBC connection?? or i need to set some parameter on ADS Gen.???Please seniors make light on my troubles

23391 views
3 replies, 6 years ago
Using different date formats Topic by adityasoni26 23 Jan 2013 date conversion

I see that my column is in the date format 'yyyy-mm-dd'. Would it affect processing speed if I were to compare this column with another column or parameter in the format CYYMMDD?

2378 views
2 replies, 6 years ago
Error Code 4 Solution required Topic by vital_parsley 28 Dec 2012 error code 4

Hi i have written a script in bteq .
The queries execute fine but at the end the scripts exits with below error.
 
*** Warning: EOF on INPUT stream.|
*** BTEQ exiting due to EOF on stdin.|

*** Exiting BTEQ...|
*** RC (return code) = 4 |

6667 views
2 replies, 6 years ago
Date and format Topic by Malvi Jaggi 19 Dec 2012

Hi,
 
This is the o/p of the show table salary :
CREATE MULTISET TABLE FINANCIAL.salary ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      FIRST_NAME VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,
      LAST_NAME VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,

4924 views
3 replies, 6 years ago
Random values Topic by mad_about_coffee 05 Jun 2009

Hi,I hope somebody can help me with my task. We are currently in the part of transitioning from oracle to teradata, and the problem that I currently have and which I really cannot identify is on how to produce random values in teradata the same way it was produced in oracle. Confusing but I have copied the results that I wanted to produce below: ORACLE code:dbms_random.value(0,1) as random_nbr TERADATA code:random(0,1) as random_nbr RESULTS:--------ORACLE--------vs-------TERADATARow1: 0.546476429------------------1Row2: 0.095457194------------------0Row3: 0.254218001------------------1Row4: 0.054972349------------------0Row5: 0.978196729------------------1 Thank you sooo much in advance, hoping that I can get the same result as the oracle's!

13421 views
5 replies, 6 years ago
All virtual circuits are currently in use Topic by tegrof 23 May 2007

Hello, (prepare for length..sorry)(background of problem)My company has recently taken on a client which uses Teradata as their data solution. We have a number of ASP.NET applications which we use to display and modify data throughout.Our goal is to have minimal changes required to our applications when porting from one client to another. We had acheived this for switching the applications between Oracle and MSSQL clients. Our connection pooling uses basic .NET objects for those two databases, but being that there was not an included TD object in .NET, we went with an OleDB connection. We had tried the TD .NET provider provided on the Teradata site, but this caused issues in the apps where it was not installed (oracle or mssql servers) because it was bound to the application after compilation.I had gone through all the applications and altered my queries where required, created the OleDB connection pooling to match the other methods and tested locally on my TD installation. I had no problems here. After implementing the applications on our TD client, we have been receiving the "All virtual circuits are currently in use" error after using the applications for a short time. This usually takes about 20-30 minutes to clear up on it's own, but will clear up immediately from an IIS restart.Their DBA simply says that our applications are not freeing up connections well enough. I mildly agreed and added some further clean up of the objects used to connect, except the connection pool, which maintains 3 connections at all times. This pool will create an extra connection if the three are in use, but immediately close and remove it after it's used. I do not have any sort of problem like this in either Oracle or MSSQL and am at a loss.Again, I can not reproduce the problem locally. Is there something that I do not know about TD and .NET? Could connection pooling be a problem with TD? Any help would be greatly appreciated. I am very new to TD and find it quite hard to find any information online that helps.Thanks,James

43508 views
7 replies, 6 years ago
Difference Horizontal data redistribution and vertical data redistribution. Topic by abdulmd 05 Dec 2012

Could some one explain what is the difference between horizontal data redistribution and vertical data redistribution ?

2697 views
0 replies
all-AMPs STATS FUNCTION Topic by SANJI 04 Dec 2012 skew, all amps stat function

I am trying to understand the following steps from an explain of a massively skewed query.
The "no confidence" is because of the calculations and functions applied to the columns.
Would appreciate if someone could correct my understanding.
It's a 2650, 219 amps system.

4158 views
0 replies
hashrow function Topic by teranerd 30 Jun 2010 hashrow

I did this

create volatile table rowhash_my,no log
(
rowhash_col varchar(20)
)
on commit preserve rows

insert into rowhash_my values ('1234_#abcd@gmail.com')

Now i am trying to calculate
select hashrow(rowhash_col) from rowhash_my
union all
select hashrow(cast('1234_#abcd@gmail.com' as varchar(20)) from rowhash_my;

17371 views
9 replies, 6 years ago
Reducing runtime for a MLOAD job Topic by Sandi 10 Oct 2008

Hi All,I have a monthly MLOAD job which loads around 26 millions of records, but it takes 16 - 20 hours to complete loading. Can someone give some tips to improve performance and reduce runtime? PS: I don't prefer changing MLOAD to any other utilities.Thanks a lot for your time and help

47018 views
11 replies, 6 years ago
How to read only two rows at a time by ordering it Topic by prakash5801 04 Jun 2010 general udf, sql assistant, analytics

HOw could I take an average of two values by arranging the data in ascending order using SQL. For example

For the Col "Var1" with data

TrackId Var1
1 1.2
2 1.1
3 1.5
4 1.6
5 1.3

11492 views
2 replies, 6 years ago
Failure 2805 Maximum row length exceeded in table in abc in BTEQ when using SET operator. Topic by sudeep_j 15 Nov 2012

Hi,
 
I have been trying to compare results of two queries using minus but i am getting the below error
Failure 2805 Maximum row length exceeded in abc
 
The query i'm using is
select * from abc
minus
select * from bcd
;
I found that UNION ALL works fine, but other operators like MINUS,UNION and INTERSET doesn't work.

4744 views
5 replies, 6 years ago

Pages