2728 | 07 May 2014 @ 05:00 PDT | Database | Reply | TERADATA | Hi Abhilah,
are those timestamp columns actually VarChars? That's bad.
If they were TIMESTAMPs it would be a simple
WHERE (START_DT, END_DT) OVERLAPS (TIMESTAMP '2014-04-24 12:13:... |
2727 | 07 May 2014 @ 04:19 PDT | Database | Reply | Calculation Skew | Hi Peter,
when you press F12 in TD Admin you can see the submitted query in the "SQL History". You'll notice that the SQL is using a similar calculation, but it's not based on th... |
2726 | 07 May 2014 @ 02:41 PDT | Tools | Reply | BTEQ Import REPORT Mode. Growing to buffer | You're importing in REPORT mode which expects fixed width CHARs, but your data is VARCHAR and your USING includes binary integers.
Try
.IMPORT VARTEXT ' ' FILE=/root/jugal/samples12... |
2725 | 07 May 2014 @ 02:34 PDT | Database | Reply | analog sys.foreign_keys.is_not_trusted | Hi Sergey,
unfortunately there's no way to get the FK type from the data dictionary (afaik).
This info is only stored in the table header, the data dictionary will just show "there's... |
2724 | 07 May 2014 @ 12:55 PDT | Database | Reply | Slowness in Teradata system | Hi Mahendra,
what's your TD release?
Do you have Viewpoint? Then simply use this to find the session(s) consuming most resources or blocking others.
If there's no Viewpoint you can do th... |
2723 | 07 May 2014 @ 12:51 PDT | Training | Reply | Prototypes | Hi Krishna,
did you already check the Resource Library on the Teradata website? It has plenty of case studies, etc.:
http://www.teradata.com/resources/
|
2722 | 07 May 2014 @ 12:42 PDT | Database | Reply | Identifying Temporal Tables | The column Ulrich mentioned exists since TD13.10, so to get all Temporal tables:
SELECT * FROM dbc.TablesV WHERE TemporalProperty <> 'N'
Similar to find the Temporal columns:
&nb... |
2721 | 07 May 2014 @ 12:20 PDT | Database | Reply | Teradata 14.0 - regex_replace unexpected result | You should open an incident with Teradata support, this is definitely a wrong result.
|
2720 | 07 May 2014 @ 12:19 PDT | Database | Reply | Is index creation allowed for a derived column to improve performance? | You can't do indexes on calculated columns, but you might create a Join Index with that calculation.
But unless you have lots of years of data in your table an index will be not selective enou... |
2719 | 07 May 2014 @ 12:10 PDT | Database | Reply | Mvc Compression and Collect Stats on TD 14 | MVC has no direct effect on collecting stats (other than the table might be smaller, thus less data to read).
But I would try to do mainly SAMPLE stats, in my experience it can be done on al... |
2718 | 07 May 2014 @ 12:04 PDT | General | Reply | How to install teradata Sql assistant | SQL Asistant is part of the Windows TTU:
TTU - Windows Install
Btw, Studio Express 13 is quite old, you should update to 15:
Teradata Studio Express
or the full version
Teradata ... |
2717 | 06 May 2014 @ 11:08 PDT | General | Reply | SQL help | You might try a conditional join based on the result of the first join, e.g.
SELECT
id, COALESCE(a.mtr_id, b.mtr_id)
FROM
(
SELECT
STGtable.ID,
CASE WHEN MIN(a.mtr_id) ... |
2716 | 01 May 2014 @ 03:55 PDT | General | Reply | TPT handling the CSV with date and timestamp column | Assuming this is from te APPLY section you need to use two single quotes, but you got three, which simply finishes the SQL string:
:ROW_CREATED_DATE (timestamp(6),format ''yyyy-mm-ddBhh:... |
2715 | 01 May 2014 @ 03:48 PDT | Database | Reply | ABNORMAL BEHAVIOR OF QUERY IN TD14.10 after upgrade from TD14 | Hi Sravan,
it's hard to read because the runtime explain table is skrewed up, but:
the optimizer assumes that this condition
WHERE businesspa0_.ACTV <> 'N'
AND ... |
2714 | 01 May 2014 @ 03:14 PDT | Database | Reply | Transpose and Concatenating Values | Hi Sagar,
only two values?
Then it's easy:
SELECT
ID,
MIN(VALUE) ||
CASE
WHEN COUNT(*) = 1 THEN ''
ELSE ';' || MAX(value)
END AS NEW_VALU... |
2713 | 30 Apr 2014 @ 04:26 PDT | Analytics | Reply | REGEXP_SPLIT_TO_TABLE doesn't work | Hi Bartosz,
there was a change in TD14.10 to add an inputkey and two additional columns in output, of course this was not documented in the manuals :-)
But the TD15 manual are corrected:
Expre... |
2712 | 28 Apr 2014 @ 11:16 PDT | Database | Reply | Cumulative Sum by distinct customer ID by year | @Carlos:
you're right, this specific query was not "easily rewritten", but it's also not an easy question :-)
@Yusuf:
The Deried Table must be in the FROM clause:
se... |
2711 | 28 Apr 2014 @ 03:47 PDT | Database | Reply | DBQL SQLTextInfo 3577: row size or sort key size overflow | You can select up to 2048 columns with a combined size of a bit less than 64KB.
This means defined maximum not actual size.
If your session uses UTF8 you need to multiple the defined size ti... |
2710 | 28 Apr 2014 @ 03:44 PDT | Teradata Applications | Reply | Table properties and utilities to load data | The load utilities are used to load data which doesn't exists yet within the system
If the data is already in a TD table there's no need to use MLoad/FastLoad, as a simple SQL Insert/Selec... |
2709 | 25 Apr 2014 @ 08:46 PDT | Database | Reply | Cumulative Sum by distinct customer ID by year | Hi Carlos,
of course this should work, too.
But the Explain wil be horrible due to the non-equi-Join, including a bad Product Join with a huge intermediate spool. I always try to avoid Scalar Sub... |
2708 | 23 Apr 2014 @ 02:05 PDT | Database | Reply | Need alternative to DBQL processing | Hi Joe,
I never tested this, but when you're able to locate the FExp sessions in DBQL the SpoolUsage of the final step in QryLogStepsV might be what you're looking for.
|
2707 | 23 Apr 2014 @ 01:58 PDT | Database | Reply | Finding the latest date | Then it's a perfect match for those OLAP functions :-)
|
2706 | 23 Apr 2014 @ 01:57 PDT | Database | Reply | Identify Set - Multiset Tables in TD 14 | CREATE TABLE AS existing_table copies everything (including SET/MULTISET, [NOT] NULL, FORMAT, indexes and checks) but Foreign Keys and Triggers.
But when you do CREATE TABLE AS SELECT ev... |
2705 | 23 Apr 2014 @ 01:51 PDT | Database | Reply | Remove the first 4 characters in a string | There's no RIGHT function in Standard/Teradata SQL, but with SUBSTR[ING] it's easy:
SUBSTRING(state FROM 5) -- Standard SQL
or
SUBSTR(state, 5) -- non-Standard SQL
|
2704 | 23 Apr 2014 @ 12:05 PDT | Database | Reply | Find patterns in a string (PATINDEX in Sql server equivalent) | What's your Teradata release?
TD14 implements Regular Expressions:
REGEXP_SUBSTR(column, '[a-z][0-9]{4}',1,1,'i')
|