#DateForumTypeThreadPost
2105 Feb 2010 @ 02:15 PSTUDAReplyImproper column reference in the search condition of a joined table. PLZ HELPSorry, I am little confused with your explanation. In original query you posted, there were three tables. There is no join condition specified between A &B. Now recent query you are showing has onl...
2004 Feb 2010 @ 07:27 PSTUDAReplyImproper column reference in the search condition of a joined table. PLZ HELPYou are doing left outer join between B & C. So condition you use in ON clause can not reference columns of A. Your query is resulting into error because of condition "A.column1 = C.column1". It wi...
1902 Feb 2010 @ 08:05 PSTToolsReplyhow to insert no of rows effected by a sql stmt in bteq script into a table with in the same scriptConsulted with one TD expert i know. He also confirmed it can not be done using BTEQ.However it can done using a stored procedure. Below is sample code if its useful to you.create multiset table te...
1802 Feb 2010 @ 07:43 PSTToolsReplyhow to insert no of rows effected by a sql stmt in bteq script into a table with in the same scriptDid some research on this. Could not find out any way to do this in BTEQ. activitycount indicates the actual number of rows affected by the request. But we can not use that in any SQL. It can just...
1702 Feb 2010 @ 07:17 PSTToolsReplyTpump utiltyThis is how i infered Tpump can be used for real time data warehousing. I may be wrong though.Lot of companies in today's world want to get data from transactional systems into data warehouse in re...
1602 Feb 2010 @ 06:57 PSTToolsReplyNUSI VS FULL TABLE SCANRefer to below URL. Hope this answers your question.http://www.teradata.com/teradataforum/Topic17866-9-1.aspx
1501 Feb 2010 @ 08:06 PSTDatabaseReplyrownum in teradataI understand your problem. But i am unable to solve the problem without having a column to identify a latest record. Only if i know the latest record then i can flag other records with "Dup". I don...
1401 Feb 2010 @ 06:37 PSTDatabaseReplyrownum in teradataYou are right. There is no pseudo column "rowid" in teradata.If you want to eliminate duplicate records (basically all columns having same value in more than one record), then you can create a SET ...
1301 Feb 2010 @ 05:32 PSTDatabaseReplyPerformance Tuning FactorsThere could be multiple factors. Below are some i can think ofQueries which does lot of redistribution and results in product joinsQueries consuming high cpu, IO, spool space usage (again this is r...
1228 Jan 2010 @ 03:38 PSTDatabaseReplycan any one tell me why teradata is pure active datawarehouse?Active data warehouse supports real time or near real time decision making. Many of the companies were doing nightly/weekly loads into data warehouse. Now trend in large organizations is to get dat...
1128 Jan 2010 @ 02:43 PSTDatabaseReplyhow to drop a constraint without knowing its nameSorry, somehow my message did not come up as i sent. You should put tablename for tvmname column.
1028 Jan 2010 @ 02:41 PSTDatabaseReplyhow to drop a constraint without knowing its nameIf you are still looking for solution, try below query. Hope this helps.select al1.* from dbc.ConstraintNames al1, dbc.tables2 al2, dbc.databases2 al3where al1.tableid = al2.tvm...
928 Jan 2010 @ 01:47 PSTDatabaseReplyWay to deal with procedure in teradata?As far as i know, It is not possible to invoke a procedure in an SQL.
827 Jan 2010 @ 09:04 PSTDatabaseReplyWhat is method or strategy to calculate the CPU utilized in running a Query?You can query "dbc.DBQLogTbl" table to get CPU time (column name: totalcputime) taken by a query. This table also provides I/O count. You can limit on sessionid if you know. If you only know time ...
727 Jan 2010 @ 08:38 PSTDatabaseReplyHow to find the no:of amps involved in storing the data of a table?Another way to find number of AMPs.select count(*) from dbc.tablesize where tablename =
627 Jan 2010 @ 08:33 PSTDatabaseReplyCollect Statistics on TableOptimizer relies heavily on statistics to generate a query plan. You should collect stats on NUSI, Partition columns, Any columns used in Joins.Once you collect stats on a column, Teradata knows ho...
527 Jan 2010 @ 08:28 PSTDatabaseReplyIndex Cocepts in TeradataUPI, NUPI:Teradata uses primary index to distribute data across AMPs. PI access is always one amp operation as teradata knows exactly where the record is. So both UPI, NUPI results in one amp opera...
427 Jan 2010 @ 08:13 PSTDatabaseReplycreate table as (select.... takes long timeI agree. Mostly it should be due to not specifying primary index explicitly, By default it takes first column in select as PI. I too did the same mistake some time back, specifying primary index re...
327 Jan 2010 @ 08:10 PSTDatabaseReplyNeed SQL Script/Logic to size an identified subset of a tableOne way i can think of is query dbc.tablesize before and after archiving the table. This should tell you how many GB you removed to archive.
206 Jan 2010 @ 08:13 PSTDatabaseReplyCalendar table calculationHi Ratu,Please try below query. Hope this helps.select al1.year_of_calendar, al1.month_of_year from sys_calendar.CALENDAR al1, (select month_of_calendar from sys_calendar.CALENDAR where cale...
130 Sep 2009 @ 03:11 PDTDatabaseReplyExtracting Object PermissionsYou should be able to query dbc.allrolerights and find list of accesses granted and who granted it.