Teradata Forums / Teradata / Teradata Database

Teradata DBA required

springteradata — Fri, 06 Nov 2009 11:43:07 GMT

Hi there,I have a urgent requirement for a Teradata DBA in the Manchester area. It is a permanent role with a large and stable organisation within the financial sector.Competitive package offered.For more information please get in touch via this post.

ANSI mode BTEQ Script

ravindrareddy — Fri, 06 Nov 2009 11:17:15 GMT

Hi All,I need to write one Bteq script to update my table.Along with this I need to take the number of records it is going to update by running select statement before Update statement.Once update statement is complete..I want to see how many records this Update statement has updated...If the prior count and after counts are matching Commit this operation else through an error by releasing the lock and do a ROLLBACK.As per my knowledge I need to go for ANSI transaction mode...I'm very new in using ANSI mode in BTEQ...Could any one help me in providing sample BTEQ script for this scenario...Thanks In Advance

DBQL field definitions

jzlamal — Tue, 03 Nov 2009 15:36:00 GMT

Is there a document that defines the columns in the database query log tables (DBQLogTbl, DBQLSQL,... etc)? For example, I have seen various documents on Teradata performance that reference the field HotAmp1IO, but I have not see any document that actually defines what that field represents. I checked the "Performance Management" documentation from Teradata and didn't see the answer there either. Any ideas?Thanks!John

Adding a interval to datetimestamp from another column

boyz123 — Mon, 27 Oct 2008 21:02:40 GMT

hi Allis it possible to add a numberic column to a datatime column?eg:column 1:begin_datecolumn 2:add_hourscolumn 3 = begin_date + interval 'add_hours' hour how do you do this in teradata?thanks

Loading Date

Rajasan77 — Mon, 02 Nov 2009 11:41:43 GMT

I getting a records from text file and needs to be loaded into teradata. One of the filed is going to be date. The value i am getting from text file is like this '20091102'. How do I load this into teradata as date field. It should be loaded like '2009/11/02'.?eg;What I getdate-----20091102I Should load this to teradata as date value.

What is spool space in teradata? explain with examples.

Gurunathan — Thu, 05 Nov 2009 06:08:15 GMT

What is spool space in teradata? explain with examples.

enter user comment too a table

kostek — Wed, 04 Nov 2009 06:16:24 GMT

Hi All,How to enter a user comment to a table so it will appear after "help database" command ?thankskostek

teradata scalability questions

teradata_eking — Wed, 04 Nov 2009 06:10:02 GMT

I wonder that in teradata, the map from hash bucket to AMPs are static?????????????If I add new AMPs to the current system,how could teradata adjust its map from hash bucket number to AMPs ???I think it has to rebuild a new map and move data accordingly,am I right????/looking for help ..............................

rank() (partition by....) help

c@lib3ar — Fri, 23 Oct 2009 17:53:07 GMT

i am trying to come up with a way to rank and have the ability to window using the rows function. i know that neither the rank nor the percent_rank functions allow you to specify the rows to window over.so what i would want to do if using rows was possible in the rank statement:rank() over (partition by column order by column )does anybody know of a work around for this?

FASTLOAD ERROR 34: fread error

c@lib3ar — Tue, 03 Nov 2009 15:36:07 GMT

I am using the Express Edition software on my laptop and have been receving this error today for some reason unbeknownst to me. I am 99% sure this exact script ran without issue last week. I cannot for the life of me find this error in the Messages documentation. Anyone have any experiene with it? Attached is the screenshot of the error. Thanks in advance.

Conversion Varchar to Date datatype

Senthilkumar — Tue, 03 Nov 2009 04:55:48 GMT

Hi All,I have to load data from varchar column which has value like '01-AUG-09' to date column.When i do CAST as date it is throwing below error.Error : 2666 Invalid date suppliedPlease help me in identifying the issue.ThanksSen

is it possible to export a database from Teradata SQL Assistant?

js7319 — Mon, 02 Nov 2009 01:00:46 GMT

I am a student learning databases.I recently got TUN account and want to see if it is possible to export a selected database from Teradata SQL Assitant into a private server and how.Cheers,Jeesoo

Site minder authentication for Teradata

gander_ss — Mon, 26 Oct 2009 17:15:25 GMT

Hi all,Problem statement :One of my client is having BO and Teradata environment.And BO authentication is happening using site minder.But Teradata authentication is not using site minder , it is using it's own set of users.When a user comes to BO web i using site minder session ID he/she can run web i reports on BO universe, which is having connection details other then user credential login (i.e. universe is using credential from set of users teradata not at site minder)So if more than 10 users are accessing the same universe via web i, then Teradata throws spool space error.Which is self explanatory.Question :So I am looking for steps through which same site minder session id is also can be reused by BO universe to connect with teradata database.So if any one has does this or having any kind of idea then please share the same with me.Regards,Subhash

Columns from Distinct Rows

ChelseaFC — Thu, 29 Oct 2009 09:35:00 GMT

Hi,Can someone out there please help me to convert a list of DISTINCT values in one column into a set of extra columns for each row value?Many thanks,Roy

3800: DATATYE MISMATCH IN THEN/ELSE EXPRESSION

prakhar — Tue, 27 Oct 2009 00:26:40 GMT

HiWhen i am running following querySEL COALESCE(A,CAST(' 12:63:39.999999 'AS TIME(6))) FROM employeeI am getting error 3800: DATATYPE MISMATCH IN THEN/ELSE EXPRESSIONCOLUMN A IS TIME(6) IN DDL.Please let me know the reason of this error and also the remedy...Regards,

Global temporary table

rediff123 — Wed, 28 Oct 2009 17:36:45 GMT

How to create a GTT from another tableis it likecreate global temporary table as (sel * from employee)on commit preserve row;if i do this, the syntax error: something expected between ')' and 'on'what is the mistake?

serach for "?"

Grao — Wed, 21 Oct 2009 06:48:47 GMT

Hi,I need to serach for strings with "?" (with out quotes)example:quest?onmar?sli??lecan any body throw some light on this.-GRR

NUSI maintenance

A.Thomsen — Tue, 27 Oct 2009 08:54:29 GMT

Hi all,I've got a large table with some NUSIs und run regularly (mass) delete/inserts on this table. In order to accelerate the inserts, my idea is to drop the NUSI's before the insert statement and rebuild them afterwards. Do you have any information on performance behavior ? How do you think, this might affect the performance and is this really significant?In my case the delete/insert operations affect about 20-30% of the tables overall row count.I've already searched the documentation and this forum for information, but couldn't find a lot. Any opinions/facts on this idea in general or in my specific case are much appreciated!Thank you very much for you help in advance.Regards,Andreas

Please help with update conversion from oracle to Teradata

sai11 — Mon, 19 Oct 2009 16:24:18 GMT

Hi ,This is Oracle query . update dbname.sharp_app_usage_rpt_details A set display_format = Coalesce ((select C.propvalue from dbname.sharp_app_usage_rpt_details B, dbname.sharp_properties C where B.request_ID = A.request_ID and B.report_Name = A.report_name and B.report_type = A.report_type and C.systemname = 'dfdf' and C.propname = B.report_name || '.' || B.report_type || '.OutputFmt' and C.PROPVALUE in ('dfd', 'dfda') ), display_format)where request_id = 56546Converted TD query update table_a A FROM (select C.propvalue from table_b B, table_c C ,table_a A where B.request_ID = A.request_ID and B.report_Name = A.report_name and B.report_type = A.report_type and C.systemname = 'dfdf' and C.propname = B.report_name || '.' || B.report_type || '.OutputFmt' and C.PROPVALUE in ('dfd', 'dfda') ) AS D(D1) SET display_format = D1 Coalesce (D1 , display_format ) where request_id = 56546I am getting error as illegal usage of alias nameThanks

create inserts on table data

toxickpi — Tue, 20 Oct 2009 07:06:52 GMT

Hi!Can you help me?I need to create inserts on table data in order to make installation script.I looked up in Sql Assistant, Teradata Administrator but haven't found.it is possible to write smth on VBA or C#, but I haven't timeThanksArtem

getting UDFCALLNAME error

TDHelp — Wed, 21 Oct 2009 12:57:25 GMT

The following query is getting an syntax error as 'expected something like a name or unicode delimited identifier or UDFCALLNAME between ')' and where keyword.SELECT 111 "id",item,atr1,rprt_format,'Submitted' "status"FROM (SELECT pkglineitem,atr1,rprt_format FROM TAble1 as a , (SELECT 'Detail' Rprt_format from Sys_Calendar.Calendar where 1=1 UNION ALL SELECT 'Monthly' rprt_format from Sys_Calendar.Calendar where 1=1 UNION ALL SELECT 'Quarterly' rprt_format from Sys_Calendar.Calendar where 1=1 UNION ALL SELECT 'Summary' rprt_format from Sys_Calendar.Calendar where 1=1 ) WHERE a.userid = 'MikMetz' AND a.moduleId = 'RPT' AND a.pkgname = 'User ID reports')The result set should look like this:id item atr1 rprt_format status111 R01 NONE Detail Submitted111 R01 NONE Monthly Submitted111 R01 NONE Quarterly Submitted111 R01 NONE Summary Submitted111 R02 NONE Detail Submitted111 R02 NONE Monthly Submitted111 R02 NONE Quarterly Submitted111 R02 NONE Summary Submitted111 R03 NONE Detail Submitted111 R03 NONE Monthly Submitted111 R03 NONE Quarterly Submitted111 R03 NONE Summary SubmittedI am converting this from Oracle to Teradata. Please help me if the query is wrong. Thanks and Regards,TDHelp

V2R6 and TD12

gangwani — Sun, 25 Oct 2009 18:31:57 GMT

Can you please highlight some of the important areas which you might have seen as shortcomings in V2R6 that have been covered in TD12? For example analytical functions which were implented in TD12 and were not available in V2R6? Thanks for all the help.

How to select special characters in teradata???

Gurunathan — Fri, 23 Oct 2009 03:49:47 GMT

HiCan anyone tell me, What is the query to select the special characters in teradata?Eg: test--tablenamename -- columnname=====@@@@!!john-----**smith????^^^```peter~~~@@sachinedwardwaughmark%---lara----i want the rows which contains the special characters in a table.Please help me out from this....Thanks in advance!!!!!!!!!

ORacle to Teradata update query conversion

sai11 — Tue, 20 Oct 2009 19:55:32 GMT

can anybody help with oracle to teradata conversion . update table_a A set display_format = nvl((select prop.propvalue from table_a B, table_c C where B.request_ID = A.request_ID and B.report_Name = A.report_name and B.report_type = A.report_type and C.systemname = 'system' and C.propname = B.report_name || '.' || B.report_type || '.OutputFmt' and C.PROPVALUE in ('yes','No') ), display_format) where request_id = 123 Regards

Teradata Issue

forumpt — Thu, 22 Oct 2009 02:09:30 GMT

Hi,I am very new to Teradata. Just today I have download Terada 12.0 and installed on VMware which is have windows 2003 server sp2.I have Installed successfully as per documentation.After re-start system, launch the Teradata service control and start the Teradata. Status bar of that window is showing PDE State is RUN/READY.I am not getting session info, not able to connect to db.Someone please help and guide me.

COALESCE

rediff123 — Mon, 12 Oct 2009 19:53:39 GMT

I need to write a query, so that it returns the non-null value like the following,COALESCE (db_1.appl_no, db_2.appl_no) as Appl_nbrBut, db_1.appl_no has both '?' and ' ' here the coalesce function takes '?' as Null, but returns the blank field ' ' assuming it to be Not Null.so i change the query like the following,COALESCE (CASE WHEN db_1.appl_no = ' 'THEN NULLELSE db_1.appl_no END , db_2.applno as appl.no) as Appl_nbrIs there any other better way???Pls help-Thank you

is it possible to add to the subseconds (precision seconds) in a timestamp

TeraDBATMV — Tue, 20 Oct 2009 15:27:50 GMT

I have run this select interval 000001 second + current_timestamp (6) , current_timestamp;2009-10-20 13:32:54.110000+00:00 2009-10-20 13:32:53.110000+00:00to add to the seconds, however I want to add to the precision seconds in a time stamp and have been unable to find a way.Has anyone done this?

Storing GMT offset in minutes with sign

novice — Mon, 19 Oct 2009 05:21:02 GMT

Hi,I am trying to make a table which should be to store GMT offset time along with the Sign for a city. For example City GMT Offset Time---------------------------------- -------------------(GMT-04:00) Atlantic Time (Canada) -4(GMT+05:30) Calcutta, Chennai, Mumbai, New Delhi 5.5(GMT+06:00) Almaty, Novosibirsk 5.75I can create a table like create table My_Table( city Varchar(60),gmt_offset_tm integer ) primary index (city)but then how can I cast this integer to time hh:mi and then perform addition and subtraction on it?please help

Teradata Parallel Transporter TPT API

PSPL — Wed, 16 Aug 2006 02:05:41 GMT

I am new to Teradata and would appreciate, if someone could respond to following queries.

-Is there any documentation on the architechture of Teradata and how does it store the data/tables?

-Where can I find the TPT API/SDK and relevant documentation on HOWTO use it? Is it available for free download from NCS?

-Is TPT API supported in v2r5 version?

-Are there any samples of code which depicts the usage of TPT API to extract data from/load data into Teradata?

Thanks
PSPL

Where to locate TPT API?

PSPL — Fri, 25 Aug 2006 06:35:37 GMT

I have installed the TTU8.1 but couldn't locate the include/libs for TPT API.

Any idea from where to get developer's c++ sdk for TPT API like header files, libraries?

Thanks.
Mataprasad

teradata stored procedure

lordofrings — Wed, 07 Oct 2009 23:19:08 GMT

Hi All, I am facing some wall in the stored procedure in Teradata. Please help me.The definition of SP is as follows:Create procedure 123(IN ABC varchar(1000))( begin Define I integer; Define query Varchar (1000); Set I = 1; Set query=ABC; While (I < 36) DBC.EXEC(:query); set I= I+1; END While;) sorry if the definition of Procedure is wrong My doubt is if my input is 'Select A from DB1. Table where B= ;' I want to pass 'I' in the procedure to this query for every loop....How to do it in teradata ?Thanks in Advance

Rename View

Rajasan77 — Mon, 12 Oct 2009 11:02:14 GMT

Is It possible to rename the VIEW itself without creating the new one?

Problem with Keyword YEAR in a view

KayeK — Thu, 08 Oct 2009 14:28:38 GMT

Why does this work....sel YEAR(Date-1) as TEST_YRBut this does not...replace view dbowner.TEST_V as ( sel YEAR(Date-1) as TEST_YR ) ;Is there a simple syntax work around I am missing here to use the keyword function YEAR in a view? Thank you!

DBC views

afedeli — Wed, 07 Oct 2009 08:13:19 GMT

Hello everybody,I need to monitor daily the tables record count of several dbs.Is there a way to get the tables list with relative record count simultaneously???Thanks

PE is Down

Sudheer05 — Wed, 07 Oct 2009 08:08:19 GMT

HiiI am using v12.0 and working on Teradata Manager when it got suddenly disconnected.When i restarted the Service it states that "" but not able to connect to the Database. When i viewed the Event Log it states that the "" and "". I used Teradata Command Prompt and typed it gave me the following information:[b]PDE state is RUN/STARTED.DBS state is 7: System is operational without PEs - Sessions are not allowed[/b]Please help me out in .

implicit cursors in Teradata

Arun sankar — Wed, 07 Oct 2009 08:06:31 GMT

Hi,Does Teradata implicit cursors in any SQL execution. I remember reading this in the net somewhere that every database uses cursors for any query execution. And usage of the cursor would make the processing a sequential one.Can someone explain..Thanks,Arun

Nth Percentile Calculation in Teradata

Navnit — Wed, 07 Oct 2009 03:13:44 GMT

Is there any build in available in teradata to calculate nth ( e.g. 90th ) percentile in teradata for a given dataset.Im looking something similar to PERCENTILE function like ORACLE

format char fields to have leading zeroes

Maharaj4 — Tue, 06 Oct 2009 14:30:45 GMT

I am new to Teradata, so please ignore my lack of knowledge. Any help would be greatly appreciated.I have char(10) field in a table, which holds a number. This number in most cases is 10 character long but not in all cases. So in case if the number is less than 10 characters(for example 000123456), I want to be able to convert it by adding a leading zero to it. I am doing something like following, but it is not converting correctly. I am doing the select to have the data exported in a file as fast export.select cast(col1 as integer) (format '9(10)') from tablexP.S. col1 is of type charPlease help.

Subquery returns more than one value

rediff123 — Mon, 05 Oct 2009 17:33:30 GMT

I need to join two tables that have no direct relation.so another table has to be taken to join themfor ex:Sel col_12, col_34from table_a Ainner join table_b Bwhere B.col_sd= (sel C.col_sdfrom table_a A JOINtable_c C where A.col_sc= C.col_sc)The subquery returns more than one column.what to do?help me on this plz....

User commentString

Cranie — Sat, 03 Oct 2009 17:12:17 GMT

On creating users not all users have been given a standard comment. As we use staff numbers for logging on its hard to tell who this person is (quickly)I have wrote a script to search ldap for all the users in the database but can not find a way to update this field (modify user doesn't show the option looking in the manuals). COMMEN ON USER|database also does not work.Any ideas on how to modify this?select databasename,commentstring from dbc.databases where databasename = 'username'If the field I am talking about.