#DateForumTypeThreadPost
385322 Jul 2015 @ 06:56 PDTDatabaseReplyRPAD and LPAD gives: SELECT Failed [9804] Response Row size or Constant Row size Hi Peter, LPAD returns a VARCHAR(32000) CHARACTER SET UNICODE, you need to cast it to an matching size: SELECT LPAD(CAST(h AS VARCHAR(2)),2,'0') AS h, LPAD(CAST(m AS VARCHAR(2)...
385218 Jul 2015 @ 03:26 PDTDatabaseReplyconcatenate value of multiple rows into one Single rowDo you need to order the values for concatenation? 'a&b&c' vs. 'b&c&a'  How many rows and rows per value exists? What's the actual datatypes? What's ...
385117 Jul 2015 @ 03:56 PDTDatabaseReplyNeed Help in QueryHi Vishal, simply add qualify count(*) over (partition by DateOfBirth, FirstName, LastName) > 1  
385017 Jul 2015 @ 03:24 PDTGeneralReplyTranspose the column with removing the duplicate stringSplitting and removing duplicates is easy with in TD14+: SELECT DISTINCT Store_line_no, token FROM TABLE (STRTOK_SPLIT_TO_TABLE(tab.Store_line_no,tab.str1, '|') RETURNS (Store_line_...
384916 Jul 2015 @ 11:30 PDTToolsReplyWhy TPT mload is taking long time than bteq for huge volume of dataWhy do you want to use TPT UPDATE if you know it's faster with SQL DELETE/INSERT? There's only one advantage of TPT UPDATE: It needs only a single scan of the table vs. two scans for two ...
384816 Jul 2015 @ 01:57 PDTDatabaseReplyALTER PPI - performance impactIn fact you can add partitions where data exists. ADD RANGE will result in a scan of the NO RANGE partition, moving matching rows into the new partitions.  But it's not really efficient (...
384716 Jul 2015 @ 01:51 PDTToolsReplyDoes TTU13.10 work with TD database v12 ?The base TTUs support 4 TD releases back (and 2 forward) , so TD12 should be supported up to TTU 14.10
384616 Jul 2015 @ 01:36 PDTGeneralReplyChanging display/format of Teradata SQL Assistant ResultLPAD/RPAD only work on strings, so non-character data must be CASTed first. And your CASTs try to convert the result of the xPAD back to the original datatype?   But do you actually need xP...
384516 Jul 2015 @ 10:48 PDTGeneralReplyChanging display/format of Teradata SQL Assistant ResultLPAD returns 32000 or 64000 chars, you need to cast to a smaller size: CAST(LPAD(COL1, 4) AS CHAR(4)) || CAST(LPAD (COL2, 5) ASCHAR(5)) AS COL3 Btw, no need for ' ', this is the default...
384416 Jul 2015 @ 09:42 PDTDatabaseReplyTeradata backup of a databaseCreate a file like this: LOGON .... ARCHIVE DATA TABLES (mydb) ALL, RELEASE LOCK, FILE=myfile; LOGOFF; And then run that using arcmain < this_file from the linux terminal (the ...
384316 Jul 2015 @ 09:22 PDTGeneralReplyChanging display/format of Teradata SQL Assistant ResultWhithout the actual statement it's hard to say why it fails, you said you're n TD14.10, so LPAD exists.
384216 Jul 2015 @ 08:47 PDTTeradata StudioReplyv15.10.3 - commit processingI second Dave, Studio (or JDBC) is definitely not working as expected. After switching on DBQL one can easily prove what's actually sent to TD, both "autocommit" and "no autocom...
384116 Jul 2015 @ 05:08 PDTDatabaseReplyEaster monday calculationThere's no simple calculation for easter monday :-) I did that some years ago: /*** Calculation of easter sunday, implemented using an algorithm from http://www.merlyn.demon.co.uk/estra...
384015 Jul 2015 @ 01:45 PDTDatabaseReplyHow to center align a decimal value(including negative numbers) with 20 characters as total fixed lengthH Krishna, no need for oTranslate, but your format defines leading blanks, simply change it: CAST(( x (FORMAT '-(16)9.99')) AS VARCHAR(20)) or TRIM( x (FORMAT '-(16)9.99')) ...
383915 Jul 2015 @ 10:52 PDTDatabaseReplyPivot based on dateYour last query was as good as possible, there's no way to get the column names dynamically (you must use Dynamic SQL in a Stored Procedure).  Assuming that Bill_Month is a DATE or T...
383815 Jul 2015 @ 10:31 PDTGeneralReplyTeraDataCan you show your current query? If you want a single result row you can use different CASEs like this: COUNT(DISTINCT CASE WHEN date_col = CURRENT_DATE THEN subscriber_no END) COUNT(DISTINC...
383715 Jul 2015 @ 06:43 PDTDatabaseReplyInsert into target table from source table for each date for a specific date rangeI calculated the number of days wrong, but it's totally independant of that number. If you run a single statement it also hits each partition only once. And it's definitely faster to run ...
383615 Jul 2015 @ 06:13 PDTDatabaseReplyInsert into target table from source table for each date for a specific date rangeI best case target is partitioned the same as source, otherwise there will be 821 full table scans of the target caused by the loop vs. 1 for a single statement.
383515 Jul 2015 @ 04:16 PDTDatabaseReplyInsert into target table from source table for each date for a specific date rangeThis translates to a basic insert into target_tbl select col1, col2,col3 from source_tbl where ins_date BETWEEN DATE '2013-03-01' and DATE '2015-15-31' Otherwise you need...
383415 Jul 2015 @ 01:55 PDTDatabaseReplyTimestamp TimeZone Cleaner CodingI don't understand what this code is doing, in fact it should return some syntax errors :) Can you show some actual data and the result?
383315 Jul 2015 @ 01:25 PDTGeneralReplyA NEW DBA DOUBT - I AM ABLE TO SEE USERS FROM ADMIN CONSOLE BUT NOT FROM DBC.USERSI don't fully understand your problem, but: dbc.Users doesn't return all users, only those where the submitting user has DROP rights or is an owner (it's an X-view without X). Btw, bet...
383215 Jul 2015 @ 01:04 PDTDatabaseReplyHow to center align a decimal value(including negative numbers) with 20 characters as total fixed length  There's no built-in function for this, a common way is nesting LPAD/RPAD: RPAD(LPAD(TRIM(col),(20 - CHAR_LENGTH(TRIM(col))) / 2 + CHAR_LENGTH(TRIM(col))), 20) But RPAD returns a Var...
383113 Jul 2015 @ 09:11 PDTToolsReplyReceiving Failure 2673 The source parcel length does not match data that was defined while importing using BTEQHi Malli, again:  please post a new question in a new thread.
383013 Jul 2015 @ 09:08 PDTDatabaseReplyBest way to add a partition to an existing tableHi Kaushik, #2 is always preferred, unless you actually can't use ALTER TABLE (because you're not adding a range to a RANGE_N). If there's no NO RANGE partition it's a fast modifi...
382909 Jul 2015 @ 07:07 PDTDatabaseReplyTO GET LASTEST VALUES FOR NULL IN QUALIFYSHow do you define latest for NULLs? Your query returns the row with the latest date per PID, if all DATE_MODIFIED are NULL it returns a random row.

Pages