#DateForumTypeThreadPost
370315 May 2015 @ 10:57 PDTDatabaseReplyStored Procedure performance improvementSounds like your're using some cursor-based processing. Of course this is slow, in every DBMS, but especially bad in a parallel system. E.g. you don't need an SP to find the next business ...
370215 May 2015 @ 04:46 PDTGeneralReplydbc.indices ; dbc.indexconstraints ;Hi Srini, there are two possible reasons: You got multiple rows per table due to multi-column indexes, add "and columnposition = 1" to return only one row per table There a...
370115 May 2015 @ 04:38 PDTGeneralReplyRetain amounts where NULL and divide by number of NULLSThis should return the expected result (if Code and Amt are both NULL): SELECT Acc, dt, --get the Code for each group MAX(Code) OVER (PARTITION BY Acc, grp), --get the Amt ...
370014 May 2015 @ 06:29 PDTGeneralReplyRemoving Logical DuplicatesWhat if there's a no flight back? This will return the correct result: SELECT LOC_1, LOC_2, distance FROM tab QUALIFY ROW_NUMBER() OVER (PARTITION BY CASE WH...
369914 May 2015 @ 02:23 PDTDatabaseReplyDBC.ColumnsV ColumnFormatFORMATs are manly based on COBOL syntax, e.g. '9' means a leading zero, '(n)' repeats the previous character n times. See "Data Type Formats and Format Phrases"...
369814 May 2015 @ 02:19 PDTDatabaseReplyComparing 2 different datatypes giving undesired resultsHi Cheeli, a NULL in SQL is not a binary zero. What you see seems to be a common problem when data is loaded from DB2, it's the so-called "low value". DB2 tends to add those in...
369714 May 2015 @ 02:11 PDTDatabaseReplycomparison of 2 columns of different datatypes giving undesired resultsHi Cheeli, why do you post the same question using a new account?
369613 May 2015 @ 02:30 PDTDatabaseReplyPartitionWhat's your Teradata release? In TD14.10 this could be done with an INS/SEL into a normalized table or using  SELECT ACCNO, ACCNAME, SAL, BEGIN(pd) FROM ( SELECT NORMALIZE ACCNO...
369513 May 2015 @ 02:13 PDTDatabaseReplyDerived table error / object contains restricted charactersDon't know how you typed it (did you write the code in MS Word?), but in line 85 there's an illegal character: ELSE b.FA_AMT – daq.psamt END AS DIFF The '-' is not U+...
369413 May 2015 @ 08:03 PDTDatabaseReplyERROR IN CASTING DECIMAL TO INTEGERHi Yuvana, of course there's no way to cast a 14 digit number to an integer (and stripping of some digits by casting to a varchar(10) will result in garbage). You need to change the target ta...
369312 May 2015 @ 10:53 PDTDatabaseReplyERROR IN CASTING DECIMAL TO INTEGERWell, a decimal has a range of +/+ 2**31, of course a 14-digit decimal doesn't fit.
369212 May 2015 @ 09:36 PDTDatabaseReplyswapping values in a column oracle SQL plusYou can do it with a single query using CASE: update emp set job= case job when 'SALESMAN' then 'CLERK' when 'CLERK' then 'SALESMAN' END ...
369109 May 2015 @ 07:32 PDTDatabaseReplyReplacing symbols from input columnHi Moutusi, what's your Teradata release? oTranslate(col, ' *-.', '') removes those characters.
369009 May 2015 @ 07:31 PDTToolsReplyDUPLICATE RECORDS USAGETeradata implements MULTISET table because Standard SQL defines them and all other DBMSes do the same :-) Of course nobody wants duplicate rows in a production database, but they might be useful i...
368909 May 2015 @ 07:27 PDTToolsReplyBTEQ IMPORT SKIP Command There's a blank within the filepath, you must quote it: .Import Vartext',' file="C:\Users\Sridhar\Desktop\teradata\BTEQ Scripts Outputs\sridhar4.txt", SKIP=10;
368808 May 2015 @ 11:40 PDTToolsReplyTTU for Mac OS There's also Wallet, ODBC & TPT for OS X :-)
368706 May 2015 @ 03:55 PDTDatabaseReplyHelp with query- I receive error 3504Hi Ilana, you need to add all columns without aggregation function to GROUP BY: SELECT TRUNC (CAST (created_at AS DATE), 'IW') AS first_day_week ,USER_ACCESS_mode ...
368606 May 2015 @ 03:52 PDTGeneralReplySuper Basic: How do you use 'Date' as a qualifier in a query?There's only one recommended way to write a date using Standard SQL syntax: where dateofhire >= DATE '2010-12-31'  
368503 May 2015 @ 01:52 PDTToolsReplyFastLoad Error -incorrect number of bytes returnedCheck if there's a final linebreak after the last line
368403 May 2015 @ 04:27 PDTToolsReplyFastLoad Error -incorrect number of bytes returnedYou define your input as fixed width, but the actual data is variable width. Use set record VARTEXT;, change all fields to VARCHAR, remove all delims and the final newlinechar. And because y...
368301 May 2015 @ 05:12 PDTDatabaseReplySelecting with WHERE clause leads to all-row scan .Without the actual SELECTs it's hard to tell if both queries return the same result.  Compare EXPLAINs and chekc if the condition is pushed into the Derived Table in Q2. Otherwise you mi...
368201 May 2015 @ 03:49 PDTDatabaseReplyParellal insert statement against same global temporary tableNo, because every session uses it's own version of that table. Do you finally Insert/Select into a permanent target table?
368130 Apr 2015 @ 02:36 PDTDatabaseReplyWindows Aggregate FunctionHi Abhinav, this should return the expected result: SELECT ..... CASE WHEN ExistingRole = 'R_PROD_SALES_ANLYTCS' THEN ExistingRole WHEN ExistingRole IN ('R_PROD_WIL&...
368030 Apr 2015 @ 01:53 PDTExtensibilityReplyIssue with Stored ProcedureIf you want to abort unconditionally you don't need dynamic SQL. ABORTSESSIONS returns a single row with the count of aborted sessions, so simply return this into a variable: SELECT sys...
367930 Apr 2015 @ 01:46 PDTDatabaseReplySQL Server Query to Teradata : XML PATHIf your TD system supports XML (native or as addon) there's an XML aggregate function. And you better replace the Scalar Subquery with a join: CREATE VOLATILE TABLE #tmpFinal AS ( SELECT...

Pages