#DateForumTypeThreadPost
310306 Sep 2014 @ 04:21 PDTDatabaseReplyStored procedure Missing/Invalid SQL statement'E(3707) errorCursors are evil in a parallel database system and nested cursor are even worse. Why don't you create you delimited list using MAX(CASE) or recursion, there have been multiple threads on that ...
310206 Sep 2014 @ 03:45 PDTDatabaseReplyOptimization of a Join QueryHi Sarang, seems like you only want a flag to detemine if data from the previous week exists. You could extract rows for both dates in a single query and then use an OLAP function to check if the...
310106 Sep 2014 @ 03:11 PDTTeradata StudioReplyChanging TMODE from ANSI to TERA after connection createdThe "normal" mode is the one used at your company :-) Changing the transaction mode should not result in any parsing error, only the transaction semantics and some defaults will change. ...
310006 Sep 2014 @ 03:06 PDTDatabaseReplyComplex SQL query Hi Shaum, your logic is based on ordering by ref_id and seq_nbr? sum(case when depth = 1 then 1 else 0 end) over (order by ref_id, seq_nbr rows unbounded preceding)  
309905 Sep 2014 @ 02:25 PDTDatabaseReplyStored procedure Missing/Invalid SQL statement'E(3707) errorString concatenation is done with two not one pipe character: SET sql_stmt_cont = 'UPDATE cntNextTable SET PROD_ENTRADA = ' || lista_entrada ||... But why do you use a cursor to do multip...
309805 Sep 2014 @ 07:30 PDTDatabaseReplyImporting Data from .XLSNo release of Teradata supports importing Excel files. I doubt there's any DBMS which can load .xls directly, as it's Microsoft's proprietary format and changing with every release. S...
309705 Sep 2014 @ 04:33 PDTDatabaseReplyHelp with macroThere's no way to do it in a macro. In every DBMS you will need some Dynamic SQL to do so and a DBA will never allow that due to possible SQL Injection, e.g. EXEC T('''2305'...
309605 Sep 2014 @ 04:29 PDTDatabaseReplyQueue TablesYou're probably correct. There's no WHERE-clause for SELECT AND CONSUME. When you SELECT AND CONSUME a row it's deleted from the queue. You might re-insert it but it will be consumed ...
309505 Sep 2014 @ 04:02 PDTDatabaseReplyre:spool spaceHi Shiva, it's hard to tell without knowing the logical PK/FK and Explain. Assuming ctr.cust_is is the PK you could rewrite it using EXISTS, imho this should be logically equivalent: SELEC...
309405 Sep 2014 @ 03:56 PDTTeradata ApplicationsReplyFASTEXPORT - Sorted order two further distribution between amps. But Why?Those steps are needed to provide the ability to export a sorted result set using multiple sessions. The query finished and each AMP has it's result sorted in a local spool. This spool is the...
309305 Sep 2014 @ 03:41 PDTDatabaseReplyDate in varchar and DD-MMM-YY FORMATDepending on a global system setting (Century Break in dbscontrol) or your Teradata release this might be easy. If you're on TD14 you can utilize Oracle's TO_DATE: WHERE TO_DATE(TRANSMI...
309204 Sep 2014 @ 11:33 PDTGeneralReplyExtract email and date from fieldHi Sabrina, if the data actually looks like this, you can do some simple POSITION/SUBTRING/TRIM  TRIM(LEADING '"' FROM SUBSTRING(x FROM 1 FOR POSITION(' ' IN x))) AS ...
309104 Sep 2014 @ 01:48 PDTGeneralReplyFailed 6706: The string contains an untranslatable character errorHi Praveen, seems like col2 is in Character Set Unicode and there's a cast to Latin. What's your Teradata release? INSTR is built-in in TD14, earlier you might have an own UDF implementat...
309004 Sep 2014 @ 12:59 PDTGeneralReplyUSING DDMMYYYY INSIDE DBC.SYSEXECSQL() in Stored ProcedureSimply change the code to get the expected result: SELECT 'teradata' AS str, 'Select * FROM DB.Table_Name where brand LIKE ''%'||str||'%'' ;'  
308904 Sep 2014 @ 12:57 PDTDatabaseReplyFull OuterJoin IssueHi Raja, it's hard to tell, there's no obvious issue. Can you compare both Explains (SEL vs. INS/SEL) if there's any difference? Or do a SEL 1 EXCEPT ALL SEL 2 to see the actual diff...
308803 Sep 2014 @ 12:54 PDTDatabaseReplyFull OuterJoin IssueYou wrote FULL join but in fact both are LEFT/RIGHT joins, simply check explain. A WHERE-condition on the inner table removes the outer join, resulting in: Select * from A LEFT join B...
308703 Sep 2014 @ 12:48 PDTGeneralReplyUSING DDMMYYYY INSIDE DBC.SYSEXECSQL() in Stored ProcedureHi Arun, there are too many quotes: SELECT 'teradata' AS str, 'Select * FROM DB.Table_Name where brand LIKE ''''%'||str||'%'''' ; ) AS EVNT_...
308603 Sep 2014 @ 12:38 PDTAnalyticsReplyusing "CONCAT" function in BTEQ causes 3706 Syntax error but works through SQL AssistantCONCAT is an ODBC SQL function which is automatially converted to correct Teradata syntax by the ODBC driver (when a specific option is set). In the ODBC manual there's a list of those functio...
308503 Sep 2014 @ 12:21 PDTDatabaseReplyre:spool spaceIf you can locate old vs. new Explains in DBQL you will see what SOMETHING changed :-)
308403 Sep 2014 @ 12:18 PDTDatabaseReplyCreating table with defined PK but not defining PIHi Moutusi, A few years ago the result of a SHOW TABLE was changed to return the logical constraints instead of the internally created indexes. So you still get a UPI on (col1,col2), but you have...
308303 Sep 2014 @ 12:14 PDTGeneralReplyDoubt with Rank Over Partition By FunctionWhat have you tried? Is there a known maximum limit of rows per ID? Do you need the concatenated result based on a specific order? What's your Teradata release?
308202 Sep 2014 @ 03:29 PDTToolsReplyCustom Message in BteqNOTIFY: - is not writing to standard output, but to EventLog on Windows or system log on Unix. - must be specified before the SQL commands - has a different syntax .notify HIGH msg 'Bteq ...
308102 Sep 2014 @ 01:08 PDTGeneralReplySTRIPING WHITE SPACESHex '00' is not a white space, TRIM by default removes hex '20'. TRIM(TRAILING '00'xc FROM col)  
308002 Sep 2014 @ 12:12 PDTGeneralReplyconvert 13 digit epoch time to date?If 1271664970687 includes milliseconds, you need to change the calculation, otherwise you try to add more than 40,000 years :-) SELECT 1271664970687 AS x, CAST(DATE '1970-01-01' + ...
307901 Sep 2014 @ 09:41 PDTDatabaseReplywhat is the significance of FORMAT in table DDL, what if we dont give any format ?If you don't specify a FORMAT there's always a default. col1: If BIGINT is changed to INT you will get 'numeric overflow' messages when you got values outside of the INT range (reg...

Pages