4053 | 24 Oct 2015 @ 11:09 PDT | General | Reply | Is this explain plan normal? | Hi Zarrar,
this is usually the optimizer's plan for UNIONing two SELECTs.
|
4052 | 23 Oct 2015 @ 11:52 PDT | Analytics | Reply | What, no REPEAT(X,n) function for Teradata? | Since TD14 there's LPAD/RPAD or TO_CHAR:
LPAD(TRIM(ACC_No), 8, '0')
TO_CHAR(Acc_no, '00000000')
Before you can apply a FORMAT:
CAST(CAST(Acc_no AS FORMAT '9... |
4051 | 23 Oct 2015 @ 11:46 PDT | Analytics | Reply | How strip of the non numeric data from VARCHAR field | If you only want to digits only:
WHERE REGEXP_SIMILAR(col1, '^[0-9]*$') = 0
Or
WHERE RTRIM(col1, '0123456789') = ''
|
4050 | 23 Oct 2015 @ 11:36 PDT | General | Reply | Teradata SQL Assist - Datepart Function | When does your week start, Sunday or Monday?
All the sys_calendar colunms are available as functions, too:
-- week starts on Sunday:
td_day_of_week(current_date)
or
DayNumber_Of_W... |
4049 | 19 Oct 2015 @ 09:45 PDT | Database | Reply | Modify TD maximum of 16 open responses per connection | There's no way to increase this limit, it's built-in.
But usually recursion can be rewritten with loops :-)
|
4048 | 19 Oct 2015 @ 09:43 PDT | Connectivity | Reply | Functions doesn't work with the ODBC connection | MONTH is an ODBC function which is rewritten to valid Teradata SQL by the ODBC driver if the "Disable Parsing" option has not been checked and the query is a DML statement.
&n... |
4047 | 19 Oct 2015 @ 12:26 PDT | Database | Reply | Performance issue with Coalesce | Hi Amit,
COALESCE in joins might change the plan, if it's in a join on the PI-columns when NULLs should be treated equal. In that case there's a recommendation to use ON (t1.col = t2.col O... |
4046 | 18 Oct 2015 @ 10:45 PDT | Database | Reply | Order by Date | I never encountered problems with sort orderm but without showing both "order by" it's hard to tell...
Q1: Of course casting a date to string might change the order if it's not b... |
4045 | 16 Oct 2015 @ 10:45 PDT | Database | Reply | LOCK IN ACCESS SELECT timestamp effectiveness | Hi RS,
please post new questions a new topic.
Regarding the product join:
http://forums.teradata.com/forum/general/recursive-query-spool-space-error#comment-136853
|
4044 | 16 Oct 2015 @ 10:41 PDT | Teradata Applications | Reply | Teradata Addition Issue | You assign an alias "TotalWork", but there's also a column "q4.totalwork", when you use "TotalWork" in "cast (TotalWork/Totalhrs as decimal (20,2)" it... |
4043 | 15 Oct 2015 @ 11:55 PDT | Teradata Applications | Reply | Teradata Addition Issue | Strange, can you show some actual data plus result?
|
4042 | 15 Oct 2015 @ 11:52 PDT | Database | Reply | How to get previous row values | What's your release?
In TD14.10 there's
LAST_VALUE(place IGNORE NULLS)
over (partition by name
ORDER BY id)
|
4041 | 15 Oct 2015 @ 11:48 PDT | Database | Reply | Average across columns - How can I do this better and in cleaner code? | Hi Will,
logic is correct, code is correct, result is correct :-)
I would just do some cosmetic changes by replacing the proprietary ZEROIFNULL/NULLIFZERO with a Standard SQL COALESCE/ NU... |
4040 | 15 Oct 2015 @ 11:44 PDT | Database | Reply | Performance issue with Coalesce | Hi Amit,
COALESCE doesn't use much CPU, but it might change the optimizer's plan.
I don't know if rewriting the logic will help:
OR ~DATABASE_NAME_BASE~.~TABLE_NAME~.TYPE_ID <&g... |
4039 | 15 Oct 2015 @ 11:24 PDT | Database | Reply | Need to calculate Age in days of a record | This is SQL Server syntax, why don't you ask in a Microsoft forum?
SYSDATETIME returns a datetime2 and you can't use plus/minus on that datatype, you need to use DATEDIFF instead:
DATED... |
4038 | 15 Oct 2015 @ 12:47 PDT | Database | Reply | query to bring in multiple row values into one record | To split in two columns you don't need two Recursive Queries, simply split the data into tw columns when you create the Volatile Table.
Additionally you can change the logic to start with the ... |
4037 | 15 Oct 2015 @ 12:38 PDT | Database | Reply | LOCK IN ACCESS SELECT timestamp effectiveness | Hi Pierre,
"before the beginning of INSERT TRANSACTION" would be READ COMMITED, but ACCESS LOCK means READ UNCOMMITTED, i.e. you might read a block with already inserted rows = &quo... |
4036 | 15 Oct 2015 @ 12:30 PDT | Database | Reply | regexp_replace woes | First some control characters are replaced by a space:
\f = 0x0C form feed
\n = 0x0A newline
\r = 0x0D carriage return
\t = 0x09 horizontal tab
\v = 0x0B vertical tab
And... |
4035 | 15 Oct 2015 @ 08:21 PDT | Database | Reply | Activating new character sets other than default | And have a look at the Unicode Tool Kit if you got some characters which fail to import
|
4034 | 15 Oct 2015 @ 08:18 PDT | Database | Reply | Failed Query! Not sure why - 3706 Syntax Error? | Hi Sandeep,
please post new questions as new topic.
#1: ISO & ANSI & Standard SQL format: yyyy-mm-dd, literals as DATE '2015-10-15'
#2: This is a very generic error, you ... |
4033 | 15 Oct 2015 @ 08:14 PDT | Database | Reply | REcursive process | Can you show your current query?
|
4032 | 15 Oct 2015 @ 08:14 PDT | Database | Reply | query to bring in multiple row values into one record | You forgot to increase the size of the "mnemonic" column, thus it's the original size and the added lines are silently truncated:
select claim_id, mnemonic (VARCHAR(1000)), line
... |
4031 | 15 Oct 2015 @ 07:50 PDT | Database | Reply | Least/Greatest Function with Dates | Yep, this stupid function only works with numeric & character columns.
Just don't ask me why, the first time I needed it was on dates, too :-)
At least there's a workaround:
... |
4030 | 15 Oct 2015 @ 07:45 PDT | Database | Reply | how to find version of a teradata database? | What does OCA mean?
Oracle Ceritfied Associate? :-)
|
4029 | 15 Oct 2015 @ 07:41 PDT | Database | Reply | Partial string matching requires character operands | #1: There's no "is like"
#2: Just read the error message: "requires character operands" means "col1" is not a CHAR or VARCHAR, but probably a numeric column.
|