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?

Hi all!

I want to optimize this query given below. There are 50 such queries and each query takes about 2 min. These queries are auto-generated and are dependent on the user interface.
So if the user logic changes these queries can get updated,deleted or added.As of today there are 50 such queries. Each of this query is joined
with the 'BIG' table which has 2 million records. Also this part of the query(SELECT DISTINCT FLD1, NEWFLD, FLD3 FROM DB.RUN2 WHERE

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

Hi All,
 
I am new to Teradata and trying to get my connection from R to work... I am running R 3.0.0 and installing the package through 
install.packages("~/Downloads/teradataR_1.0.1.zip", repos = NULL)
throws a warning that package ‘~/Downloads/teradataR_1.0.1.zip’ is not available (for R version 3.0.0)

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

HI,
I am using teradataR (1.01) to do some clustering analysis, but I found the td.kmeans function can only receive a full table as the input.
So my question is can I assign some columns to td.kmeans and make it do analysis only on these columns?
like this way:
>  test <- td.data.frame("test", "testdb")
>  td.kmeans( cbind(test["x"], test["y"]) )

I am troubling over this error as I am not sure what the cause might be. Any help would be greatly appreciated.

select Id, (select
case
when Fall_score = b.MaxScore then 'Fall'
when Spring_score = b.MaxScore then 'Spring'
from sandbox.testtable t where t.Id = b.Id
) as MaxScoreCategory,MaxScore
from (select Id, max(Score) as MaxScore
from (select Id, 'Fall', Fall_score from sandbox.testtable union all
select Id, 'Spring', Spring_score from sandbox.testtable)
a (Id, Category, Score)

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

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?

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

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

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)
 

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,

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

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

I've been looking for a solution to this, and I found this: http://forums.teradata.com/forum/enterprise/concatenating-different-values-in-a-field but I'm just not able to figure out how to make it work. I'm a complete noob at teradata, and I've never used a recursive query before.

I've seen the examples where you do a cast() against a known number of child values, but that isn't possible- I don't know how many child values are out there.

Here's what I'm essentially trying to do:

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?

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

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

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

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.

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.

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

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?

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 |