203 | 14 Oct 2007 @ 06:10 PDT | Database | Reply | Dynamic SQL and SELECT INTO | Hi 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... |
202 | 14 Oct 2007 @ 05:56 PDT | Database | Reply | What 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... |
201 | 13 Oct 2007 @ 02:43 PDT | Database | Reply | bizarre CAST/CASE problem | Hi 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... |
200 | 05 Oct 2007 @ 04:11 PDT | Database | Reply | [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... |
199 | 05 Oct 2007 @ 03:12 PDT | Database | Reply | How to increase Number of sessions | Hi rajan,increase number of sessions available for the system?Adding new PEs, each PE supports 120 sessions.Dieter |
198 | 05 Oct 2007 @ 03:10 PDT | Database | Reply | Access to DBC tables | Hi 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... |
197 | 05 Oct 2007 @ 03:06 PDT | Database | Reply | DBQL log for Modify User | Query 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 |
196 | 05 Oct 2007 @ 03:03 PDT | Database | Reply | Covering Indexes | Hi 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 ... |
195 | 05 Oct 2007 @ 03:00 PDT | Analytics | Reply | CSUM 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... |
194 | 05 Oct 2007 @ 02:55 PDT | Database | Reply | SQL to generate a column name list | Hi 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 |
193 | 05 Oct 2007 @ 02:52 PDT | Database | Reply | [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 |
192 | 05 Oct 2007 @ 02:48 PDT | Analytics | Reply | How 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 |
191 | 30 Sep 2007 @ 03:03 PDT | UDA | Reply | Query needs to run faster | Hi 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... |
190 | 26 Sep 2007 @ 04:16 PDT | Tools | Reply | export data with column name using Fast export | Hi 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... |
189 | 26 Sep 2007 @ 04:03 PDT | Tools | Reply | Problem in writing MULTILOAD script | A 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... |
188 | 26 Sep 2007 @ 03:42 PDT | Database | Reply | Data Dictionary | CheckOpt 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... |
187 | 25 Sep 2007 @ 02:31 PDT | Tools | Reply | sample to export data | There 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 |
186 | 25 Sep 2007 @ 02:24 PDT | Database | Reply | Collecting column level stats | Collect 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... |
185 | 24 Sep 2007 @ 02:27 PDT | Database | Reply | Age Calculation | Hi Sam,intervals default to 2 digits, just modify that using year(4)Dieter |
184 | 20 Sep 2007 @ 04:30 PDT | Database | Reply | Data Dictionary | Etc. 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 ... |
183 | 18 Sep 2007 @ 06:32 PDT | Tools | Reply | Inserting Integer into Varchar | Hi 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... |
182 | 14 Sep 2007 @ 03:46 PDT | UDA | Reply | inner join with or in the join | Hi 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... |
181 | 11 Sep 2007 @ 10:56 PDT | Analytics | Reply | fastload 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... |
180 | 10 Sep 2007 @ 10:37 PDT | UDA | Reply | differance between in,exists ,= in correlated subqueries | Hi 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 |
179 | 10 Sep 2007 @ 10:35 PDT | UDA | Reply | Minusing row values | Hi 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 |