#DateForumTypeThreadPost
302815 Aug 2014 @ 06:14 PDTDatabaseReplyswap values in two columns using update SQLHi Moutusi, it's that easy :-) Unless you try it on MySQL, which will screw up the data to A B 2 2 4 4 6 6
302715 Aug 2014 @ 06:12 PDTDatabaseReplyPartition effectivenessHi Robin, you defined 416.741 partitions and even if there a PARTITION stats the optimizer must still include all possible partitions in the plan. You should limit the number of partitions, ...
302615 Aug 2014 @ 05:57 PDTDatabaseReplyDetermine new table skew for a different PIHi Steven, how do you define the skew factor? I use this for calculating the percent deviation from average: SELECT HASHAMP(HASHBUCKET(HASHROW(col))) AS vproc, COUNT(*) AS cnt, 100...
302515 Aug 2014 @ 05:36 PDTDatabaseReplyDetermine status on a given dateInstead of a join you might use EXPAND on to create the missing dates, see http://forums.teradata.com/forum/general/creating-missing-observations
302415 Aug 2014 @ 04:54 PDTDatabaseReplyVarchar to Timestamp(6) conversionHi Orlando, what's your TD release? In TD14 you might utilize Oracle's TO_TIMESTAMP, which is a bit more flexilbe regarding single digit day/hour/minute/second, but month still needs to b...
302314 Aug 2014 @ 03:06 PDTDatabaseReplycast and concatenateHi Salokh, remove the dashes from the hex string: sel * from dbc.databasespace where tableid= '00001C000000'xb  
302214 Aug 2014 @ 02:19 PDTDatabaseReplyHow to limit rows to only those where there are duplicates of a specific column?Hi Chandan, based on your result set (I didn't fully understand your explanantion) this should work: CREATE VOLATILE TABLE vt ( ACCT INT, ID INT, code CHAR(3)) ON COMMIT PRESERVE...
302111 Aug 2014 @ 01:37 PDTGeneralReplyQuery performance issueYou said 'I did not change anything', well, I would call changing the PI a big thing :( So you don't know how to choose a good Primary Index, you don't know what Soft RI is, you do...
302011 Aug 2014 @ 11:02 PDTToolsReplyFast load ErrorAnd there's another error, remove the period before DEFINE: • Commands may begin with a period, but do not have to begin with a period. • If there is no leading period, then t...
301911 Aug 2014 @ 08:58 PDTGeneralReplyCase statementHi Pragov,  you should know the functionality of basic functions like SUBSTRING, 5 indicates the number of characters returned, i.e. ';BYE;'. If you want to compare to ';OTHER;...
301811 Aug 2014 @ 08:51 PDTGeneralReplyQuery performance issueSoft RI (WITH NO CHECK OPTION) does not validate any referential integrity. WITH CHECK OPTION validates on a statement level doing a kind of [NOT] EXISTS correlated subquery, but you can spot...
301711 Aug 2014 @ 08:39 PDTDatabaseReplyRepeatability of random samplingThere's no random seed, if there's only a single row inserted/deleted/updated it would be impossible to repeat that sample. The only way is materializing the result of the sample select in...
301610 Aug 2014 @ 12:12 PDTGeneralReplyCase statementHi Pragov, it would be helpful to know what you mean by "It is not working" There's probably an error message as INSTR returns the position where it found the 19th ';' and y...
301510 Aug 2014 @ 12:07 PDTGeneralReplyCreating Missing ObservationsIt's hard to tell without knowing the data. You might not order by the correct column, based on your example eff_dt was the same for all rows, but period_date was unique. Or there are multipl...
301410 Aug 2014 @ 07:04 PDTGeneralReplyQuery performance issueOk, there's no step indicating an RI check, this is only visible when using WITH CHECK OPTION.   You implemented Soft RI (WITH NO CHECK OPTION) and since that time it's slower? Did ...
301310 Aug 2014 @ 06:55 PDTGeneralReplyCreating Missing ObservationsLooks like a perfect case for EXPAND ON, the missing rows get the last existing row's data. The needed PERIOD can be created on-the-fly: SELECT Key Eff_Date, Start, End, ...
301209 Aug 2014 @ 07:37 PDTDatabaseReplyStored procedure conversion from Oracle to TeradataThere's no new session assigned, it's within the calling user's session.
301108 Aug 2014 @ 12:33 PDTDatabaseReplycoalesce functionAdd a WHERE AP.RECORD_DATE IS NULL to check if there are any NULLs.
301008 Aug 2014 @ 10:58 PDTDatabaseReplycoalesce functionWhat do you mean by "i don't see them", you must be more specific. The COALESCE will return the DATE casted to a string or 'Not yet recorded'.
300908 Aug 2014 @ 10:54 PDTDatabaseReplycast and concatenateSo you need some TRIMs: CAST(TRIM(cust_id) || SUBSTRING('0' || TRIM(cust_nbr) FROM CHAR_LENGTH(TRIM(cust_nbr)) - 2 + 1) || TRIM(cust_dig) AS BIGINT)  
300808 Aug 2014 @ 10:48 PDTGeneralReplyQuery performance issueIf there's Soft RI there shouldn'tt be any kind of check. Can you show the Explain?
300708 Aug 2014 @ 10:41 PDTDatabaseReplyDelete from table allMantra: There's no difference between DELETE and DELETE ALL, ommmmm. A full table SQL DELETE [ALL] will be faster than a MLoad DELETE, because MLoad actually scans the table. If there's a...
300608 Aug 2014 @ 10:34 PDTDatabaseReplyStored procedure conversion from Oracle to TeradataA Stored Procedure in Teradata is rewritten as a C program and then compiled into a shared object on Linux. When you CALL the SP it's actually calling an exported function of that shared object...
300508 Aug 2014 @ 10:20 PDTDatabaseReplycast and concatenateCAST and || are both Standard SQL, so exactly the same in Teradata. There's no RIGHT function in Teradata, but it's easy to rewrite: RIGHT(col, n) = SUBSTRING(col FROM CHAR_LENGTH(co...
300408 Aug 2014 @ 03:09 PDTGeneralReplyQuery performance issueAs you access the same table twice you might also try a Windowed Aggregate Function instaed of a join: SELECT count(distinct(TBL_OUTPUT.C_CID)) as CNT FROM ( SELECT ...

Pages