All Forums

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

You must login to post to the forums.
week issues Topic by mad_about_coffee 04 Nov 2009

Hello,I hope someone could help me with my task:Weekends = Fri, Sat, SunWeekend days = 6, 7, 1What I need to do is to identify the number of times a specific guest should win depending on how many weekends the guest stayed. The situation is if the guest stayed for 2 consecutive weekends (which could either be Fri+Sat or Sat+Sun), then that is already considered as 1 win. If on the next weekends the guest still hasn't checked-out, then that is already considered as 2 wins. If on the 3rd weekends the guest still hasn't checked-out, then that already is his 3rd winning... and so on as long the guest is still checked-in.In the attached xls file, I have placed examples of guests' stay dates (which are already given), and the number of winnings (which I have to find-out).I pray that someone will be able to help me on this... ;) thanks again!

2 replies, 10 years ago
Requirements Gathering Topic by meta 16 Nov 2009

HiIam new to Teradata..can any one tell me the process of Requirements Gathering in Teradata warehousingthanks

1 reply, 10 years ago
Unable to COnfigure Teradata Manager Topic by 18 Apr 2009

Hi frnds!I have download teradata express verison. Installed done properly.All tools are working expect Teradata Manager.Host name :- or Localhost1.Database set up Failure system name user name = dbc / pass = dbcconsole = emptyuncheck create teradata mngr USerUncheck Migrate TDWM Databse error==========================================================4/19/2009 12:31:22 AM Database Setup started.Logging on to as DBC... *** Logon successfully completed.__________Setting up DBCMNGR__________Create profile pDBSSETUPDBA as password = (expire = 0); *** Query acceptedCreate profile pDBSSETUPDBA as password = (expire = 0); *** Query accepted*** TDWM setup and migration failed. See previous entries in the log (C:\Documents and Settings\saurav1\My Documents\NCR\Teradata Manager\data\TDWMMigration0904190031.log).Logging off... *** You are now logged off from the database.Database Setup terminated.Thanks in Adv

1 reply, 10 years ago
How to search through multiple stored procedures?? Topic by ratu 28 Oct 2009

Hi All,I am trying to search for dependencies in our database, and I have about 500 stored procedures to go through. Is there an easy way to do this?I compiled all the show procedures as follows:SHOW PROCEDURE database_name.procedure_name_1;SHOW PROCEDURE database_name.procedure_name_2;SHOW PROCEDURE database_name.procedure_name_3;SHOW PROCEDURE database_name.procedure_name_4;SHOW PROCEDURE database_name.procedure_name_5;etcDoes anyone know how I can save the result set into a table or export it to a text file where I can perform my searches on the keywords?Thanks!

2 replies, 10 years ago
Validate column data Topic by David Korb 27 Oct 2009

Hi,I'm trying to validate that each character in a varchar(100) row is between 0 and 9 on a 250M row table. Any rows that are not valid will be ignored in an insert statement into a base table. However, this is giving me a hard time. I tried using a tally table with substring comparison in an exists clause, but the data are just too huge.

4 replies, 10 years ago
Difference between select * from Table & select individual columns Topic by az_maverick 20 Aug 2009

Hello all, I am having trouble understanding difference select * from TABLE X or select a , b, c ( columns) from table X ( same table ) above.I was wondering if someone actually knows what is the difference at the Teradata DB Architecture level.Thanks,Aditi

2 replies, 10 years ago
SQL query Topic by srinu 10 Sep 2009

Hi all, I am trying to create a new variable depending on already existing values in the table but got stuck.. here is the examplecolA colB new_col1 new_col2jet a 1 1jet b 1 1cat c 1 2cat d 1 2dog e 2 3hen f 3 4 I need to create new columns (new_col1 & new_col2) . I am looking to assign a common value for new_col1 if the values in colA are duplicates and values in colB are unique (i.e jet and cat should get the value of 1 in new_col1) and assign a separate value for each duplicate value in colA (i.e . jet should get 1 and cat should get 2 in new_col2). Could anyone please help me in getting this result? Really appreciate any help on this issue! Thanks in advance!Srinivas.

1 reply, 10 years ago
what lock is when inserting data Topic by shubh 22 Oct 2009

Hi,I want to know if I am inserting data from TBL1 to TBL2, what lock is on the TBL2.I want to make sure when data is being inserted to TBL2 it is still available to read but not with half full data.Ex -> 1. TBL2 is empty initially.2.

3 replies, 10 years ago
what is best way to query huge table primary partitioned by date and indexed by id and date Topic by TimManns 15 Jun 2009

I'm in telecommunications.I can run a query which completes in several hours (against 1 month of data), but I am looking for a way to optimise my query to make it run faster. I have a generic-ish question about how I should index a cross-join table or optmise my query. - bit of background - I'm working on a huge detail table with billions of rows, containing call detail rows (cdr's). The table is primary partitioned by date and primary indexed by id (phone number) and date. There are many rows with the same id and date (multiple phone calls in a single day).At the moment in one single query I create a cross-join with a few million id's (unique) each duplicated by 30 days (from sys_calendar). This cross-join part then inner-joins to the huge detail table. The query completes in several hours, although Explain looks great and says it should take a few minutes :) I'm using all the correct indexes etc and no DBA can fault the query.I tried to break this query down so that the cross-join was a separate step and outputted the data into a table with primary index of id (phone number) and date. I hoped the query would run faster if I index the cross-join table to match the huge detail table. Creating the volatile cross-join table takes a few minutes. I then inner join this volatile cross-join table to the huge detail table, but after 8 hours (out of normal office hours) it hadn't completed and i killed it.- question -Any advice on how I might get a huge cross-join query against several billion rows to run faster?ThanksTim

3 replies, 10 years ago
mbbs in ukraine Topic by iglobal1 08 Oct 2009

Hi, Join the world recognized MBBS program in Ukraine. You can now earn your MBBS degree at an expense less than 30% by doing it in one of the most reputed universities in Ukraine with world class accreditation. * 50% Aggregate In Physics, Chemistry & Biology * 30% Cheaper * World Class Standards * Medical University Listed With International Medical Education Directory (IMED). KarabutsLogovenko Street,MykolayivUkraine - 16600+380930484422Website: http://www.karabuts.comEmail:

0 replies
Data migration open source tool Topic by ofdabler 06 Oct 2009

Hello, We are trying to find an ETL tool open source. Basically, we need our software to perform ETL, data migration and data synchronization. The program should not be used on larger projects. A few open source tools are on the market. Some ideas? Thanks.

1 reply, 10 years ago
Averaging time field Topic by indo81 05 Aug 2009

Hello there, I am encountering an error when I am trying to perform average function on a field with data type of time(0). For example: sel avg(end_time)from table1It gave error "5407: Invalid operation on an ANSI Datatime or Interval value"I tried: sel sum(end_time)/count(end_time)from table1This failed with the same error message due to the sum function.Has anyone encountered this problem before? If so, can someone be kindly offer a solution to this? Thanks in advanced,N

1 reply, 10 years ago
7502: A system trap was caused by UDF/XSP/UDM ALL_ETL_UDF Topic by pgoutam 23 Sep 2009

I wrote a UDF to read the string and if there are any control characters replace with spaceswhen I run the udf one time is working and next time its giving 7502 system trap error and if I run again its working, I am not sure what needs to fix, can anyone helpI am also pasting the codeselect myreplace(loss_desc) from xxx #define SQL_TEXT Latin_Text#include "sqltypes_td.h"#include #include #include #include #define MAX_STRING_LENGTH 256#define IsNull -1#define IsNotNull 0#define NoSqlError "00000"#define NUL '\0'void myreplace( char *inp_str, char *out_str, int *inp_strIsNull, int *out_strIsNull){ int i = 0, j = 0; char *tmp_str ; *out_str = '\0' ; memset(out_str, NULL, sizeof(out_str)) ; tmp_str = malloc(strlen(inp_str)*sizeof(char)); strcpy(tmp_str, inp_str) ; if (*inp_strIsNull == IsNull) { *out_strIsNull = IsNull; return; } *out_strIsNull = IsNotNull; *out_str = NUL; if (strlen(inp_str) == 0) { *out_str = '\0'; return; } while (tmp_str != NULL ) { if (iscntrl(tmp_str) ) { tmp_str = ' '; out_str[j] = toupper(tmp_str); } else if (tmp_str == '^' ) { tmp_str = ' '; out_str[j] = toupper(tmp_str); } else { out_str[j] = toupper(tmp_str); } ++i; ++j; } out_str[j] = '\0' ; free(tmp_str) ; return;}

0 replies
Date Gap Analysis Topic by ccotton333 20 Aug 2009

Here are the details I have a table that contains effective start/end dates. What I want to do is find where the gap is and only pull data on either side of that gap. for example:UPC effect_start effect_end123 2-7-2007 8-2-2007123 8-2-2007 11-2-2007123 11-2-2007 12-4-2008123 8-1-2009 8-4-2009123 8-4-2009 NULLIf I wanted to pull the most current items history information, I would only return the records from 8-1-2009. The indication is the mismatch between the effect_start and the effect_end fields. I have tried all sorts of ways to make this happen. My understanding of SQL deals with databases other than Teradata, and I can't recall ever having this much trouble doing this. I would think that there is a way to compare the data within a order recordset/answerset. Am I completely off base here? Can this and/or how do you perform this operation. Please understand that I would prefer to not build temp tables, but I will if I have to. All of my attempts have been without the use of temp tables.

7 replies, 10 years ago
Teradata SQL Assistant result set Topic by JustMe 16 Sep 2009

I am performing some stress tests of user queries, and the query text contains carriage returns. When I try to put this data in an Excel spreadsheet, it breaks up the query text. I tried checking the appropriate box on the EXPORT tab on SQL Assistant, but the carriage return remains in my result set. Any suggesstions?

1 reply, 10 years ago
how to export table values into a file? Topic by rohitbeth 16 Jun 2008

Hi All,Is it possible to export the table values generated from a select query into a file ???I have tried using the tools menu >> export option - but this only exports the queryman history values. I am looking at exporting the actual data in the in table to a file ??It would be great if any of u can help me with this...!!Thanks,Rock

5 replies, 10 years ago
[NCR][ODBC Teradata Driver] Timeout expired Topic by tsnr 01 Sep 2009

Hi ,I'm trying to connect from Siebel 7.8.2 application to Teradata database(V12) using Teradata ODBC driver.The application is on Solaris 8 server.I'm getting the following errorError Codes: OPR4ONWY:U9IM8TAC:OI2DL65PFile: odbcstatementimpl.cpp, Line: 186State: S1000. Code: 10058. [NQODBC] [SQL_STATE: S1000] [nQSError: 10058] A general error has occurred. [nQSError: 16001] ODBC error state: IM006 code: 0 message: [DataDirect][ODBC lib] Driver's SQLSetConnectAttr failed. [nQSError: 16001] ODBC error state: S1T00 code: 0 message: [NCR][ODBC Teradata Driver] Timeout expired. (S1000).I'm thinking there is some problem with odbc.ini file settings.Please help in identifying the correct issue.ThanksSiva.

1 reply, 10 years ago
OReplace function Topic by jayshree909 24 Aug 2009

want to know can we use Oreplace function for eliminating a character

1 reply, 10 years ago
Difference between select * from TABLE & select individual columns Topic by az_maverick 20 Aug 2009

Hello all, I am having trouble understanding difference select * from TABLE X or select a , b, c ( columns) from table X ( same table ) above.I was wondering if someone actually knows what is the difference at the Teradata DB Architecture level.Thanks,Aditi

0 replies
Deadlocks on Join Indexes- How+What+Why? Topic by roy.kabir 10 Aug 2009

I have a table, CUSTOMER and 3 Join Indexes- CUST_JI1, CUST_JI2, CUST_JI3 defined on the same, say for columns- cust_address, cust_phone, cust_email.During peak hours, users insert data into the CUSTOMER table via a web UI. Randomly, they suffer from time outs. Analysis showed that deadlocks are the culprit and PMON points to the 3 JIs i.e. CUST_JI1, CUST_JI2 and CUST_JI3 as the contention points. User 1 session waits up on CUST_JI1 WRITE LOCK of User 2 and so on and so forth.Per TD text, JIs cannot be updated directly, rather they are updated post insert/updates to the base table, CUSTOMER in this case.Since the earlier random time outs are occurring frequently now, someone, please do shed some light on how come lock contention happens on JIs?Thanks in advance.

0 replies
How to store the result set from SELECT query using CLIv2 programming Topic by shrihari_s 07 Aug 2009

Hi,I'm new to Teradata and trying to write simple program using CLIv2 (Teradata version 12). Using the sample cli code provided by Teradata I could able to connect and execute the statements like CREATE TABLE, INSERT INTO.. But when I want to fetch the result set of a query SELECT * from SAMPLES.table1 I'm facing problem in coding. I dont know how to get the records from the query. Could any one help me in coding this. According to me after executing the query using DBCHCL(&result,cnta,&dbcarea), the result will be stored in dbcarea.fet_data_ptr; I dont know for which structure I have to type cast this, and also do I need to take care of the Data Types returned by the result set. In the Teradata sample program, it is reading character by character and storing in a file. I want to how to read a Database column. I sample example for fetching the records will be very helpful me.

0 replies
Lock Info in teradata Topic by Manish Jain 15 Jul 2009

Hi all,Is there any SQL with help of which we can get the information as which table and what kind of lock is acquired on it at any point of time in teradata.Your response will be appreciated.Thanks

1 reply, 10 years ago
Will MLOAD able to Insert data into Global Temporary Tables !!! Topic by ANIMESH.DUTTA 09 Jul 2009

Is Teradata Utilities like allows to /update/delete the data into !I need to insert huge volume of data to a GTT through MLOAD via 1 session! Is that possible via single session! It seems MLOAD will not insert into GTT via multiple sessions.Inserting data into GTT via BTEQ or MLOAD (if possible) - which is preferable?

5 replies, 10 years ago
Teradata admin Topic by sureshbadam 09 Jul 2009

I have created a role and granted this role to the user but the user can't access the database that is granted to the role.I have executed the follwoing.create role x;grant select on edw_tb to x;grant x to user1;What I am doing wrong here?

1 reply, 10 years ago
Teradata coding standards, review checklist, performance checklist - Help me Topic by shreegnc1981 11 Jul 2009

Hi ,I am now preparing coding standards, review checklist, performance checklist for teradata. If anybody has these checklists please provide me so that it will be of great help for me. If not atleast please provide me the points which you all have taken care during coding which helped in improving the performance of the code and also the standards you all followed while coding.Regards,Prem

0 replies