#DateForumTypeThreadPost
272807 May 2014 @ 05:00 PDTDatabaseReplyTERADATAHi Abhilah, are those timestamp columns actually VarChars? That's bad. If they were TIMESTAMPs it would be a simple WHERE (START_DT, END_DT) OVERLAPS (TIMESTAMP '2014-04-24 12:13:...
272707 May 2014 @ 04:19 PDTDatabaseReplyCalculation SkewHi Peter, when you press F12 in TD Admin you can see the submitted query in the "SQL History". You'll notice that the SQL is using a similar calculation, but it's not based on th...
272607 May 2014 @ 02:41 PDTToolsReplyBTEQ Import REPORT Mode. Growing to bufferYou're importing in REPORT mode which expects fixed width CHARs, but your data is VARCHAR and your USING includes binary integers. Try .IMPORT VARTEXT ' ' FILE=/root/jugal/samples12...
272507 May 2014 @ 02:34 PDTDatabaseReplyanalog sys.foreign_keys.is_not_trustedHi Sergey, unfortunately there's no way to get the FK type from the data dictionary (afaik). This info is only stored in the table header, the data dictionary will just show "there's...
272407 May 2014 @ 12:55 PDTDatabaseReplySlowness in Teradata systemHi Mahendra, what's your TD release? Do you have Viewpoint? Then simply use this to find the session(s) consuming most resources or blocking others. If there's no Viewpoint you can do th...
272307 May 2014 @ 12:51 PDTTrainingReplyPrototypesHi Krishna, did you already check the Resource Library on the Teradata website? It has plenty of case studies, etc.: http://www.teradata.com/resources/
272207 May 2014 @ 12:42 PDTDatabaseReplyIdentifying Temporal TablesThe column Ulrich mentioned exists since TD13.10, so to get all Temporal tables: SELECT * FROM dbc.TablesV WHERE TemporalProperty <> 'N' Similar to find the Temporal columns: &nb...
272107 May 2014 @ 12:20 PDTDatabaseReplyTeradata 14.0 - regex_replace unexpected resultYou should open an incident with Teradata support, this is definitely a wrong result.
272007 May 2014 @ 12:19 PDTDatabaseReplyIs index creation allowed for a derived column to improve performance?You can't do indexes on calculated columns, but you might create a Join Index with that calculation. But unless you have lots of years of data in your table an index will be not selective enou...
271907 May 2014 @ 12:10 PDTDatabaseReplyMvc Compression and Collect Stats on TD 14MVC has no direct effect on collecting stats (other than the table might be smaller, thus less data to read).  But I would try to do mainly SAMPLE stats, in my experience it can be done on al...
271807 May 2014 @ 12:04 PDTGeneralReplyHow to install teradata Sql assistant SQL Asistant is part of the Windows TTU: TTU - Windows Install Btw, Studio Express 13 is quite old, you should update to 15: Teradata Studio Express or the full version Teradata ...
271706 May 2014 @ 11:08 PDTGeneralReplySQL helpYou might try a conditional join based on the result of the first join, e.g. SELECT id, COALESCE(a.mtr_id, b.mtr_id) FROM ( SELECT STGtable.ID, CASE WHEN MIN(a.mtr_id) ...
271601 May 2014 @ 03:55 PDTGeneralReplyTPT handling the CSV with date and timestamp columnAssuming this is from te APPLY section you need to use two single quotes, but you got three, which simply finishes the SQL string: :ROW_CREATED_DATE (timestamp(6),format ''yyyy-mm-ddBhh:...
271501 May 2014 @ 03:48 PDTDatabaseReplyABNORMAL BEHAVIOR OF QUERY IN TD14.10 after upgrade from TD14Hi Sravan, it's hard to read because the runtime explain table is skrewed up, but: the optimizer assumes that this condition WHERE businesspa0_.ACTV <> 'N' AND ...
271401 May 2014 @ 03:14 PDTDatabaseReplyTranspose and Concatenating ValuesHi Sagar, only two values? Then it's easy: SELECT ID, MIN(VALUE) || CASE WHEN COUNT(*) = 1 THEN '' ELSE ';' || MAX(value) END AS NEW_VALU...
271330 Apr 2014 @ 04:26 PDTAnalyticsReplyREGEXP_SPLIT_TO_TABLE doesn't work Hi Bartosz, there was a change in TD14.10 to add an inputkey and two additional columns in output, of course this was not documented in the manuals :-) But the TD15 manual are corrected: Expre...
271228 Apr 2014 @ 11:16 PDTDatabaseReplyCumulative Sum by distinct customer ID by year@Carlos: you're right, this specific query was not "easily rewritten", but it's also not an easy question :-)   @Yusuf: The Deried Table must be in the FROM clause: se...
271128 Apr 2014 @ 03:47 PDTDatabaseReplyDBQL SQLTextInfo 3577: row size or sort key size overflowYou can select up to 2048 columns with a combined size of a bit less than 64KB. This means defined maximum not actual size. If your session uses UTF8 you need to multiple the defined size ti...
271028 Apr 2014 @ 03:44 PDTTeradata ApplicationsReplyTable properties and utilities to load dataThe load utilities are used to load data which doesn't exists yet within the system If the data is already in a TD table there's no need to use MLoad/FastLoad, as a simple SQL Insert/Selec...
270925 Apr 2014 @ 08:46 PDTDatabaseReplyCumulative Sum by distinct customer ID by yearHi Carlos, of course this should work, too. But the Explain wil be horrible due to the non-equi-Join, including a bad Product Join with a huge intermediate spool. I always try to avoid Scalar Sub...
270823 Apr 2014 @ 02:05 PDTDatabaseReplyNeed alternative to DBQL processingHi Joe, I never tested this, but when you're able to locate the FExp sessions in DBQL the SpoolUsage of the final step in QryLogStepsV might be what you're looking for.
270723 Apr 2014 @ 01:58 PDTDatabaseReplyFinding the latest dateThen it's a perfect match for those OLAP functions :-)
270623 Apr 2014 @ 01:57 PDTDatabaseReplyIdentify Set - Multiset Tables in TD 14CREATE TABLE AS existing_table copies everything (including SET/MULTISET, [NOT] NULL, FORMAT, indexes and checks) but Foreign Keys and Triggers. But when you do CREATE TABLE AS SELECT ev...
270523 Apr 2014 @ 01:51 PDTDatabaseReplyRemove the first 4 characters in a stringThere's no RIGHT function in Standard/Teradata SQL, but with SUBSTR[ING] it's easy: SUBSTRING(state FROM 5) -- Standard SQL or SUBSTR(state, 5) -- non-Standard SQL
270423 Apr 2014 @ 12:05 PDTDatabaseReplyFind patterns in a string (PATINDEX in Sql server equivalent)What's your Teradata release? TD14 implements Regular Expressions: REGEXP_SUBSTR(column, '[a-z][0-9]{4}',1,1,'i')  

Pages