3678 | 28 Apr 2015 @ 02:39 PDT | Analytics | Reply | Find average time of day | Hi John,
the distance between two points can easily be calculated using Pythagoras' theorem, no need for geospatial :-)
SELECT
tr.hours
,SQRT(((tr.x-dt.x)**2) + ((tr.y-dt.y)**2)... |
3677 | 28 Apr 2015 @ 02:16 PDT | Database | Reply | Tune qry | There's no evident problem, did you check QryLogStepsV for the actual resusage?
|
3676 | 28 Apr 2015 @ 02:14 PDT | Database | Reply | UDF that accept string then process that string and return processed string |
REPLACE FUNCTION concat(str1 VARCHAR(100), str2 VARCHAR(100))
RETURNS VARCHAR(200)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
RETURNS NULL ON NULL INPUT
SQL SECURITY DEFINER
COLLATION INVOKER... |
3675 | 28 Apr 2015 @ 02:12 PDT | Database | Reply | Need help on Converting from Characters into Integer | What's the datatypes of those custID1s?
Why do you cast to INT/DATE and back to VARCHAR?
|
3674 | 26 Apr 2015 @ 04:12 PDT | Analytics | Reply | Find average time of day | Hi John,
Getting the closest point is a bit tricky, how do you define "closest"?
What result should be returned for hours 10 and 14 (or 6 and 18), Midnight or noon?
What's your TD ... |
3673 | 25 Apr 2015 @ 10:31 PDT | Database | Reply | aggregate function based on multiple rows | Can you show your actual query?
Do you need this result for a single year of multiple years?
Based on your description you will get the same week of the previous year using YrWK - 100
|
3672 | 25 Apr 2015 @ 10:26 PDT | Analytics | Reply | Find average time of day | You can't do an average on time, but it's allowed for interval :-)
Subtracting two times return an interval:
AVG(CAST(opened_at AS TIME(0)) -- extract only t... |
3671 | 21 Apr 2015 @ 12:45 PDT | Database | Reply | optimisation of a query with TRIM function | Do you really need that TRIM?
Do you actually have leading blanks in those columns? Ouch, that's worst case, you should clean your data immediatly and change your loading process.
If th... |
3670 | 21 Apr 2015 @ 12:41 PDT | Data Modeling | Reply | Table with Primary key and Primry Index | Hi TDArc,
simply drop the PK and trust your loading process :-)
You must ensure that the logical PK is not violated during load anyway. You never load a large number of rows if the PK is act... |
3669 | 21 Apr 2015 @ 10:16 PDT | Database | Reply | Stats Collection | Hi Kishore,
as VandeBergB mention you might switch to TD14.10's AutoStats feature.
If you still want to collect using your own logic, I would suggest switching to the apporach I described ove... |
3668 | 21 Apr 2015 @ 10:10 PDT | Data Modeling | Reply | Table with Primary key and Primry Index | Hi TDArc,
a Primary Key is implemented as a USI if there's a PI :-)
PKs are often not implemented in a warehouse, because they might not be used in WHERE/JOIN. And uniquenes must be guarantee... |
3667 | 14 Apr 2015 @ 09:39 PDT | Database | Reply | Pattern for LIKE Operator | If you're on TD14 you might use a Regular Expression:
where REGEXP_SIMILAR(table_name, 'ABC_[0-9].*') = 1
|
3666 | 12 Apr 2015 @ 02:53 PDT | Database | Reply | Pattern match and Extracting the words from column | Hi Sri,
there's no Teradata-specific documentation on regex, but there are many books and online resources available, simply google for "regular epression". There are different diale... |
3665 | 12 Apr 2015 @ 01:15 PDT | Database | Reply | Pattern match and Extracting the words from column | Hi Sri,
this is a RegEx to find the word after 'A_B_C.', the 1st group searches for the pattern without adding it to the result and the 2nd group extracts the following word:
REGEXP_SUB... |
3664 | 11 Apr 2015 @ 02:54 PDT | Database | Reply | Classification of dates in different columns | Now I see, remove the "NULLS LAST", it's TD14.10 syntax and not needed in your case, it was a cut&paste error...
|
3663 | 11 Apr 2015 @ 02:39 PDT | General | Reply | Value parsing in Teradata | Delimited data is absolutely worst case in a relational dabatase system.
The best solution would be fixing the data model.
Otherwise it depends on your Teradata release.
Since TD14... |
3662 | 10 Apr 2015 @ 03:19 PDT | Database | Reply | Classification of dates in different columns | My syntax was ok, maybe you removed a ")"?
|
3661 | 10 Apr 2015 @ 01:40 PDT | Database | Reply | Classification of dates in different columns | For DENSE_RANK it's a bit more complicated, see Missing Functions: DENSE_RANK
SELECT dt.*
, CASE WHEN Prod1 = '0' THEN '0' ELSE TRIM(rnk) END ||
CASE WHEN Prod2 = ... |
3660 | 09 Apr 2015 @ 02:33 PDT | Database | Reply | Classification of dates in different columns | That's why I wrote "maybe you need a DENSE_RANK instead of a RANK" :-)
Are you on TD14.10?
|
3659 | 07 Apr 2015 @ 02:40 PDT | Database | Reply | Classification of dates in different columns | This is a horrible data model, as a result you need horrible SQL :-)
Following approach normalizes the columns into rows and the RANKs the data. Maybe you need a DENSE_RANK instead of a RA... |
3658 | 07 Apr 2015 @ 02:15 PDT | Teradata Studio | Reply | Teradata Studio Express 15 (mac) slow & laggy | On my system this was mainly related to low memory assigned to Java.
There's a TeradataStudio.ini file within the app folder:
/Applications/TeradataStudio/Teradata Studio.app/Contents/MacOS
... |
3657 | 07 Apr 2015 @ 02:06 PDT | Database | Reply | View removes duplicate records? | UNION defaults to DISTINCT, you must override it using UNION ALL.
|
3656 | 07 Apr 2015 @ 04:07 PDT | Database | Reply | partition by prior week | Whatever calculation you put in PARTITION BY doesn't matter, it's still the same rows.
But you don't need a OLAP-function, it's a simple aggregate:
SELECT
t1.REPORT_DT,
t1.... |
3655 | 07 Apr 2015 @ 04:02 PDT | General | Reply | DDL of the tables in one single query | You can't get the CREATE as a single row because it might be more than the maximum VarChar size (or you want it as a CLOB).
Plus RequestText is not reliable for tables, you can only create all... |
3654 | 07 Apr 2015 @ 03:47 PDT | Teradata Studio | Reply | Unable to parse simple query: version 14.10.01 | If this is your actual DDL it's the ',' before the final ')'.
|