#DateForumTypeThreadPost
67809 Mar 2010 @ 08:41 PSTDatabaseReplyCalculate AMPs used by a queryHi Rahul, in dbc.qrylog there's NumOfActiveAMPs. Based on that it's easy to find out which queries used all AMPs: WHERE NumOfActiveAMPs = HASHAMP()+1 Dieter
67709 Mar 2010 @ 08:33 PSTDatabaseReplyStored procedures and macros - Perm spaceA MACRO is just source code, like a VIEW. The definition is stored in the Data Dictionary (= dbc tables), thus doesn't need any perm space in the target database. But the source code of an SP is...
67608 Mar 2010 @ 04:58 PSTDatabaseReplyvarchar concatenationCould you post the full CASE expression? Thus it's much easier to see why it's actually failing. Dieter
67501 Mar 2010 @ 10:20 PSTDatabaseReplyHow to interpret value of field ‘CreateDate’ in TDWM tables?Looks like a date in the internal Teradata format: (year - 1900) * 10000 + month * 100 + day 110 + 1900 = 2010 Dieter
67401 Mar 2010 @ 10:16 PSTDatabaseReplyNumeric overflow error. Querying 27+ billion row table. Finding ranges.I don't see any CASTing in your queries :-) ROW_NUMBER is a kind of COUNT, in a Teradata mode session it returns an INT. To do a count on a larger table you have to CAST(COUNT(*) AS BIGINT or D...
67324 Feb 2010 @ 11:53 PSTDatabaseReplycan someone give me a quick dirty answer?1. You don't want to get rid of the pseudo-locks :-) The request to set a table level lock is sent to each AMP in parallel, but it's not guaranteed to be received at exactly the same time on eac...
67224 Feb 2010 @ 08:03 PSTUDAReplyjoining table A to table B only once where condition satisfiedHi Chris,"Can you explain the following step please as I do not understand what you are looking for here?===============AND -- check for overlapping ranges((S1 >= S2 AND NOT (S1 >= E2 AND E1 ...
67123 Feb 2010 @ 12:27 PSTDatabaseReplyStored procedure--Bit tricky oneHi Suni,i still don't understand the rules:Why do you expect two rows abc 1 2 3 6 8 9 6 45 1234 4-Apr 6-Junabc 1 2 3 6 8 9 6 45 1234 6-Jun 7-Julinstead ofabc 1 2 3 6 8 9 6 45 1234 4-Apr 7-JulHopef...
67020 Feb 2010 @ 08:00 PSTUDAReplyTrying to Identify Logons that are not assigned to a the person whom it belongs to.Could you be are bit more precise? What's your definition of "not assigned to a the person whom it belongs to"?The Unix/Windows-username and originating ip-address is included in dbc.LogonOff.Logon...
66919 Feb 2010 @ 04:18 PSTUDAReplyjoining table A to table B only once where condition satisfiedHi Chris,hopefully this query returns exactly what you want, including reminders.It creates all requested combinations of sells/buys.Just try it.But please add "PARTITON BY client_id" to each of th...
66819 Feb 2010 @ 04:05 PSTDatabaseReplyDetermining max internal storage on a VARBYTE columnYep, there's a BYTES function.Dieter
66719 Feb 2010 @ 04:03 PSTDatabaseReplyHow to kill Teradata user sesiionAs you already tried the PMon API UDFs:Check the AbortSessions UDF.Dieter
66619 Feb 2010 @ 04:01 PSTDatabaseReplyHow to enable Session usage sampling in teradata 12.0Ok, now i know what you mean :-)3274 Data Unavailable: Session Usagesampling is not currently enabled.Explanation: The MONITOR SESSION request failedbecauase both the Local and the System-wide Sess...
66518 Feb 2010 @ 05:32 PSTDatabaseReplyHow to enable Session usage sampling in teradata 12.0I never heard about "Session usage sampling", what does it mean?Dieter
66418 Feb 2010 @ 05:30 PSTDatabaseReplyTime Bandsselect extract(hour from timecol), count(*) from tab group by 1?Dieter
66318 Feb 2010 @ 05:25 PSTDatabaseReplyAbout JoinHi Petr,this is a "find the gap" question:SELECT MIN(a), MAX(b)FROM ( SELECT a,b, SUM(change_indicator) OVER (ORDER BY b ROWS UNBOUNDED PRECEDING) AS grp FROM ( SELECT a,b, CA...
66218 Feb 2010 @ 04:57 PSTUDAReplyjoining table A to table B only once where condition satisfiedHi Chris,this is a kind of FIFO queue, which can be written using some OLAP plus OVERLAPS:SELECT *FROM ( SELECT client_id, amount, date_col, movement, SUM(amount) OVER (ORDER BY date_col ROWS U...
66118 Feb 2010 @ 08:57 PSTDatabaseReplyGlobal Tempoary Table ErrorTry to create that table in a database where you got the needed "create table" right. Your user doesn't need temp space to create a GTT, but perm space. If you try to create a GTT in your own u...
66018 Feb 2010 @ 08:52 PSTDatabaseReplyOptimizer expert, please correct my under standing hereYou're absolutely right, according to explain there can't be a merge join using those columns. Is the answer set correct? Then it might be a kind of explain bug. Dieter
65910 Feb 2010 @ 04:58 PSTToolsReplymload rounding decimal dataMLoad should easily load that decimal value without any rounding, so something different must be going wrong.You selects are probably submitted using SQL Assistant with Tools - Options - Answerset ...
65810 Feb 2010 @ 04:37 PSTToolsReplyHow to Capature number of rows inserted using stored procedureWithin a SP there's a built-in variable ACTIVITY_COUNT which is set to that number after each execution of a DML statement.Dieter
65710 Feb 2010 @ 04:33 PSTDatabaseReplyStored procedure--Bit tricky oneHi Suni,you probably don't need an SP for that, it's seems to be a query using OLAP-functions.But i can't understand your requirements, could you please post DDL + some INSERTs and a description wh...
65610 Feb 2010 @ 04:30 PSTDatabaseReplyTo get a latest record in teradata tableHi suni,"In Sql, using rowid and rownum function we can achieve the result."You probably mean "in Oracle SQL", because rowid and rownum is no Standard SQL :-)select *from PhoneTablequalify row_num...
65504 Feb 2010 @ 06:44 PSTDatabaseReplyOptimizer expert, please correct my under standing hereCould you please post at least the appropriate steps from explain. It's hard to tell without knowing the join types etc. Dieter
65404 Feb 2010 @ 06:40 PSTDatabaseReplySQL to Create a Table from a View & Retrieve the DDL Hi Ayyad, you're close: CREATE TABLE THIRTY_DAY_TABLES.NEW_TABLE AS (select * from SAMPLE_VIEWS.ACTUAL_SALES) WITH DATA PRIMARY INDEX (whatever); Just choose an appropriate PI else it defa...

Pages