#DateForumTypeThreadPost
255325 Jan 2014 @ 12:16 PSTDatabaseReplyRDBMS failure, 3538: A MultiLoad UPDATE Statement is Invalid The rules for PIs in MLoad apply also to PPI: All Primary Index columns plus all partitioning columns must be specified in the WHERE-caluse and must not be modified by an UPDATE, i.e. the ROWID ca...
255225 Jan 2014 @ 12:06 PSTDatabaseReplyQuery to find tables with no stats defined/old statisticsDid you run my the version for TD13 on a TD14 system? The old statsinfo will still run and return NULL for everything, as stats are no longer stored in tvfields and indices. You have to use the T...
255125 Jan 2014 @ 01:12 PSTDatabaseReplySubstring ExtractYou don't need to specify the length when you want to get all chatacters until the end of the string, so this can be further simplified: SELECT SUBSTRING(long_str FRO...
255024 Jan 2014 @ 07:48 PSTDatabaseReplyConverions of timestamp to FORMAT 'MM/DD/YYYYBHH:MI:SS'Hi Nishant, works for me in both BTEQ and SQL Assistant over ODBC/.NET: SELECT '01/23/2014 11:53:50.000000' AS ts, ts (TIMESTAMP, FORMAT 'MM/DD/YYYYBHH:MI:SS.S(6)') AS a, ...
254924 Jan 2014 @ 05:41 PSTGeneralReplyusing a calculation done in the previous row in this row's calculationYes, you need to Cross Join years and companies, preferable not on the big table, better use the comanies table. But of course it's still a an extra step. Regarding EXPAND ON, i wrote a simila...
254824 Jan 2014 @ 01:26 PSTDatabaseReplyGetting week of the year from a dateThis is quite similar to the week_of_year calculation in sys_calendar (as long as week 1 of 2014 doesn't include 2013-12-31). Using sys_calendar.calendar: ((day_of_year - day_of_week) ...
254724 Jan 2014 @ 01:12 PSTDatabaseReplySQL WorkaroundHi Kelvin, 130+ records is not skewed - unless the "+" includes at least 5 to 6 diigt numbers :-) What's the PI of those tables and number of rows? Which table is redistributed bas...
254624 Jan 2014 @ 01:02 PSTAnalyticsReplyGROUP BY ROLLUPI t's not clear waht you want, at least for me. Are you talking about a single table? Can you please elaborate on your tables, data and rules? Btw, you don't need to do an aggregation wh...
254524 Jan 2014 @ 12:56 PSTGeneralReplyusing a calculation done in the previous row in this row's calculationThat's quite complicated. A possible solution is a Cross Join between the list of years and the list of companies to create all combinations, followed by an Left Join to your query and finally...
254424 Jan 2014 @ 12:46 PSTToolsReplynew line character@Raja: I assume Ekladios wanted to avoid oReplace because it's not installed on his system. If there's no oReplace the probability to for a RegExp function is almost zero :-)   @Ek...
254324 Jan 2014 @ 12:03 PSTDatabaseReplyDelete or Drop a table if it already existsREPLACE VIEW is not a new feature in TD14, you can do that since at least V2R2 :-) Other DBMSes use CREATE OR REPLACE VIEW syntax for this functionality. But there's no REPLACE TABLE syntax w...
254223 Jan 2014 @ 09:36 PSTDatabaseReplyLagging variablesYou need an ORDER BY: select account_id, date, flag_1, max(flag_1) over (partition by account_id order by date rows b...
254123 Jan 2014 @ 06:23 PSTDatabaseReplyStored Procedures and I/OCan you share a bit more details? Is it the same target table for all queries? Do you run that SP in ANSI or Teradata mode? Are there any Secondary/Join Indesex? Do you also DELETE data?
254023 Jan 2014 @ 03:49 PSTDatabaseReplyROWID in TeradataIn fact there is a ROWID for each row, but it's usage has been disabled in V2R5, mainly due to Oracle users abusing it :-) The keyword ROWID was implemented in V2R3 and was supposed to be used...
253923 Jan 2014 @ 01:43 PSTDatabaseReplySQL statements in UNION executes serial or parallelHi Bikky, a UNION needs to insert both results into the same spool -> the individual SELECT might run in parallel (if they need multiple steps due to joins, etc), but the final inserts are alwa...
253822 Jan 2014 @ 01:32 PSTDatabaseReplyGetting week of the year from a dateHi San, when you want 1st of january to be in week 1, both sys_calendar and iso calendar will fail. Can you elaborate about the rules for this calendar? When does a week start, sunday or monday?...
253722 Jan 2014 @ 09:39 PSTGeneralReplyStatus of queriesMonitorSession returns info about all sessions, only DBAs are usually allowed to do this. If you only want to see your queries you can try: SELECT * FROM TABLE (MonitorMySessions()) AS dt; If ...
253622 Jan 2014 @ 09:35 PSTDatabaseReplyGetting week of the year from a dateWhat's you Teradata release? <13.10?
253522 Jan 2014 @ 01:51 PSTDatabaseReplyWhich is efficient?Derived Tables are only materialzed when there's something like DISTINCT/GROUP BY/OLAP.  Otherwise they are already resolved by the parser. Also any unused columns are removed from the pa...
253421 Jan 2014 @ 06:23 PSTDatabaseReplyFind usage #1: probably CpuTime or CptPct #2: of course there's no idle, etc., to which WD this should be assigned. The max available CPU time will not change unless you add new nodes or upgarde the CPU...
253321 Jan 2014 @ 06:12 PSTTeradata StudioReplyGetting row countsHi Francine, I didn't know about that restriction of the JDBC driver, that sounds really bad :-( I always liked to know in advance how many rows will be returned, I think I'll better keep...
253221 Jan 2014 @ 01:22 PSTGeneralReplyteradata linked table in access database how to format date in stringYou can pass a date as a string '2000-01-01' but it must match the FORMAT of the target column. There's only one recommended way to write a date in Teradata: DATE '2000-01-01',...
253121 Jan 2014 @ 01:10 PSTDatabaseReplyCast-DATEHi Sri, you can extract year/month/day using MOD/DIV, e.g. CAST(((x MOD 100) + CASE WHEN (x MOD 100) < 20 THEN 100 ELSE 0 END) * 10000 -- year 19xx or 20xx + (x / 10000) * 100 -- month...
253021 Jan 2014 @ 01:04 PSTDatabaseReplyFind usage You'll find similar CPU info in several other ResUsage tables. If your calculation is based on WDs you should check dbc.ResSpsView. DBQL is not really reliable for consumed CPU seconds, not a...
252920 Jan 2014 @ 11:37 PSTDatabaseReplyFind usage Check dbc.ResUsageScpu/ResScpuView.  This is from the Resource Usage Macros and Tables manual: The CPU utilization columns are aggregates representing all CPUs on the node. CPU u...

Pages