3328 | 02 Dec 2014 @ 10:21 PST | General | Reply | Add title to view column | You 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';
... |
3327 | 01 Dec 2014 @ 02:30 PST | Database | Reply | Help with avoiding redistribution using a hash index | You 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 ... |
3326 | 01 Dec 2014 @ 02:13 PST | Database | Reply | Identify 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
|
3325 | 01 Dec 2014 @ 02:10 PST | Database | Reply | Check for special character in a string | TD 14 supports regular expressions:
REGEXP_SUBSTR(col, '([0-9]{1,3}\.){3}[0-9]{1,3}')
|
3324 | 01 Dec 2014 @ 01:39 PST | Database | Reply | selecting 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... |
3323 | 30 Nov 2014 @ 11:45 PST | Database | Reply | selecting one record out of many records depending on few rules | Is 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... |
3322 | 30 Nov 2014 @ 11:14 PST | Database | Reply | Stored Procedure for storing the DDL of Table in a variable | Yep, this approach will fail for tables with identity.
Have a look at Glenn McCall's article Running Unsupported Queries from a Stored Procedure
|
3321 | 30 Nov 2014 @ 08:11 PST | Database | Reply | Stored Procedure for storing the DDL of Table in a variable | Instead 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;
|
3320 | 30 Nov 2014 @ 08:00 PST | Database | Reply | QUERY OPTIMIZATION | Your 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... |
3319 | 26 Nov 2014 @ 04:21 PST | Teradata Studio | Reply | Increase 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 ... |
3318 | 26 Nov 2014 @ 04:02 PST | Database | Reply | Teradata 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... |
3317 | 26 Nov 2014 @ 03:55 PST | Database | Reply | Need help on creating table. Error 3933 | Hi 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... |
3316 | 26 Nov 2014 @ 03:50 PST | Database | Reply | Delete old records from a table after a period of time | Of 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... |
3315 | 26 Nov 2014 @ 03:44 PST | Database | Reply | Execute permission | Hi 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... |
3314 | 26 Nov 2014 @ 03:36 PST | General | Reply | Partitioning | The 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... |
3313 | 26 Nov 2014 @ 03:20 PST | Database | Reply | Timestamp and date calculation | Hi Harpreet,
this was probably Geoffrey Rommel's Dates, Times and Timestamps in Teradata (through TD 12) at www.teradataforum.com
|
3312 | 26 Nov 2014 @ 03:17 PST | Database | Reply | select value from string with varying position | Hi Mike,
what's your Teradata release?
In TD14 you can use REGEXP_SUBTR to extract a price:
REGEXP_SUBSTR(col, '\$[0-9]*.[0-9]+')
|
3311 | 22 Nov 2014 @ 10:42 PST | Teradata Studio | Reply | SQL WARNING from statement: State = HY000, Error Code = 1298 | This is just a warning, indicating that the export used NO SPOOL mode.
Simply ignore it :-)
|
3310 | 20 Nov 2014 @ 04:59 PST | Database | Reply | OREPLACE | 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... |
3309 | 19 Nov 2014 @ 11:54 PST | Database | Reply | Table Lock in Teradata | How is this table accessed?
One or multiple Inserts per job? Any Selects?
What transaction mode is used? ANSI or Teradata?
|
3308 | 19 Nov 2014 @ 11:49 PST | Database | Reply | Dropping and creating case_n partition | There's no way to drop/add a partition to an existing CASE_N, only RANGE_N allows that.
|
3307 | 19 Nov 2014 @ 11:35 PST | Database | Reply | Split The Column which is delimited into separate Rows | Hi 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... |
3306 | 19 Nov 2014 @ 04:15 PST | Database | Reply | help on tuning a query | Hi Sri,
can you show the explain?
What's the rowcount for both tables?
|
3305 | 19 Nov 2014 @ 03:19 PST | Database | Reply | How to decide about Stats for new queries | Hi Gian,
besides using HELPSTATS you find the official recommendata in Carrie Ballinger's blog:
Statistics Collection Recommendations – Teradata Database 14.10
Statistics collection re... |
3304 | 19 Nov 2014 @ 03:16 PST | Database | Reply | DECIMAL 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... |