#DateForumTypeThreadPost
87819 Jan 2011 @ 10:53 PSTDatabaseReplySuggestion for a query..CREATE INDEX (END_DT) ON DP_EDW.TX0305_ACCT_BAL_TYPE_DD; But i don't know if this will be actually helpfull: What percentage of End_Dates are NULL, i.e. is the selectivity high enough for the o...
87719 Jan 2011 @ 10:24 PSTDatabaseReplyOPTIMIZED QUERYOf course it can be optimized. I don't know what you actually want to achieve, but those LEFT JOINs are useless. If you check EXPLAIN you'll see that the optimizer replaces them with INNER JOIN...
87619 Jan 2011 @ 10:17 PSTDatabaseReplyCode checkIf you remove the second join, you'll get a totally different result set. The first join retrieves all IDs from A where there's an OPEN status in B. The second join + WHERE removes those IDs wh...
87515 Jan 2011 @ 07:11 PSTDatabaseReply3798: A column or character expression is larger than the max size.1. The concatenated string must be less than 64000 chars. And this is not actual, but possible chars, so you better change the definition of "msg" to an appropriate size. Or cast it like SELECT...
87415 Jan 2011 @ 07:01 PSTToolsReplyMultiload errorYou probably dropped some Work-/Error-/Logtables. You should drop all of those and then drop the target tables, too or run an RELEASE MLOAD. LAYOUT is a part of the IMPORT command, so remove...
87313 Jan 2011 @ 10:47 PSTDatabaseReplySPOOL SPACE ERRORWhen you create a kind of sequence, there are no more duplicate rows, because the sequence makes all of them distinct. The DISTRINCT in your query is done *after* the calculation of the ROW_NUMBER...
87213 Jan 2011 @ 06:20 PSTDatabaseReplyLEFT OUTER instead of NOT IN...The NOT IN is the "exclusion" join, the PJ is because the optimizer thinks (based on the existing statistics) this is the most efficient way (based on the join-condition he could use other join typ...
87113 Jan 2011 @ 12:22 PSTGeneralReplyhow to use column as unicode literal .. for addin intrvl keywordWhat datatype is this column? Numeric? For days you don't need the interval syntax and you don't want it because it's limited to 9999 days. Simply use the old Teradata style: select current_d...
87012 Jan 2011 @ 11:38 PSTDatabaseReplySPOOL SPACE ERRORYou don't need a unique column combination, just more than the current number, which is exactly 1. Whitin an OLAP-function "ORDER BY 1" is *not* the ordinal position within the Select list, it's...
86911 Jan 2011 @ 06:34 PSTDatabaseReplyDifference in Inserting '1 ' and '1' It's not a bug, this is how the algorithm works, trailing blanks are removed for hashing. Dieter
86810 Jan 2011 @ 04:43 PSTDatabaseReplyLEFT OUTER instead of NOT IN...Now it's easy: When you define an alias "A" for DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST you have to use it throughout the query, it's not a secondary name, it's *replacing* the original tablename. I...
86709 Jan 2011 @ 10:54 PSTDatabaseReplyLEFT OUTER instead of NOT IN...It can't be the actual query as "sample" is a keyword which can't be used as a database name without double quotes and the target table is called DO_TEDW.TX0305_ACCT_BAL_TYPE_DD_TEST in explain bu...
86609 Jan 2011 @ 03:26 PSTDatabaseReplyDBS CRASHED OR SESSIONS RESETYou need Bridged Mode or NAT-forwarding to work with any VM on a bare metal VM server, so this should be easy to enable (I don't know how to forward a port on ESXi as i run VMWare Fusion on a Mac a...
86509 Jan 2011 @ 03:02 PSTDatabaseReplyLEFT OUTER instead of NOT IN...The product join in step 7 is suspicious, seems like a logical error in your query. Could you post the *actual* query and the table/view DDL? And you seem to update the partitioning column of t...
86407 Jan 2011 @ 03:38 PSTDatabaseReplyDBS CRASHED OR SESSIONS RESETIs 172.16.2.23 the address of the host running VMWare or the Teradata VM? The easiest solution is to set the VM network mode to "Bridged", then it's visible in the network like a real server. ...
86307 Jan 2011 @ 02:30 PSTDatabaseReplycombine 2 queries into 1If the UPDATE is on all previously inserted rows (and none else) it's like: INSERT INTO table1 SELECT A.col1, A.col2, 'T', T.ID FROM Tmptable A LEFT JOIN table1 B ON A.Col1 = B.col1 AND A.co...
86207 Jan 2011 @ 02:22 PSTDatabaseReplySPOOL SPACE ERROR"row_number() over (order by 1)" will send all rows to a single AMP, better order by another column with lots of distinct values. And remove the DISTINCT, it's useless, because the row_number wi...
86107 Jan 2011 @ 02:18 PSTDatabaseReplyDBS CRASHED OR SESSIONS RESETHow does your logon-string look like? Seems to be a name resolution problem. Try .logon 127.0.0.1/username Dieter
86031 Dec 2010 @ 11:21 PSTDatabaseReplyCurrent_date minus constant!!Why don't you simply write "-" instead of MINUS? Happy New Year :-) Dieter
85922 Dec 2010 @ 07:17 PSTGeneralReplyList Teradata expired ID'sHi Vince, the calculation of PasswordLastModDate changed in TD12. Have a look a the definition of dbc.users, it's much easier now :-) CAST(PasswordModTime AS DATE) + ExpirePassword AS Password...
85822 Dec 2010 @ 12:03 PSTGeneralReplyAcceptable Duplicate Row CountI would consider any duplicate row (outside of the staging area) to be non-acceptable. Not only because it contradicts the Relational Model (no PK), but also because it might screw your queries/...
85721 Dec 2010 @ 11:56 PSTDatabaseReplyshow view this is showing some what diffentDid you check what Fred wrote? There should be some info in dbc.eventsV about that COPY. Is the view actually working? Try a show QUALIFIED view ODS_OHR_REP_SOURCE_RECV.HR_PRT_PERSN_EDHR_D_V...
85621 Dec 2010 @ 11:45 PSTGeneralReplyProblem regarding the difference between two dates in terms of monthBlame Standard SQL for that calculation :-) One of the available Oracle UDFs is MONTHS_BETWEEN, which is what you probably need. Dieter
85521 Dec 2010 @ 11:43 PSTToolsReplyDate Formating in Multiload ScriptYou have to apply a FORMAT: .DML Label CorpAction_Ins; INSERT INTO TestDB.actDate ( Script, RECORD_DT ) VALUES(:Script, :RECORD_DT (date, format 'dd-mm-yyyy')); Dieter
85421 Dec 2010 @ 11:40 PSTDatabaseReplyDBA queryThere is no global right to create databases in Teradata. Create the neccessary SQL: SELECT 'grant database on ' || databasename || ' to userxxx;' FROM dbc.databasesV; and then run the ou...

Pages