#DateForumTypeThreadPost
332802 Dec 2014 @ 10:21 PSTGeneralReplyAdd title to view columnYou can add a title, but it's not shown in dbc.ColumnsV or HELP TABLE: REPLACE VIEW testview AS SELECT InfoKey (TITLE 'bla') FROM dbc.dbcInfoV WHERE InfoKey = 'VERSION'; ...
332701 Dec 2014 @ 02:30 PSTDatabaseReplyHelp with avoiding redistribution using a hash indexYou can get a similar result to the TD_NORMALIZE_MEET using only 2 instead of 3 STAT steps. Is the min(ins_audit_id) the value from the starting row? A Join Index might be better than a Hash ...
332601 Dec 2014 @ 02:13 PSTDatabaseReplyIdentify error record in case of VT insert statement failure Hi Ashish, what's your TD release? TD14 supports regular expressions, following will return the bad rows: WHERE REGEXP_SIMILAR(col, '^[0-9]+$') = 0  
332501 Dec 2014 @ 02:10 PSTDatabaseReplyCheck for special character in a stringTD 14 supports regular expressions: REGEXP_SUBSTR(col, '([0-9]{1,3}\.){3}[0-9]{1,3}')  
332401 Dec 2014 @ 01:39 PSTDatabaseReplyselecting one record out of many records depending on few rules SELECT ACT, CASE WHEN MIN(CASE WHEN ACTIND = '01' THEN 1 END) + MIN(CASE WHEN ACTIND = '02' THEN 1 END) + MIN(CASE WHEN ACTIND = '07' THEN 1 END...
332330 Nov 2014 @ 11:45 PSTDatabaseReplyselecting one record out of many records depending on few rulesIs it possible to define the rules using an ORDER BY?  QUALIFY ROW_NUMBER() OVER (ORDER BY CASE ACTIND WHEN 3 THEN -2 WHEN 2 THEN -1 E...
332230 Nov 2014 @ 11:14 PSTDatabaseReplyStored Procedure for storing the DDL of Table in a variableYep, this approach will fail for tables with identity. Have a look at Glenn McCall's article Running Unsupported Queries from a Stored Procedure
332130 Nov 2014 @ 08:11 PSTDatabaseReplyStored Procedure for storing the DDL of Table in a variableInstead of trying to get the full DDL you might simply do:   CREATE TABLE tab_new AS tab_old WITH NO DATA; DROP TABLE tab_old; RENAME TABLE tab_new AS tab_old;  
332030 Nov 2014 @ 08:00 PSTDatabaseReplyQUERY OPTIMIZATIONYour query returns the customers who did not login in the year 2014. "Logged in for the first time this year" is something different for me: ​SELECT * FROM CUSTOMERS A WHERE CUS...
331926 Nov 2014 @ 04:21 PSTTeradata StudioReplyIncrease Java Heap space in OSX Express Studio? On OSX the ini file is in the application folder: control click on the application and choose "Show Package Contents" in the context menu.   Then it's in the MacOs folder (you ...
331826 Nov 2014 @ 04:02 PSTDatabaseReplyTeradata Pivot Is there Seq_nbr guaranteed to be always in the correct order: 1,2,1,2,1,2,...? SELECT EMP_ID,Date,Seq_nbr, Punch AS Punch_in, MIN(Punch) -- next value OVER (PARTITION BY EMP_I...
331726 Nov 2014 @ 03:55 PSTDatabaseReplyNeed help on creating table. Error 3933Hi Prasanth, the maximum size of a row in Teradata is limited to a bit less than 64k bytes, for a VarChar the defined maximum length (*2 if it's Unicode) will be used for the row length c...
331626 Nov 2014 @ 03:50 PSTDatabaseReplyDelete old records from a table after a period of timeOf course you can use a trigger (but change the syntax from sqllite to Teradata), but why trigger a delete whenever there's a new row inserted? Better run a daily batch job to delete the outda...
331526 Nov 2014 @ 03:44 PSTDatabaseReplyExecute permissionHi Blaine, GRANT EXECUTE ON object = the right to execute a MACRO   GRANT EXECUTE ON PROCEDURE object or GRANT EXECUTE PROCEDURE ON object  = the right to CALL a PROCEDURE   G...
331426 Nov 2014 @ 03:36 PSTGeneralReplyPartitioningThe combined partitioning expression is the PARTITION number, which is calculated on based on the number of PARTITION#Ln in each Level, basically a number between 1 and the product of the number of...
331326 Nov 2014 @ 03:20 PSTDatabaseReplyTimestamp and date calculationHi Harpreet, this was probably Geoffrey Rommel's  Dates, Times and Timestamps in Teradata (through TD 12) at www.teradataforum.com 
331226 Nov 2014 @ 03:17 PSTDatabaseReplyselect value from string with varying positionHi Mike, what's your Teradata release? In TD14 you can use REGEXP_SUBTR to extract a price: REGEXP_SUBSTR(col, '\$[0-9]*.[0-9]+')  
331122 Nov 2014 @ 10:42 PSTTeradata StudioReplySQL WARNING from statement: State = HY000, Error Code = 1298This is just a warning, indicating that the export used NO SPOOL mode. Simply ignore it :-)
331020 Nov 2014 @ 04:59 PSTDatabaseReplyOREPLACE There might be an old oReplace C-UDF with a different definition in the syslib database. If a function is not qualified the parser checks the current default db, syslib and td_sysfnlib in this ord...
330919 Nov 2014 @ 11:54 PSTDatabaseReplyTable Lock in TeradataHow is this table accessed? One or multiple Inserts per job? Any Selects? What transaction mode is used? ANSI or Teradata?    
330819 Nov 2014 @ 11:49 PSTDatabaseReplyDropping and creating case_n partitionThere's no way to drop/add a partition to an existing CASE_N, only RANGE_N allows that.
330719 Nov 2014 @ 11:35 PSTDatabaseReplySplit The Column which is delimited into separate RowsHi Naveen, this question has been asked numerous times :-)   What's your Teradata release? Since TD14 there's STRTOK_SPLIT_TO_TABLE: SELECT * FROM TABLE (STRTOK_SPLIT_TO_TABLE...
330619 Nov 2014 @ 04:15 PSTDatabaseReplyhelp on tuning a queryHi Sri, can you show the explain? What's the rowcount for both tables?
330519 Nov 2014 @ 03:19 PSTDatabaseReplyHow to decide about Stats for new queriesHi Gian, besides using HELPSTATS you find the official recommendata in Carrie Ballinger's blog: Statistics Collection Recommendations – Teradata Database 14.10 Statistics collection re...
330419 Nov 2014 @ 03:16 PSTDatabaseReplyDECIMAL REPRESENTATION IN EXPORTED FILE AND TABLE Hi Sri, this is probably due to the "Display BigInt and Decimal(16)+ values as Strings" option being unchecked, i can't remember where this is set in SQLA12, might be Tools-Options-D...

Pages