#DateForumTypeThreadPost
102822 Jun 2011 @ 05:31 PDTDatabaseReplyCALCULATING THE NUMBER OF COLUMNS OF A SPECIFIC TABLEColumn information for all tables can be found in dbc.ColumnsV Dieter
102722 Jun 2011 @ 05:04 PDTDatabaseReplyConverting DECODE into CASE statementI think this should be: CASE NOTES_QS_CODE.name WHEN 'QS-9000 Certified' THEN NOTES_QS_CODE.name WHEN 'Unknown') THEN NOTES_QS_CODE.name ELSE 'Error' END And the DECODE is overly...
102622 Jun 2011 @ 05:03 PDTDatabaseReplysubstract timestampIt's both, missing paretheses and something else :-) SELECT COALESCE((time1 - time2 DAY TO SECOND), interval '0' second) Dieter
102517 Jun 2011 @ 12:07 PDTDatabaseReplyXplainIn the previous post you wrote "it saved 2 minutes", did you actually run it? It's hard to tell if those 2500 hours are just a bad estimate without knowledge about your data/stats/DDL/PKs/FKs. ...
102417 Jun 2011 @ 12:02 PDTDatabaseReplyXplainIf X_SUB_TYP is defined as NOT NULL then your NOT EXISTS is correct, else there might be NULLs because of the Outer Join *and* because of NULLs within X_SUB_TYP. I don't know of any specific SQ...
102317 Jun 2011 @ 08:31 PDTDatabaseReplyXplainThere are 4 joins, but it's a mixture of old and new syntax: FROM MKT_ACTN_F C join MKT_ACTN_TYP_D A on A.ROW = C.MKT_ACTN_TYP join MKT_OBJ_TYP_D B on B.ROW ...
102215 Jun 2011 @ 10:08 PDTDatabaseReplyCURRENT_DATE - 1 Just remove the comma and the format: WHERE a.TRNDTE = current_date - 1 AND a.KEY > (SELECT MAX (KEY) FROM MTGLIBP1.DAILYTRNPF) Dieter
102115 Jun 2011 @ 07:03 PDTDatabaseReplyCan I change the CASESPECIFIC attribute on a table after it has been created?Hi Mike, you have to use the ADD syntax: ALTER TABLE mytab ADD existing_column NOT CASESPECIFIC Btw, don't worry :-) Even your dba will not be able to change the UpperCaseFlag by way of an up...
102014 Jun 2011 @ 10:52 PDTDatabaseReplyCan somebody help to exec following SQL?Add a WHERE Position('|' in InCol) > 0 or use it within a CASE WHEN Position('|' in InCol) > 0 THEN ... Dieter
101914 Jun 2011 @ 09:59 PDTDatabaseReplyOptimizing a qryYour query doesn't look correct, you should only join on QueryID. Maybe step info is not enabled for your user. Or the QueryID is not displayed correctly in SQL Assistant. Compare the QueryID ...
101814 Jun 2011 @ 09:52 PDTDatabaseReplyExplain The only additional column in the GROUP BY is c3, which returns the day of week. A."DAY_DT" sounds like a date, i don't think there are different weekdays for any given date. When there are no du...
101713 Jun 2011 @ 03:11 PDTDatabaseReplyExplain Without stats the optimizer will use a NUSI only when access is based on equality, "NUSIcol = value". So stats on NUSI are usually recommended, read Carrie's articles on stats, especially http...
101613 Jun 2011 @ 03:01 PDTDatabaseReplyHow to use fetch in a dynamic cursorThis is copied straight from the manuals: CREATE PROCEDURE GetEmployeeSalary (IN EmpName VARCHAR(100), OUT Salary DEC(10,2)) BEGIN DECLARE SqlStr VARCHAR(1000); DECLARE C1 CURSOR FOR S1; SE...
101513 Jun 2011 @ 03:01 PDTDatabaseReplyOptimizing a qryWhenever any step accesses a table and shows "no confidence" there's no stats or the column is involved in a calculation. Costs are CPU and IO, in dbc.QryLogV there's only the actual AMPCPUTime an...
101413 Jun 2011 @ 03:01 PDTDatabaseReplyAge CalculationWorks as designed/according to Standard SQL. You might use the "months_between" Oracle UDF or ((date1 (int)) - (date2 (int))) / 10000 Dieter
101313 Jun 2011 @ 02:27 PDTDatabaseReplyOptimizing a qryAll but the first step show "no confidence", which means no stats or calculations on a column. But there's only one cast on ISSUE_DT (you can probably avoid it) and in other posts you usually got...
101213 Jun 2011 @ 02:13 PDTDatabaseReplyLock qsnThere's no (b)locking info in any dbc table. You need access to any tool using the Performance Monitor API, e.g PMon or the UDFs in syslib like MonitorMySessions. Dieter
101113 Jun 2011 @ 02:07 PDTDatabaseReplyGTT InstancesThere's one instance per GTT per session and "script" sounds like a single session. But you could simply add another column to distinguish between those different data sets. Dieter
101013 Jun 2011 @ 02:04 PDTDatabaseReplyNeed help optimizingHard to tell without additional info about DDL/stats/explain/DBQL. But my guess would be the inner query to calculate the "age", this is probably a product join due to the BETWEEN creating a lar...
100913 Jun 2011 @ 01:47 PDTDatabaseReplySQL help?@Vamsi Without any additional information my guess would be: SELECT something FROM my_Crystal_Ball @Rashmi Could you also provide some more information like the actual query and error message...
100813 Jun 2011 @ 01:41 PDTDatabaseReplyTeradata v13 There's a Release Summary for each new release showing all the new stuff. You just have to read those for V2R5.1/V2R6.0/V2R6.1/V2R6.2/TD12/TD13 :-) Dieter
100713 Jun 2011 @ 01:33 PDTDatabaseReplyNUSI subtable distributionYou'll find all the manuals at www.info.teradata.com Dieter
100613 Jun 2011 @ 01:32 PDTGeneralReplyHow to calculate query cost and compare 2 queries which gives the same result set?#1: Look at explain if the plans are different #2: Run the queries and check the QueryLog to see the actual resources used. Dieter
100508 Jun 2011 @ 03:27 PDTUDAReplyErr0r 5781- cannot collect stats on more than 32 groups of non-indexed columns teradataAccording to explain this query should run fast, did you check the actual vs. estimated row count? ACTVTY.CRTD_DT is an integer with yyyymmdd? Then CAST( CAST( F.crt_dt AS CHAR ( 8 ) ) AS DA...
100406 Jun 2011 @ 03:05 PDTDatabaseReplyHow to use fetch in a dynamic cursorWITH RETURN ONLY is for returning answer sets to the client, not for processing them within the SP, i think you just need to remove it. But do you actually need a cursor for your processing, you...

Pages