#DateForumTypeThreadPost
45312 Feb 2009 @ 04:16 PSTToolsReplyloading multiple files using multiloadMLoad 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
45212 Feb 2009 @ 04:12 PSTAnalyticsReplyHow to calculate a distance between point a to b by using Longitude and LatitudeFirst 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://...
45103 Feb 2009 @ 12:23 PSTDatabaseReplyRegarding LEAD (analytical function) function in TERADATAHi 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...
45030 Jan 2009 @ 04:34 PSTDatabaseReplyDetermining Gaps in Date RangesJust 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...
44930 Jan 2009 @ 04:20 PSTUDAReplyWhy 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
44830 Jan 2009 @ 04:17 PSTToolsReplyHow to fastlaod a file which is actually fastexportedHi 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 ...
44730 Jan 2009 @ 04:05 PSTToolsReplySQL Right Justify columnIn 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...
44623 Jan 2009 @ 04:25 PSTUDAReplydate functionThis 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...
44523 Jan 2009 @ 03:42 PSTDatabaseReplyCounting NULLs in columns for TOP N recordsThe 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...
44423 Jan 2009 @ 01:57 PSTDatabaseReplyVertical PivotingHave a look at that thread:http://www.teradataforum.com/teradata/20060710_173415.htmDieter
44323 Jan 2009 @ 01:43 PSTDatabaseReplyDetermining Gaps in Date RangesSELECT 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...
44223 Jan 2009 @ 01:33 PSTDatabaseReplyHow 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
44123 Jan 2009 @ 01:30 PSTDatabaseReplyCounting NULLs in columns for TOP N recordsThe 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...
44019 Jan 2009 @ 12:13 PSTDatabaseReplyCompressing a Character Column containing non-alphabetic charactersHi 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
43918 Jan 2009 @ 03:51 PSTDatabaseReplyaccess rightsWithout 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
43818 Jan 2009 @ 03:46 PSTDatabaseReplysum part of sql takes a lot longer timeWithout 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...
43718 Jan 2009 @ 03:41 PSTDatabaseReplyTop 5 By CategoryYour QUALIFY returns the 5th highest item, so change it to "<=" instead of "="Dieter
43618 Jan 2009 @ 06:23 PSTAnalyticsReplyProblem merging historical dataEven worse, when i edit the post, each linebreak is doubled (as a punishment for working on a mac?)Dieter
43518 Jan 2009 @ 06:15 PSTAnalyticsReplyProblem merging historical dataHi 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...
43414 Jan 2009 @ 12:42 PSTToolsReplyQuery Scheduler email address for notificationIs 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...
43314 Jan 2009 @ 12:34 PSTUDAReplyCode conversion to Teradata from Oracle or MS SQL ServerIt 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...
43214 Jan 2009 @ 12:25 PSTDatabaseReplySELECT TOP 2 PER GROUPThe 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...
43113 Jan 2009 @ 04:55 PSTToolsReplySQL Assistant - Letters not field names appear in Answer Set headerI 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
43013 Jan 2009 @ 04:53 PSTUDAReplyHow to CREATE , INSERT TABLES INTO PERSONAL DATABASE FROM THE COMPANY'S DATA WAREHOUSEYour 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 ...
42907 Jan 2009 @ 05:09 PSTToolsReplyfast exportHi Ramya,any formatting is done within the select statement.Dieter

Pages