#DateForumTypeThreadPost
72821 May 2010 @ 11:44 PDTDatabaseReplyDesperately need ur help!It's probably because of integer division in your CNT_tasks calculation, try SEL cast(COUNT(*) as dec(10,0)) TOT_CNT Dieter
72721 May 2010 @ 10:37 PDTDatabaseReplyDesperately need ur help!The simplest approach involves a non-equi join over hierarchy, which might cause a lot of cpu-usage, but it mainly depends on the number of hierarchies. You just have to calculate the ranges of ta...
72621 May 2010 @ 07:52 PDTDatabaseReplyDesperately need ur help!I lost my harddrive in my MacBook and had to fix it first. The good thing about it, i replaced it with a SSD and now it's increadibly fast :-) I not sure if i actually understand your problem,...
72518 May 2010 @ 01:51 PDTAnalyticsReplyNeed help with OLAP or If function in subqueryYou probably just have to add another WHEN: case when i_ird like 'CH%' then 0 when i_ird like 'EE%' then 1 else 1 end Dieter
72417 May 2010 @ 01:51 PDTToolsReplyspaces became <null> after mload/tpumpDo you mean strings like ' ' are converted to NULL, too? It's based on the comparison rules for strings: The shorter string will be padded with strings before comparison. It seems like ...
72317 May 2010 @ 01:37 PDTDatabaseReplyMerge Join with OVERLAPS producing huge spool fileDUMPS.TRUCK_EQUIP_KEY = OES.EQUIPMENT_KEY The optimizer assumes it is a many-to-many join (which results in a kind of product join per equipment key) and can't calculate the actual numbers correct...
72217 May 2010 @ 01:26 PDTDatabaseReplyOptimizer Lies :) Let's assume both tables are large (because they are partitioned): A PI on a column with a small number of distinct values is rather stupid. Partitioning a single-column NUPI on business_date b...
72117 May 2010 @ 01:15 PDTDatabaseReplyNeed help with subquery IF/THEN statement, or OLAP functionYou probably need a QUALIFY to pick the "best fit", just a wild guess: LEFT JOIN (select ... from filter ltc_p.vltc_er_ird_mo qualify row_number() over (partition by i_pol order by case whe...
72017 May 2010 @ 01:06 PDTDatabaseReplyHow to create records from a defined rangeThe only way to get rid of the cross join would be a table UDF creating those ranges "on the fly", but then there's still that huge spool. If the range is usually small you could calculate the m...
71917 May 2010 @ 12:57 PDTDatabaseReplyNeed to create a Terdata QuerySee if this fits your requirement: select * from tab qualify row_number() over (partition by student_id order by com_date, case when course_name ='MLN' then 0 else 1 end) = 1 Dieter
71817 May 2010 @ 12:49 PDTDatabaseReplyNeed help in Optimizing query!If this is the actual query, it's quite stupid, what kind of "user interface" created it? - a DISTINCT on an already DISTINCT result set - an OUTER join which is actually an INNER join, but at ...
71717 May 2010 @ 11:38 PDTDatabaseReplySql queryWhat about team yellow/orange? select tab.*, TotalCount * Ratio / sum(Ratio) over (partition by Hierarchy) from tab Dieter
71610 May 2010 @ 03:41 PDTDatabaseReplyQuick Q for Dieter Noeth or People who follow DN's Stats query..!!!I don't know how to check for 32/64-bit and i would appreciate any way to calculate that using some simple SQL :-) If you can submit any HELPP PROCEDURE xxx ATTRIBUTES there's a column PLATFORM:...
71502 May 2010 @ 11:05 PDTDatabaseReplyImplementation of while loop in TD sqlHi Chandru, if you SHOW TABLE INV_REF_TEMP you'll see ON COMMIT DELETE ROWS. Just change it to COMMIT PRESERVE ROWS. Btw, in Teradata there's a LOOP, too, you just have to write it within a ...
71402 May 2010 @ 11:02 PDTDatabaseReplyMaxParseTreeSegIt's field #4 in the Performance group of dbscontrol. But better check with your Teradata support before increasing it on your own. Dieter
71302 May 2010 @ 10:58 PDTDatabaseReplyMTJI self joinsAFAIK there's no way to use the same table more than once in a Join Index. Dieter
71202 May 2010 @ 10:54 PDTDatabaseReplyjob in teradata..Which load tool do you want to use? Is the text fixed width or delimited? How many rows will be loaded? A single text file for all tables? Dieter
71102 May 2010 @ 10:52 PDTDatabaseReplyBteq import..Theoretically there's no limit, practically it depends on how much time you have :-) BTEQ (especially when PACK is used) might load a few thousand rows faster than FastLoad/MLoad/TPump, because ...
71002 May 2010 @ 10:47 PDTDatabaseReplyAlias performanceThe optimizer should be smart enough not to re-calculate the same substring repeatedly. If you look at explain you will see (on TD12) that the optimizer just replaces the alias with the base calc...
70922 Apr 2010 @ 08:14 PDTGeneralReplyNeed clarification in PI.....If this is the actual query you submit, then it will result in an Inner Join (check explain), because there's a where-condition on the inner table, which removes all the NULLed rows: B.DTSTRTCHRG ...
70822 Apr 2010 @ 08:03 PDTToolsReplyDelete using MloadWhy do you want to use MLoad to delete all the rows from a partition? I don't think it's partition-aware, it's always going for a FTS. Just delete the partitions using an SQL DELETE, this shoul...
70722 Apr 2010 @ 07:55 PDTDatabaseReplyExplain PlanA product join is not always bad. The optimizer might choose it for an equi-join because it's the most efficient way, especially if the duplicated table is small. Check the estimated number of ...
70613 Apr 2010 @ 11:59 PDTGeneralReplyUpdate SpaceIt's not a SQL command, it's a stand-alone utility. You run it from the Database Window or command line. Check the Utilities manual for details on "updatespace". Dieter
70511 Apr 2010 @ 02:59 PDTDatabaseReplyProbelm with Query BandHi Somnath, this is a strange error message. Query band is available since TD12, are you running on a lower version? Dieter
70411 Apr 2010 @ 02:45 PDTDatabaseReplyQuery Band SyntaxNo, it's part of the syntax. So why not just adding the semicolon? Dieter

Pages