#DateForumTypeThreadPost
320315 Oct 2014 @ 10:21 PDTToolsReplyView Joining two fields of a tableyearmonth is usually calculated using yearcol * 100 + monthcol as yearmonth But why can't you simply use two conditions? WHERE yearcol = 2014 and monthcol = 10 will allow the...
320215 Oct 2014 @ 10:05 PDTDatabaseReplyTranspose data from wide table to long tableUlrich's code avoids the UNION and is the most efficient solution before TD14.10 which added a TD_UNPIVOT table function: SELECT customerid, id FROM TD_UNPIVOT ( ON (SELECT * FROM vT...
320114 Oct 2014 @ 11:24 PDTDatabaseReplyRemove Chars and get only numeric valuesThere's an old trick utilizing nested oTranslate: remove all characters you want to keep and then use this as characters to be removed: oTranslate(x, oTranslate(x, '0123456789','&#...
320014 Oct 2014 @ 11:20 PDTDatabaseReplySeparation of columns into rowsBefore TD14 it's much more complicated, see a solution I posted at: https://forums.teradata.com/forum/database/stored-procedures-need-help-with-massaging-data-within-an-input-parameter
319911 Oct 2014 @ 03:36 PDTDatabaseReplySeparation of columns into rowsWhat's your Teradata release? In TD14 there's a STRTOK_SPLIT_TO_TABLE which does exactly what you need: SELECT * FROM TABLE (STRTOK_SPLIT_TO_TABLE(your_table.id,your_table.demog,';...
319811 Oct 2014 @ 02:47 PDTDatabaseReplyAnalytic functionI assume you ment range between interval '1' month preceding and '1' month preceding instead of rows. The RANGE keyword is not implemented in Teradata (probably due to possible perf...
319710 Oct 2014 @ 02:45 PDTDatabaseReplyPerformance of Correlated Subquery!!!Hi Anish, the TD optimizer can't do anything else but rewrite a CS to a join (in worst case a cross/product join), a Hash Join would be the closest to a lookup processing. Of course there mig...
319610 Oct 2014 @ 12:50 PDTDatabaseReplyPerformance of Correlated Subquery!!!Hi Anish, some remarks: There's no special technique called "shared spool" for correlated subqueries (CS), the query is simply rewritten as join. Joe D'Silva didn't claim t...
319509 Oct 2014 @ 11:05 PDTDatabaseReplyRow level Duplicate Record eliminationHi Ambuj, SET tables should perform best unless the PI is very non-unique.
319409 Oct 2014 @ 12:49 PDTAnalyticsReplyBteq script error handling not workingAnd your problem is?   Guessing: GOTO can only skip forward, but not back. Why are there two bteq <<EOF?
319309 Oct 2014 @ 08:55 PDTDatabaseReplyWhere does the CHECKSUM metadata value for a table get stored in DBC Teradata catalog tablesCHECKSUM can't be found in any system table, it's only in the table header (similar to BLOCKSIZE). The only way to get this info using SQL is a SHOW TABLE, otherwise only a low-level comma...
319209 Oct 2014 @ 05:23 PDTDatabaseReplyHow to avoid using QUALIFY OVER PARTITION BYHi Sarang, depending on your actual data you might do the ROW_NUMBER in a Derived Table instead: LEFT JOIN (SELECT * FROM TableF QUALIFY ROW_NUMBER() OVER (PARTITION BY key_field OR...
319108 Oct 2014 @ 09:24 PDTDatabaseReplyNeed help in Optimizing Row_Number/RANK() operationsCan you show your current process? And what's your TD release?
319008 Oct 2014 @ 07:12 PDTDatabaseReplyInsert ASCII 222 valueThis must be related to something else: BTEQ -- Enter your SQL request or BTEQ command: CREATE MULTISET VOLATILE TABLE vt (x VARCHAR(10)) ON COMMIT PRESERVE ROWS; *** Table has been cre...
318908 Oct 2014 @ 06:08 PDTDatabaseReplyFailure 2620 The format or data contains a bad characterWhat's the datatype of TYP_CHK_CD?
318808 Oct 2014 @ 06:06 PDTDatabaseReplyInsert ASCII 222 valueOnly hex '1A' will result in an error, but 222 should work What did you try? SELECT CHR(222), 'DE'xc
318708 Oct 2014 @ 02:13 PDTDatabaseReplyAn owner referenced by user does not have REFERENCES accessPlease read the topic on SQL SECURITY in the manuals, you might want to switch to CREATOR: http://www.info.teradata.com/HTMLPubs/DB_TTU_14_10/index.html#page/SQL_Reference/B035_1144_112A/Create_Pr...
318608 Oct 2014 @ 01:52 PDTDatabaseReplyError Handling in Stored ProcedureUpdate/Insert as part of the handler? BEGIN Update/Insert END   Outside of the handler? Nested handlers   There are lots of examples in the manuals: http://www.info.teradata.com/HT...
318508 Oct 2014 @ 01:44 PDTDatabaseReplyTeradata SQL Assistant 15.00 DROP STATISTICS not workingHi Peter, as a TD customer you don't need to download the TTU from DevEx, ask your DBA to get it from T@YS, there's always the latest version.
318407 Oct 2014 @ 11:34 PDTGeneralReplyAMP FATALDid you contact Teradata support, yet? Why are those AMPs fatal? If it's due to disk/RAID failures the process is called "restore from backup" :-)
318307 Oct 2014 @ 04:21 PDTGeneralReplyHIGH - SesAmpCpuDepending on the actual queries 15,000 CPU seconds for a few hundred queries might be low. I've seen single Selects using a way more than 100,000 seconds.
318207 Oct 2014 @ 03:38 PDTDatabaseReplyTeradata SQL Assistant 15.00 DROP STATISTICS not workingThis was fixed in SQLA 15.0.0.3 :-)
318106 Oct 2014 @ 11:46 PDTToolsReplyUsing the Append statement to write the results from 2 queries to one txt file.This should work, what's the error message?
318006 Oct 2014 @ 10:22 PDTDatabaseReplyCreating Header,Detail and Trailer records from one SQL statement What tool do you use for exporting? - both BTEQ and FastExport append the data of multiple selects by default - SQL Assistant got an option "write all answer set to a single file" &nbs...
317906 Oct 2014 @ 09:57 PDTGeneralReplyrecursive query spool space errorThis is not due to recursion :-) Teradata was implemented before there was Standard SQL, the initial query language was called TEQUEL (TEradata QUEry Language), whose syntax didn't require to ...

Pages