#DateForumTypeThreadPost
75314 Jun 2010 @ 04:18 PDTDatabaseReplyHow do these queries execute?There's a simple rule: *First* there's the join based on every condition in ON, *then* the WHERE-conditions are applied. a: "a.n_name is null" is part of the JOIN-condition, so it's returning a...
75214 Jun 2010 @ 04:02 PDTDatabaseReplyUDF CompilationIf this is yor actual C-source, there's a lot missing :-) You should start reading the Orange Book on UDFs (http://www.teradata.com/DownloadCenter/Topic9233-132-1.aspx) or the modules about UDFs...
75114 Jun 2010 @ 03:44 PDTDatabaseReplyHow to get the list of procedures?It's the same way you get the list of tables, it's just a different TableKind: select * from dbc.tables where databasename = your_db and TableKind = 'P' Dieter
75013 Jun 2010 @ 01:22 PDTDatabaseReplyProblem: concatenate multi rows in one row and column Hi Marco, http://www.teradataforum.com/teradata/20060710_173415.htm But the fastest (and easiest) solution is an Aggregate UDF. Dieter
74910 Jun 2010 @ 11:25 PDTGeneralReplyNeed clarification in PI.....Why do you insist on a Hash Join? The plan is as good as possible. It's a direct merge join using the existing PPI without any preparation. There's no need to change the PI, but you should m...
74810 Jun 2010 @ 10:43 PDTDatabaseReplyHow to generate permutations using SQL?Simply CROSS JOIN those tables: select * from table1 CROSS JOIN table2 CROSS JOIN table3 or using old comma delimited syntax: select * from table1, table2, table3 Dieter
74709 Jun 2010 @ 12:23 PDTDatabaseReplyinsert records time incrementalSorry, but i don't understand your question. Could you please elaborate on what you actually want to achieve? Dieter
74609 Jun 2010 @ 12:20 PDTDatabaseReplyNeed help with Unicode in DDL issueHi Jonas, the data dictionary is capable of storing unicode data, the view source code is stored in dbc.tvm.createtext/requesttext, which are both declared as unicode. REPLACE VIEW D_ORG_PSC_HI...
74508 Jun 2010 @ 11:40 PDTDatabaseReplyProblem with DBQLogTbl dataNo, FirstRespTime - StartTime is the elapsed time needed to create the answer set. AMPCpuTime is the cpu seconds used by all AMPs to create that answer set. As you got lots of CPUs you might ...
74408 Jun 2010 @ 11:33 PDTDatabaseReplyTeradata dbms structure and navigation, catalog commands?Hi Steve, if you know the database name there's a "Find" in the "Edit" menu. dbc is the root for all databases/users. As database/user names are unique you don't have to know the "path" to a d...
74331 May 2010 @ 03:27 PDTDatabaseReplyHow to get the integer part of a numeric fieldIt's CAST(xx as BIGINT). There's no CONVERT in SQL/Teradata, if it worked you probably used SQL Assistant and tools -> options -> query -> "allow use of sql extensions in queries" was checked. T...
74228 May 2010 @ 01:42 PDTDatabaseReplyHilite and execute, stacking sql in macrosNo menu? I didn't know you could hide the Menubar... Try ALT-T for the Tools menu. Or right click any toolbar and choose Customize, in the Options tab there's a "Reset my usage data" button, m...
74127 May 2010 @ 12:43 PDTDatabaseReplyDesperately need ur help!SELECT task_id, hierarchy, ROW_NUMBER() OVER (PARTITION BY hierarchy ORDER BY r) AS rn FROM (SELECT task_id, hierarchy, random(1,10000000) from tasks) as t Dieter
74027 May 2010 @ 11:33 PDTDatabaseReplyDesperately need ur help!You have to order by something random. As RANDOM is not allowed in OLAP-functions you could use HASHROW instead, it's not truely random but repeatable: SELECT task_id, hierarchy, ROW_NUMBER()...
73927 May 2010 @ 11:28 PDTDatabaseReplyHilite and execute, stacking sql in macros1. There's an option "submit only selected query text when highlighted" in options - query For some historical reason it's unchecked by default. 2. Of course you might have multiple DML stateme...
73827 May 2010 @ 08:05 PDTDatabaseReplyDesperately need ur help!5 days ago you wrote: "DIST_RT is a float." http://forums.teradata.com/forum/database/desperately-need-ur-help#comment-11190 If you changed it to integer/decimal just change it back to FLOA...
73727 May 2010 @ 03:13 PDTDatabaseReplyDesperately need ur help!This query just mops the floor, it's better to fix the leak. 1. Make B.DIST_RT a FLOAT 2. remove all the CASTs, thus DST_CNT will be a float, too 3. COALESCE(CAST(SUM(CNT_tasks) OVER (PAR...
73626 May 2010 @ 02:18 PDTDatabaseReplyDesperately need ur help!Keep DST_CNT as a float and cast to decimal in the last step. Dieter
73526 May 2010 @ 01:34 PDTDatabaseReplyDesperately need ur help!It's supposed to work, add a TYPE(DST_CNT) to see if DST_CNT really a float. Dieter
73426 May 2010 @ 12:44 PDTDatabaseReplyDesperately need ur help!Ok, finally remove the cast and apply it to both moving sums of the outer query: CAST(SUM(CNT_tasks) OVER (PARTITION BY hierarchy ORDER BY CNT_tasks ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRE...
73326 May 2010 @ 11:25 PDTDatabaseReplyDesperately need ur help!Oops, copied the wrong query, cast to DECIMAL instead of INTEGER Dieter
73226 May 2010 @ 08:33 PDTDatabaseReplyOptimizer Lies :) For the table you created you don't need that partition, it's totally useless (just creates a larger table). Partitioning is best on columns which are used a lot in WHERE, but a PI only on that ...
73126 May 2010 @ 08:15 PDTDatabaseReplyDesperately need ur help!I don't get it, but it sounds like you have to add another derived table: SEL team,hierarchy, CAST(a.TOT_CNT * B.DIST_RT / SUM(B.DIST_RT) OVER (PARTITION BY B.TA_HIER_NUM) AS INTEGER) CNT_tasks...
73021 May 2010 @ 02:03 PDTDatabaseReplyDesperately need ur help!Is there really a fractional part in B.DIST_RT? Try casting to a DEC instead of INT: sel hierarchy, TOT_CNT , sum(CNT_tasks) over (partition by hierarchy) as x, from ( SEL team,hierarchy,a....
72921 May 2010 @ 12:32 PDTDatabaseReplyDesperately need ur help!What's the datatype of DIST_RT? Try following query to see the wrong values: sel hierarchy, TOT_CNT , sum(CNT_tasks) over (partition by hierarchy) as x, from ( SEL team,hierarchy,a.TOT_...

Pages