453 | 12 Feb 2009 @ 04:16 PST | Tools | Reply | loading multiple files using multiload | MLoad allows just up to 4 files within a script. If you got lots of small files better use the Named Pipes Access Module instead and concat all the files into that.Dieter |
452 | 12 Feb 2009 @ 04:12 PST | Analytics | Reply | How to calculate a distance between point a to b by using Longitude and Latitude | First you have to define "distance", because there are several ways to calculate this :-)For the "great circle distance" (http://en.wikipedia.org/wiki/Great_circle) there's already a UDF at http://... |
451 | 03 Feb 2009 @ 12:23 PST | Database | Reply | Regarding LEAD (analytical function) function in TERADATA | Hi Koushik,it's easy to rewrite LEAD/LAG:SELECT last_name, hire_date,--LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS "NextHired"MIN(hire_date) OVER (ORDER BY hire_date rows between 1 following an... |
450 | 30 Jan 2009 @ 04:34 PST | Database | Reply | Determining Gaps in Date Ranges | Just add another calculation:SELECT tab.*, eff_dt - MIN(exp_dt) OVER (PARTITION BY loc ORDER BY exp_dt ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS diff, exp_dt - MAX(eff_dt) OVER (PARTIT... |
449 | 30 Jan 2009 @ 04:20 PST | UDA | Reply | Why does optimizer access empty partitions also when filtered for a date range? | Because only *you* know that a partition is empty, but the optimizer doesn't.Even if statistics exist, they might be wrong.Dieter |
448 | 30 Jan 2009 @ 04:17 PST | Tools | Reply | How to fastlaod a file which is actually fastexported | Hi Satish,if you do a FastExport without any FORMAT then it's exporting in internal format (FORMAT FASTLOAD) including NULL indicators.To load that format using FastLoad you have to use SET RECORD ... |
447 | 30 Jan 2009 @ 04:05 PST | Tools | Reply | SQL Right Justify column | In Teradata there are two different ways to do a typecast:- new ANSI style using CAST(col AS newdatatype) -> left aligned- old Teradata style using col (newdatatype) -> right alignedSELECT 22... |
446 | 23 Jan 2009 @ 04:25 PST | UDA | Reply | date function | This problem is typically solved doing a calculation based on a known date:0001-01-05 was a friday (just don't ask why), thus (( x - DATE '0001-01-05') MOD 7) returns a value between 0 and 6 (0 rep... |
445 | 23 Jan 2009 @ 03:42 PST | Database | Reply | Counting NULLs in columns for TOP N records | The data is already loaded to the Teradata system and they need that information for further processing?select count(*) - count(col1) as col1nulls, 100 * (col1nulls - count(col1)) / cnt as col1... |
444 | 23 Jan 2009 @ 01:57 PST | Database | Reply | Vertical Pivoting | Have a look at that thread:http://www.teradataforum.com/teradata/20060710_173415.htmDieter |
443 | 23 Jan 2009 @ 01:43 PST | Database | Reply | Determining Gaps in Date Ranges | SELECT DISTINCT locFROM ( SELECT loc, eff_dt - MIN(exp_dt) OVER (PARTITION BY loc ORDER BY exp_dt ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS diff FROM tab QUALIFY diff > 1... |
442 | 23 Jan 2009 @ 01:33 PST | Database | Reply | How does teradata store rows. | What do you mean by "denormalized" storage?Teradata stores data like most traditional databases in rows.You'll find all the details in the Teradata Database Design manual.Dieter |
441 | 23 Jan 2009 @ 01:30 PST | Database | Reply | Counting NULLs in columns for TOP N records | The result will be different:count(columnname) counts only non-NULL rows, but count(*) counts all rows regardless of NULLs.In your case "count(columnname) where columnname is null" will return 0.Bu... |
440 | 19 Jan 2009 @ 12:13 PST | Database | Reply | Compressing a Character Column containing non-alphabetic characters | Hi Alistair,there's no problem with (, because it's within the string, but a ' must be doubled:compress('Press Ad (Newspaper, Best buy table)','Internet: Ad on Alistair''s website')Dieter |
439 | 18 Jan 2009 @ 03:51 PST | Database | Reply | access rights | Without any other right? No.But a useless right like "checkpoint" on a table without permanent journal would suffice.Or "grant select on dbc.tvm", which allows SHOW on any object.Dieter |
438 | 18 Jan 2009 @ 03:46 PST | Database | Reply | sum part of sql takes a lot longer time | Without any information this is hard to tell.If it's a query with some joins on large tables in it try to calculate the sum as soon as possible in a Derived Table, e.g.select sum(t1.col), ...from t... |
437 | 18 Jan 2009 @ 03:41 PST | Database | Reply | Top 5 By Category | Your QUALIFY returns the 5th highest item, so change it to "<=" instead of "="Dieter |
436 | 18 Jan 2009 @ 06:23 PST | Analytics | Reply | Problem merging historical data | Even worse, when i edit the post, each linebreak is doubled (as a punishment for working on a mac?)Dieter |
435 | 18 Jan 2009 @ 06:15 PST | Analytics | Reply | Problem merging historical data | Hi Roland,this is a tough one.At the first glance it looked like a typical OLAP query based on a "SELECT start_date, ... UNION ALL SELECT end_date, ..." Derived Table.But i couldn't think of any w... |
434 | 14 Jan 2009 @ 12:42 PST | Tools | Reply | Query Scheduler email address for notification | Is the Teradata Manager runing as a service?Did you follow the instructions in the Teradata Manager User Guide?Chapter 15: Using Alerts to Monitor Your SystemConfiguring the System to Send E-mail A... |
433 | 14 Jan 2009 @ 12:34 PST | UDA | Reply | Code conversion to Teradata from Oracle or MS SQL Server | It mainly depends on the code.If it's plain SQL and the Oracle UDFs provided by Teradata are installed on your system you could do a 1:1 conversion.If there are lots of MS Stored Procedures or Orac... |
432 | 14 Jan 2009 @ 12:25 PST | Database | Reply | SELECT TOP 2 PER GROUP | The answer to your problem is an OLAP function:SELECT Name, JobType, JobIDFROM JOBSWHERE JobEndDate between StartDate and EndDatequalify row_number() over (partition by Name, JobType order by JobT... |
431 | 13 Jan 2009 @ 04:55 PST | Tools | Reply | SQL Assistant - Letters not field names appear in Answer Set header | I remember that, IIRC it was a bug in SQL Assistant when the query started with a comment.Try to install the latest patch level of SQLA.Dieter |
430 | 13 Jan 2009 @ 04:53 PST | UDA | Reply | How to CREATE , INSERT TABLES INTO PERSONAL DATABASE FROM THE COMPANY'S DATA WAREHOUSE | Your screenshot shows an ODBC connection to an MS SQL Server, which doesn't support CREATE TABLE AS.But the SELECT INTO syntax is used in MS SQL to materialize a new table, this should work if the ... |
429 | 07 Jan 2009 @ 05:09 PST | Tools | Reply | fast export | Hi Ramya,any formatting is done within the select statement.Dieter |