#DateForumTypeThreadPost
20314 Oct 2007 @ 06:10 PDTDatabaseReplyDynamic SQL and SELECT INTOHi Ron,you're right SELECT INTO is not supported for Dynamic SQL and a volatile table would be a solution for that.But if you're just trying to remove all blanks, this would be much easier using on...
20214 Oct 2007 @ 05:56 PDTDatabaseReplyWhat exactly is Session?Hi Ravi,a session is connected to any of the existing PEs on any of the nodes in the system.If you request more than one session they're distributed round robin, so 4 sessions on a 4 node system re...
20113 Oct 2007 @ 02:43 PDTDatabaseReplybizarre CAST/CASE problemHi Andrew,LEFT is not a Teradata/Standard SQL function, it's ODBC-SQL.If it worked before then because Options -> Query -> "Allow Use of ODBC SQL Extensions in Queries" was checked. As it's not rec...
20005 Oct 2007 @ 04:11 PDTDatabaseReply[HELP] How to collect statistics...Hi karmakoma,if a table is partitioned, there's always a column named PARTITION, which returns the partition number.You can "collect stats on table column partition", it's much faster than "collect...
19905 Oct 2007 @ 03:12 PDTDatabaseReplyHow to increase Number of sessionsHi rajan,increase number of sessions available for the system?Adding new PEs, each PE supports 120 sessions.Dieter
19805 Oct 2007 @ 03:10 PDTDatabaseReplyAccess to DBC tablesHi Leo,"How are access rights to DBC(dictionary) tables granted to the users?"By the DBA, typically using PUBLIC rights."Suppose when a user is created what rights does he have to access the dictio...
19705 Oct 2007 @ 03:06 PDTDatabaseReplyDBQL log for Modify UserQuery Log doesn't log systemtable accesses and LastAlterName is not only set by "modify password", so the usual way would be enabling Access Log.Dieter
19605 Oct 2007 @ 03:03 PDTDatabaseReplyCovering IndexesHi Leo,as far as i know in older releases the optimizer just stopped after the first matching index was found, but now it should be based on cost estimates.Teradata allows different index types on ...
19505 Oct 2007 @ 03:00 PDTAnalyticsReplyCSUM or SUM: Can work with this?Hi Benjamin,OLAP again ;-)But a bit more complicated, because you want a reset if the cust_id changes *OR* grp changes within ordered rows by cust_id, start_dateSELECT Cust_Id, Start_Date, End_Da...
19405 Oct 2007 @ 02:55 PDTDatabaseReplySQL to generate a column name listHi hurstrescue,dbc.columns returns a lot of NULLs for views and HELP VIEW is based on dbc.columns.But "HELP COLUMN viewname.*" is resolved by the PE and thus returns better info.Dieter
19305 Oct 2007 @ 02:52 PDTDatabaseReply[HELP] How to collect statistics...Hi karmakoma,you can't collect stats on a single partition.But at least you can collect stats on the pseudo-column PARTITION, which is really fast.Dieter
19205 Oct 2007 @ 02:48 PDTAnalyticsReplyHow to Create A Date From Preceding Date?Hi Benjamin,this is a task for OLAP:MIN(end_date) OVER (PARTITION BY Cust_Id ORDER BY end_date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) Dieter
19130 Sep 2007 @ 03:03 PDTUDAReplyQuery needs to run fasterHi John,your narrative and your query are a bit confusing to me. Could you please post the actual query, explain and some info about PIs and Statistics.There should be no need for a temp table. And...
19026 Sep 2007 @ 04:16 PDTToolsReplyexport data with column name using Fast exportHi Ayes,FExp never exports column headers, so you have to start the export with a select returning a single row, e.g..export ...;select * from (select 'col1' || '|' || 'col2' as x) dt;your_select;Y...
18926 Sep 2007 @ 04:03 PDTToolsReplyProblem in writing MULTILOAD scriptA MLoad update *must* reference the PI of the table using equality to be able to calculate the RowHash and to determine which AMP is responsible for that row, but you wroe a "Bal_Date > :Bal_Date"A...
18826 Sep 2007 @ 03:42 PDTDatabaseReplyData DictionaryCheckOpt stores totally different infomation (of course this is bad practice, but i didn't implement it):If it's a table then set/multiset, if it's a view then it indicates if "WITH CHECK OPTION" w...
18725 Sep 2007 @ 02:31 PDTToolsReplysample to export dataThere are lots of examples how to export in different formats:Just check the BTEQ (Basic Teradata Query) manual for the EXPORT command or the Fastexport manual or SQL Assistant help.Dieter
18625 Sep 2007 @ 02:24 PDTDatabaseReplyCollecting column level statsCollect those stats on the GTT definition:collect statistics column week_id, column bond_id, column branch_id on myTable;After Insert/Select re-collect the predefined stats on the materialized vers...
18524 Sep 2007 @ 02:27 PDTDatabaseReplyAge CalculationHi Sam,intervals default to 2 digits, just modify that using year(4)Dieter
18420 Sep 2007 @ 04:30 PDTDatabaseReplyData DictionaryEtc. is probably included in dbc.tables :-)But Set/Multiset and temp table info isn't, so you have to query dbc.tvm directly:If it's a table (TableKind = 'T') -> CheckOpt = 'Y' Multiset else SetIf ...
18318 Sep 2007 @ 06:32 PDTToolsReplyInserting Integer into VarcharHi Ashish,those format are COBOL-style, e.g. '–(5)9' is the same as '-----9' which means 5 digits plus a floating sign padded with blanks. This is the default format for a smallint.Teradata-style...
18214 Sep 2007 @ 03:46 PDTUDAReplyinner join with or in the joinHi Paul,you have to get rid of the OR, because OR-ed join conditions lead to product joins in Teradata.There are two common ways to solve that problem:UNIONs/CASE:select Client, mydate, count(case...
18111 Sep 2007 @ 10:56 PDTAnalyticsReplyfastload perform better that multiload while loading empty tables--reason?Hi Syam,the Acquisition phase of MLoad is comparable to a FastLoad into the Worktable (i don't know about the internals, but it's probably using the same technique). And there's some additional inf...
18010 Sep 2007 @ 10:37 PDTUDAReplydifferance between in,exists ,= in correlated subqueriesHi bharath111,there shouldn't be any difference (just check EXPLAINs), but the first one could fail if there's more than one row per value.Dieter
17910 Sep 2007 @ 10:35 PDTUDAReplyMinusing row valuesHi quarry,it's called a moving difference:select col, min(col) over (order by col rows between 1 preceding and 1 preceding) as prev_val, col - coalesce(prev_val,0) as moving_difffrom tabDieter

Pages