#DateForumTypeThreadPost
120306 Jan 2012 @ 04:14 PSTDatabaseReplyWhen are join Indexes being used by the optimizer? Q3: Yes, if the query can be processed using only the JI the optimizer will probably use it (if it's more efficient than accessing the base table). Q1: Based on Q3, should be Yes. Q2...
120206 Jan 2012 @ 03:51 PSTGeneralReplyJoin Index with derived tables or subquery TABLE1 ( app 30k) <--> 6,404,033 Unique Values TABLE2 (app 400K) <--> 820,489 Unique Values Either your numbers or the stats are totally wrong. Is TABLE1 partitioned? There...
120106 Jan 2012 @ 03:35 PSTPrivate Forum
120006 Jan 2012 @ 03:16 PSTDatabaseReplyUsing store procedure in SQL. Is it possible ? No, you can only CALL it. Dieter  
119906 Jan 2012 @ 03:08 PSTDatabaseReplySuggestion about collect stats Hi Sunny, you probably can't afford to (re)collect all those stats. Stats are only usefull when they actually change the optimizer's plan. There are some articles/blogs about...
119828 Dec 2011 @ 02:42 PSTPrivate Forum
119728 Dec 2011 @ 09:30 PSTGeneralReplyJoin Index with derived tables or subquery And how many rows are returned by that step? Could you post the collected stats? Dieter
119628 Dec 2011 @ 04:11 PSTGeneralReplyJoin Index with derived tables or subquery If you got 30k and 300k records with stats and the optimizer assumes 180m rows in spool, this looks like a bad join condition, not one-to-many, but many-to-many. Did you check the actual run...
119520 Dec 2011 @ 11:14 PSTDatabaseReplyUniqueness Value Hi Pat, of course you're right regarding collisions, but: If you actually start storing records from different tables on the same datablock you have to add the tableid to each record...
119420 Dec 2011 @ 11:01 PSTDatabaseReplyUniqueness Value You just have to add "and the same tableid" to the last statement. Both rows from different tables would have the same uniqueness value/rowid: All rows within an AMP are logically ...
119316 Dec 2011 @ 01:21 PSTDatabaseReplykilling a particular query in a session with out viewpoint/pmon Of course all queries within a session are logged with the same LogonTime, beause it's the session's logon time. Just put a SELECT SESSION; before the long running queries in SQL Ass...
119215 Dec 2011 @ 10:28 PSTDatabaseReplyTenure in years and months between two dates Hi Mike, 2 is only "incorrect" for a human being, but "correct" for Standard SQL :-) SELECT DATE '2007-01-31' AS dt1, DATE '2005-07-01' AS dt2,...
119115 Dec 2011 @ 01:12 PSTDatabaseReplykilling a particular query in a session with out viewpoint/pmon You can't have multiple requests running iin parallel within a single session. When you run a second query in SQL Assistant, it's doing following sequence in the background: logon a seco...
119014 Dec 2011 @ 11:48 PSTPrivate Forum
118914 Dec 2011 @ 04:52 PSTDatabaseReplyREPLACE RECURSIVE VIEW - ERROR 6926 You (or probably the forum software due to square brackets) didn't show the full error message: 6926 WITH (RECURSIVE) clause or recursive view is not supported within WITH (RECURSIVE...
118814 Dec 2011 @ 04:37 PSTGeneralReplyWhat is the alternative for CALL DBC.SYSEXECSQL? Double quoted strings are always object names, you need two single quotes instead: "Y" -> ''Y'' Dieter  
118709 Dec 2011 @ 09:34 PSTDatabaseReplyJoining tables issue What are you trying to get as a result? I can't follow. When you want some specific products, why there's no WHERE condition? This looks like a kind of pivot query, this is usual...
118609 Dec 2011 @ 09:23 PSTDatabaseReplySELECT-INTO returns more than one row Your description sounds like this should work: SELECT abs(period_bal) as  MAX_PB, code_fk (CASESPECIFIC) FROM table1 WHERE acc_no=12345 and period_dte= '2011-12-30&#...
118509 Dec 2011 @ 08:27 PSTDatabaseReplySELECT-INTO returns more than one row Of course if there's a 1-to-1 relation between acc_no and code_fk you could also get rid of the GROUP BY and do a simple MAX(code_fk). But if it's not 1-to-1 your SP will fail some day. ...
118409 Dec 2011 @ 07:15 PSTGeneralReplyWhat is the alternative for CALL DBC.SYSEXECSQL? What's the actual query in :SQL_QUERY? A SELECT? You can't do a SELECT with sysexecsql. In Td13.10 you could probably OPEN/FETCH it. And what's the error for problem 2? D...
118309 Dec 2011 @ 07:05 PSTDatabaseReplyRunning sum What's the datatype of "month"? If you can cast it to a date (preferably 1st of month), you can create a period on-the-fly using period(month_as_date, coalesce(min(m...
118209 Dec 2011 @ 06:22 PSTDatabaseReplyJoining tables issue 68710147? Number of rows? You don't need the second outer join, as you don't use any column from it. And do the aggregation before the join: Sel prd.product_name, prd1.p...
118107 Dec 2011 @ 06:22 PSTDatabaseReplyNUPI and USI Hi Pat, the criteria for choosing the PI are in order of importance: #1 WHERE and even more important JOIN access #2 distribution #3 volatility The logical Primary Key of a t...
118007 Dec 2011 @ 06:04 PSTToolsReplyERROR: Numeric overflow occurred during computation. Do you really think you can cast 206450004012925470 into a decimal with 8 digits? Dieter
117907 Dec 2011 @ 02:43 PSTDatabaseReplyGot the error "Multiple 'FORMAT' options" when run the SQL from Teradata Document. The available FORMATs are described in the "SQL Data Types and Literals" manual. Your client or ODBC seems to change the source code. You could check the QueryLog for the actual qu...

Pages