#DateForumTypeThreadPost
367828 Apr 2015 @ 02:39 PDTAnalyticsReplyFind average time of dayHi 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)...
367728 Apr 2015 @ 02:16 PDTDatabaseReplyTune qryThere's no evident problem, did you check QryLogStepsV for the actual resusage? 
367628 Apr 2015 @ 02:14 PDTDatabaseReplyUDF 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...
367528 Apr 2015 @ 02:12 PDTDatabaseReplyNeed help on Converting from Characters into IntegerWhat's the datatypes of those custID1s? Why do you cast to INT/DATE and back to VARCHAR?
367426 Apr 2015 @ 04:12 PDTAnalyticsReplyFind average time of dayHi 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 ...
367325 Apr 2015 @ 10:31 PDTDatabaseReplyaggregate 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  
367225 Apr 2015 @ 10:26 PDTAnalyticsReplyFind average time of dayYou 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...
367121 Apr 2015 @ 12:45 PDTDatabaseReplyoptimisation of a query with TRIM functionDo 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...
367021 Apr 2015 @ 12:41 PDTData ModelingReplyTable with Primary key and Primry IndexHi 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...
366921 Apr 2015 @ 10:16 PDTDatabaseReplyStats CollectionHi 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...
366821 Apr 2015 @ 10:10 PDTData ModelingReplyTable with Primary key and Primry IndexHi 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...
366714 Apr 2015 @ 09:39 PDTDatabaseReplyPattern for LIKE OperatorIf you're on TD14 you might use a Regular Expression: where REGEXP_SIMILAR(table_name, 'ABC_[0-9].*') = 1  
366612 Apr 2015 @ 02:53 PDTDatabaseReplyPattern match and Extracting the words from columnHi 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...
366512 Apr 2015 @ 01:15 PDTDatabaseReplyPattern match and Extracting the words from columnHi 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...
366411 Apr 2015 @ 02:54 PDTDatabaseReplyClassification of dates in different columnsNow I see, remove the "NULLS LAST", it's TD14.10 syntax and not needed in your case, it was a cut&paste error...
366311 Apr 2015 @ 02:39 PDTGeneralReplyValue 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...
366210 Apr 2015 @ 03:19 PDTDatabaseReplyClassification of dates in different columnsMy syntax was ok, maybe you removed a ")"?
366110 Apr 2015 @ 01:40 PDTDatabaseReplyClassification of dates in different columnsFor 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 = &#...
366009 Apr 2015 @ 02:33 PDTDatabaseReplyClassification of dates in different columnsThat's why I wrote "maybe you need a DENSE_RANK instead of a RANK" :-) Are you on TD14.10?
365907 Apr 2015 @ 02:40 PDTDatabaseReplyClassification of dates in different columnsThis 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...
365807 Apr 2015 @ 02:15 PDTTeradata StudioReplyTeradata Studio Express 15 (mac) slow & laggyOn 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 ...
365707 Apr 2015 @ 02:06 PDTDatabaseReplyView removes duplicate records?UNION defaults to DISTINCT, you must override it using UNION ALL.
365607 Apr 2015 @ 04:07 PDTDatabaseReplypartition by prior weekWhatever 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....
365507 Apr 2015 @ 04:02 PDTGeneralReply DDL of the tables in one single queryYou 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...
365407 Apr 2015 @ 03:47 PDTTeradata StudioReplyUnable to parse simple query: version 14.10.01If this is your actual DDL it's the ',' before the final ')'.

Pages