InstantForum.NET v4.1.4Teradata Forumshttp://www.teradata.com/teradataforum/info@teradata.comSat, 21 Nov 2009 01:10:13 GMT20http://www.teradata.com/teradataforum/Topic12816-9-1.aspxHi all!I just installed TD Express 12 on a Windows XP SP3 box, with DEP set to alwaysoff. However, I can't get PDE to start. Sometimes, it'll say that segmain.exe crashed, and other times, pdestate -w will show it hanging on START/NETCONFIG, then after a long delay, goes back to DOWN/HARDSTOP.Any idea what might be wrong here? I thought the Demo was supposed to work on a non-server-class system straight out of the box...Thanks in advance!Wed, 03 Sep 2008 13:47:38 GMTtdgihttp://www.teradata.com/teradataforum/Topic17342-9-1.aspxHi,I have this query in teradata:select current_date (format 'dd.bmmmmbyyyy') (varchar(30));with this output:17. November 2009Does anyone know how to convert the Month into German?Tue, 17 Nov 2009 14:01:35 GMTcarlsantoshttp://www.teradata.com/teradataforum/Topic17345-9-1.aspxI have a table and the values need to be inserted from text file. The table is like this.Ext_id should be the first column.Ext_id Group_id Claim_Number 123 4375 12300The ext_id always have to be derived from claim_number by stripping off last two digits.I have the text file with values to be loaded except for Ext_id. How do I derive from Claim_Number and insert/import into table? Anyway can I do it using INSERT query. I dont want to update the table once its inserted becasue i have to do this periodically.Tue, 17 Nov 2009 18:12:14 GMTRajasan77http://www.teradata.com/teradataforum/Topic17326-9-1.aspxHi experts,please help me out in finding index of last occurrence. for finding first occurrence index( string,'_'). - Returns the index of position of '_' in the given string. Example.abc_ppc_abdd_1234P;name=abcabc_ppc_abdd_hello_1234P;name=abcabc_ppc_abdd_here_it_is_1234P;name=abcabc_ppc_abdd_tesing_1234P;name=abcI need the position of '_' before 1234P;Your help will be greatly appreciated. ThanksVisMon, 16 Nov 2009 12:27:45 GMTgreatvissuhttp://www.teradata.com/teradataforum/Topic17330-9-1.aspxI'm trying to create a DROP/CREATE script for JI's to execute before/after daily batch jobs. The DROP statement needs to contain the databasename.jiname of any JI's that reference one, or more tables in the production database. This is a problem if the JI is in another database than the object(s) it references and has the same name as another JI. I can get the objects that are referenced from dbc.indexes and the databasename.jiname from dbc.TVM but I don't know how to join those two tables.Mon, 16 Nov 2009 14:51:24 GMTtboonehttp://www.teradata.com/teradataforum/Topic17324-9-1.aspxGurusLets say thers a update happening to a billion + row table and all of a sudden because i see that wrong column has been updated i abort the he session and i see from schmon -m that rollback is taking place in $R$ priority. This may be likely to take more time. So in these cirucumsatnaces how can i abort rollback from happening since i knew that at this time there are few imp worlloads coming in and ensure that thers no rollback activity during that time.by the way can we reduce the ROLLBACK runiing in rush priority to Low priority?CheersTD_DBAMon, 16 Nov 2009 07:12:13 GMTteradata_dbahttp://www.teradata.com/teradataforum/Topic17323-9-1.aspxHello Guys,We recently upgraded from 6.2 to 12 version. One of the queries which was running well in 6.2 has hit out with spool space issue.As a first measure i have already collected stats . Still its failing.So i would like to know in general what action should i take further?thanksTD_DBAMon, 16 Nov 2009 07:04:08 GMTteradata_dbahttp://www.teradata.com/teradataforum/Topic17315-9-1.aspxFor Meta Data Management reasons we’re looking for a java sql parser.The goal is to find a JAVA class where you can pass your sequel.As result we want to know the tables used by the query. In a later phase the columns become also important.Who is already doing this?What is the software you’re using.Who had good results with java code he is using?Thanks for the information.: I don’t want to use the Teradata parser (eg. with explain). Some scripts are using volatile and temporary tables.Thu, 12 Nov 2009 09:26:29 GMTKoen Meessenshttp://www.teradata.com/teradataforum/Topic17311-9-1.aspxDoes the compressed column play an impact in query execution, when it is involved in a left outer join?the column is compressed to a value for eg: '*'Wed, 11 Nov 2009 12:54:51 GMTrediff123http://www.teradata.com/teradataforum/Topic17279-9-1.aspxHi all,Is there any way I can run teradata on my windows 7 machine.???thanksBig appleSat, 07 Nov 2009 20:04:22 GMTBig applehttp://www.teradata.com/teradataforum/Topic17288-9-1.aspxHi,What are large objects? Can you please lemme know the prominence of an BLOB an CLOB(Binary and Char Large objects)?Thank YouSun, 08 Nov 2009 12:13:39 GMTsmartyhttp://www.teradata.com/teradataforum/Topic17287-9-1.aspxHi,If Teradata has a feature of Single version of business i.e different clients accessing the same database server, how does this happen in other databases? Will the data be repplicated for different clients?Thank YouSun, 08 Nov 2009 12:10:05 GMTsmartyhttp://www.teradata.com/teradataforum/Topic17286-9-1.aspxHi,I would like to know 1) if there are any other databases with the concept of parallelism. What are the competitors for teradata in Market.2) What are the major differences between teradata and oracle. Are there any advantages of Oracle over Teradata.If yes, can you please explain them.Thank You. Sun, 08 Nov 2009 12:05:38 GMTsmartyhttp://www.teradata.com/teradataforum/Topic17275-9-1.aspxHi 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.Fri, 06 Nov 2009 11:43:07 GMTspringteradatahttp://www.teradata.com/teradataforum/Topic17273-9-1.aspxHi 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 AdvanceFri, 06 Nov 2009 11:17:15 GMTravindrareddyhttp://www.teradata.com/teradataforum/Topic17220-9-1.aspxIs 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!JohnTue, 03 Nov 2009 15:36:00 GMTjzlamalhttp://www.teradata.com/teradataforum/Topic13326-9-1.aspxhi 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?thanksMon, 27 Oct 2008 21:02:40 GMTboyz123http://www.teradata.com/teradataforum/Topic17214-9-1.aspxI 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.Mon, 02 Nov 2009 11:41:43 GMTRajasan77http://www.teradata.com/teradataforum/Topic17252-9-1.aspxWhat is spool space in teradata? explain with examples.Thu, 05 Nov 2009 06:08:15 GMTGurunathanhttp://www.teradata.com/teradataforum/Topic17231-9-1.aspxHi All,How to enter a user comment to a table so it will appear after "help database" command ?thankskostekWed, 04 Nov 2009 06:16:24 GMTkostekhttp://www.teradata.com/teradataforum/Topic17230-9-1.aspxI 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 ..............................Wed, 04 Nov 2009 06:10:02 GMTteradata_ekinghttp://www.teradata.com/teradataforum/Topic17153-9-1.aspxi 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?Fri, 23 Oct 2009 17:53:07 GMTc@lib3arhttp://www.teradata.com/teradataforum/Topic17221-9-1.aspxI 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.Tue, 03 Nov 2009 15:36:07 GMTc@lib3arhttp://www.teradata.com/teradataforum/Topic17217-9-1.aspxHi 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.ThanksSenTue, 03 Nov 2009 04:55:48 GMTSenthilkumarhttp://www.teradata.com/teradataforum/Topic17212-9-1.aspxI 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,JeesooMon, 02 Nov 2009 01:00:46 GMTjs7319http://www.teradata.com/teradataforum/Topic17160-9-1.aspxHi 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,SubhashMon, 26 Oct 2009 17:15:25 GMTgander_sshttp://www.teradata.com/teradataforum/Topic17197-9-1.aspxHi,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,RoyThu, 29 Oct 2009 09:35:00 GMTChelseaFChttp://www.teradata.com/teradataforum/Topic17161-9-1.aspxHiWhen 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,Tue, 27 Oct 2009 00:26:40 GMTprakharhttp://www.teradata.com/teradataforum/Topic17189-9-1.aspxHow 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?Wed, 28 Oct 2009 17:36:45 GMTrediff123http://www.teradata.com/teradataforum/Topic17122-9-1.aspxHi,I need to serach for strings with "?" (with out quotes)example:quest?onmar?sli??lecan any body throw some light on this.-GRRWed, 21 Oct 2009 06:48:47 GMTGraohttp://www.teradata.com/teradataforum/Topic17171-9-1.aspxHi 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,AndreasTue, 27 Oct 2009 08:54:29 GMTA.Thomsenhttp://www.teradata.com/teradataforum/Topic17103-9-1.aspxHi ,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 nameThanksMon, 19 Oct 2009 16:24:18 GMTsai11http://www.teradata.com/teradataforum/Topic17108-9-1.aspxHi!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 timeThanksArtemTue, 20 Oct 2009 07:06:52 GMTtoxickpihttp://www.teradata.com/teradataforum/Topic17128-9-1.aspxThe 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,TDHelpWed, 21 Oct 2009 12:57:25 GMTTDHelphttp://www.teradata.com/teradataforum/Topic17158-9-1.aspxCan 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.Sun, 25 Oct 2009 18:31:57 GMTgangwanihttp://www.teradata.com/teradataforum/Topic17146-9-1.aspxHiCan 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!!!!!!!!!Fri, 23 Oct 2009 03:49:47 GMTGurunathanhttp://www.teradata.com/teradataforum/Topic17119-9-1.aspxcan 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 RegardsTue, 20 Oct 2009 19:55:32 GMTsai11http://www.teradata.com/teradataforum/Topic17133-9-1.aspxHi,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.Thu, 22 Oct 2009 02:09:30 GMTforumpthttp://www.teradata.com/teradataforum/Topic17083-9-1.aspxI 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 youMon, 12 Oct 2009 19:53:39 GMTrediff123http://www.teradata.com/teradataforum/Topic17115-9-1.aspxI 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?Tue, 20 Oct 2009 15:27:50 GMTTeraDBATMV