253 | 21 Nov 2007 @ 07:36 PST | Database | Reply | How to cast the timestamp | Hi Fred,is the session time zone is set, then he'd better use TIMESTAMP WITH TIME ZONE :-)Casts to time normalize the time, but you can de-normalize it, too:SET TIME ZONE INTERVAL '03:00' HOUR TO M... |
252 | 21 Nov 2007 @ 07:13 PST | UDA | Reply | How to create a Macro passing table names as the parameter | You can't pass any database/table/column-name as parameter to macros.The only way is a Stored Procedure using Dynamic SQL.Dieter |
251 | 21 Nov 2007 @ 07:11 PST | UDA | Reply | How to cast the timestamp? | Hi Rahul,WHERE CAST(start_tmstp AS TIME(0)) BETWEEN TIME '00:00:00' AND TIME '04:59:59'Dieter |
250 | 21 Nov 2007 @ 07:09 PST | UDA | Reply | derived table | Instead of three Derived Tables you can rewrite it using a single DT:Select z.acct_id,da1.last_month,da1.two_mths_ago,da1.three_mths_agoFrom CE1393_Join_AC zLeft Join(Select acct_id,Sum(case when c... |
249 | 20 Nov 2007 @ 08:40 PST | Analytics | Reply | Ip address of Server | Hi Praneet,within hosts there must be an entry like this for a logical name XXX:ip-address1 XXXcop1ip-address2 XXXcop2...Dieter |
248 | 20 Nov 2007 @ 08:36 PST | Analytics | Reply | Regarding Space Management In Teradata | Hi Praneet,dbc.databasespace is the base table used by dbc.tablesize, dbc.diskspace and dbc.allspaceThe ALL table is a dummy and represents all tables within a database -> dbc.diskspaceIt's a bi... |
247 | 19 Nov 2007 @ 03:02 PST | Analytics | Reply | Regarding Space Management In Teradata | Hi Praneet,DBC.TableSize accessed in your third query reports currentperm per table per AMP.So those 10132992 Bytes on AMP 50 are used by the largest table within that database, because you used M... |
246 | 19 Nov 2007 @ 02:50 PST | Analytics | Reply | Ip address of Server | Hi Praneet,there's no system table for that. IP-addresses are resolved by CLI/ODBC using the network's name resolution facility, e.g. DNS, WINS, hosts-file.COP1 is added to the system's logical nam... |
245 | 19 Nov 2007 @ 02:45 PST | Analytics | Reply | Amp Sharing | Hi,what do you mean by "share the amps across different platforms"?Different hardware generations, e.g. 5400 and 5450 within a system?Mixing OSes, Windows and Unix, within a system?Nodes within a c... |
244 | 17 Nov 2007 @ 08:57 PST | Database | Reply | Cannot start Teradata 6.1 | Check the windows eventlog for Teradata related errors within the application branch (and post them).Dieter |
243 | 17 Nov 2007 @ 08:55 PST | Database | Reply | How to schedular job(such as procedure,function,macro) in teradata | Job scheduling is usually done using a Unix cron-job.It can also be done using Teradata Manager.Dieter |
242 | 17 Nov 2007 @ 08:53 PST | Database | Reply | comparing the date with 11 months | Hi Rahul,i didn't understand exactly what you want, but it looks like:delete from table driver where insert_dt > add_months(insert_dt, -11)Of course this will delete all the rows, so you probabl... |
241 | 17 Nov 2007 @ 08:47 PST | Analytics | Reply | Date Validation | Hi John,CHR_VAL_CD might be an invalid date, even when CHR_CD field is 'RETIREDT'?Is there just one date format, e.g. yyyymmdd?You can't use a typecast, because "We do not want to abend the code in... |
240 | 14 Nov 2007 @ 07:01 PST | Analytics | Reply | Version Related Info | select * from dbc.dbcinfo;Dieter |
239 | 14 Nov 2007 @ 07:00 PST | Database | Reply | Dynamic SQL with Terdata | You probably submit your query as a multistatement request, but within any request there might be only a single DDL statementIf you run it within QueryMan using F5 it's send step by step.If you sub... |
238 | 09 Nov 2007 @ 05:39 PST | Analytics | Reply | Overlapping Date SQL | This time i actually tested the query :-)
select
comm_cnt,
count(*)
from
(
select CustomerID,
max(overlap) as comm_cnt
from
(
select CustomerID, Dat,
... |
237 | 09 Nov 2007 @ 04:46 PST | Analytics | Reply | Overlapping Date SQL | Ooops, i can't delete that previous post.I just recognized that it will not work, i'll have to think about another solution.Dieter |
236 | 09 Nov 2007 @ 04:42 PST | Analytics | Reply | Overlapping Date SQL | If the number of rows per CustomerID is low there's a simple solution:select cnt, count(*)from ( select t1.CustomerID, count(*) as cnt from tab t1 join tab t2 on t1.CustomerID = t2.CustomerID ... |
235 | 08 Nov 2007 @ 07:17 PST | Analytics | Reply | string surch problum | Hi Rahul,you could use where col not like any ('%A%','%B%','%C%',...), but this is quite ugly, too.If there's the Oracle Translate function installed on your system, it's easy:compare the string wi... |
234 | 08 Nov 2007 @ 07:10 PST | Database | Reply | What is Multi-Level Partitioning in TD 12 | The new manuals for TD12 are available for download since a few days, you'll find all the details within the Database Design manual.Dieter |
233 | 08 Nov 2007 @ 07:07 PST | Analytics | Reply | QUANTILE(100) x 50 times... | Hi Tim,"I thought I could perfrom QUANTILE(100, ) upon the 50 columns in a single statement. Running the analysis against just one column gives me a result in 30 secs. EXPLAIN for 50 columns qu... |
232 | 06 Nov 2007 @ 07:08 PST | Database | Reply | Soft Referential Integrity | No and no :-)There's a lot of info about that in the DDL manual: Create TableDieter |
231 | 06 Nov 2007 @ 07:01 PST | Database | Reply | Collect Stats on Primary Indexes | If the stats are collected on all PI columns as multicolumn stats there's no difference between index or column stats. It's just easier to write "Collect stats on tablename index indexname" than "C... |
230 | 06 Nov 2007 @ 06:57 PST | Database | Reply | sub queries | Teradata performs a query rewrite and replaces any kind of [correlated] subquery with joins.Thus in many cases there will be no difference between Subquery and Join, just check Explain.You just hav... |
229 | 06 Nov 2007 @ 06:52 PST | Tools | Reply | Teradata Manager internal queries | If i want to know about queries submitted by an application i simply switch on Access Logging for that user.But some of the manager queries can be found in the SQL subfolder, e.g. the *.hst are the... |