#DateForumTypeThreadPost
442804 May 2016 @ 01:55 PDTGeneralReplyLooking for help with regards to nested aggregatesSo this is the base data, no pre-calculation, yet? You need another Derived Table to combine the times for enter and exit: SELECT AVG(maxdiff) FROM ( SELECT B_DT, MAX(diff) AS maxdiff ...
442704 May 2016 @ 03:26 PDTDatabaseReplyNeed to calculate count of first name and last name based on Individual Names CASE WHEN INDV_FRST_NM IS NOT NULL THEN 1 ELSE 0 END + CASE WHEN INDV_LST_NM IS NOT NULL THEN 1 ELSE 0 END  
442604 May 2016 @ 03:23 PDTAnalyticsReplyTeradata - calculate KPI every month for last 24 monthsOuch, this might be possible, but would be quite hard to implement as a single query for 24 months. But it's a one time only task to create the initial 24 months, after that you run the query ...
442504 May 2016 @ 03:16 PDTGeneralReplyLooking for help with regards to nested aggregatesLondon Oyster Card?  :-) You need another nesting level for each nested aggregation: avg(max(count))) select acg(maxdiff) from ( select date, max(diff) as maxdiff from ( ...
442404 May 2016 @ 02:56 PDTDatabaseReply3707 ErrorThe last query in my post on MEDIAN (http://developer.teradata.com/blog/dnoeth/2011/06/missing-functions-percentile-disc-percentile-cont-median) shows how to get the it as OLAP result, you nee...
442303 May 2016 @ 02:29 PDTAnalyticsReplyTeradata - calculate KPI every month for last 24 monthsCan you rollup the data into one row per month and then simply apply OLAP-functions like  SUM(col) OVER (ORDER BY year_month ROWS 23 PRECEDING)   In Standard SQL there's RANGE, t...
442203 May 2016 @ 02:25 PDTDatabaseReplySort a cell value What does the actual data look like?  Is there a limited number of values? Is this a one time only task?   And of course the most important question: Why is this column not properly n...
442103 May 2016 @ 02:18 PDTDatabaseReplyFind the Nearest Future Record in a tableTD14.10+ supports Standard SQL's FIRST/LAST_VALUE: first_value(my_id ignore nulls) over (partition by emp_id order by strt_dt rows between current row and unbounded folowing...
442002 May 2016 @ 09:46 PDTDatabaseReply3707 ErrorMEDIAN is only an aggregate function.   But this query makes no sense, you apply an OLAP function on unique rows. What are you trying to do?  
441902 May 2016 @ 01:57 PDTConnectivityReplyCan not connect to TD from SQLA over home network, but can connect using BTEQ!BTEQ uses Teradata's low level interface, CLI. Did you double check if it's the same IP-address 192.168.1.2 in both BTEQ and ODBC?
441802 May 2016 @ 12:11 PDTDatabaseReplyMin and Max date for every change in value of a flagSee http://forums.teradata.com/forum/database/from-table-with-daily-log-to-table-with-from-to-dates#comment-147472
441702 May 2016 @ 12:07 PDTDatabaseReplyNeed to write an SQL query as per the below data.In both cases you can utilize LAST_VALUE: select source, sum(data), source-1, last_value(sum(data)) over (order by source rows between 1 preceding and 1 preceding) from ...
441602 May 2016 @ 12:00 PDTConnectivityReplyCan not connect to TD from SQLA over home network, but can connect using BTEQ!Check you VMWare network settings, it's probably NAT or Host Only, in both cases your VM is not visible from outside, you must change it to Bridged (this will also change the assigned IP-addres...
441501 May 2016 @ 11:57 PDTTeradata ApplicationsReplySet Default week start day as Friday in TeradataThere's no concept of a DATEFIRST is Teradata and the built-in calendars support only a variation of calendar where the first partial week of a year is week 0 or ISO Standard weeks or Oracle co...
441430 Apr 2016 @ 04:25 PDTDatabaseReplyDiff between CTE and Volatile tables in TeradataA Common Table Expression is the same as a Derived Table, the only difference is the place where it's defined and reusability (CEs can be used multiple times, but a DT must be repeated). Some ...
441330 Apr 2016 @ 04:17 PDTConnectivityReplyDelay in establishing connection form hibernate to Teradata.I don't know about JDBC, but this seems to be related to the network speed, locally it's 30 ms but on the server 300 ms to get a response, check ping/traceroute timings.
441229 Apr 2016 @ 07:03 PDTAnalyticsReplyExtract numeric values from stringI didn't see that it's an exact number of digits, no need to repeat [0-9] multiple times, you can specify the number directly: REGEXP_SUBSTR(COL1,'[0-9]{6}-[0-9]{5}')  
441129 Apr 2016 @ 05:49 PDTAnalyticsReplyExtract numeric values from stringUse a REGEXP_SUBSTR to extract a group of digits [0-9]+ followed by a dash - followed by a group of digits [0-9]+ REGEXP_SUBSTR(COL1,'[0-9]+-[0-9]+') AS COL2  
441028 Apr 2016 @ 08:25 PDTDatabaseReplyMultiple with clauseHi Vivek, in Teradata the first Select in a set operation determines the resulting data type, simply CAST to a larger size:  SELECT CAST('vast' AS VARCHAR(20)) AS c1, CAST('fix...
440927 Apr 2016 @ 06:53 PDTDatabaseReplyTeradata SQL/procedure : How to replace the multiple occurance of pattern`s in column valuesLooks like your pattern includes everything between & and ; REGEXP_REPLACE(Col1,'&(.*?);','')  
440826 Apr 2016 @ 11:33 PDTDatabaseReplyHelp with queryYou need the earliest date after a previous worked. This should return the expected result using a single STAT-step: SELECT ID, t AS Detected, MIN(CASE WHEN flag = 1 THEN t END) -- get the...
440726 Apr 2016 @ 11:20 PDTConnectivityReplyhow to install teradata express edition on windows 7There's no more Teradata running natively on Windows since TD14.
440626 Apr 2016 @ 11:19 PDTToolsReplyTeradata procedure creation || Aquadata studio There must be a way in Aquadata Studio to submit the CREATE as a single request, currently it splits it into three seperate requests. 
440526 Apr 2016 @ 11:17 PDTDatabaseReplyMultiple with clause WITH MainCTE AS ( SELECT c1 FROM innerCTE ), innerCTE AS ( SELECT 'vast' AS c1, 'fix' AS c2 FROM TABLE UNION SELECT 'int'AS c1 , 'int' AS ...
440426 Apr 2016 @ 02:01 PDTDatabaseReplyRollup subtotal as percent of grand totalI don't think you need ROLLUP, looks like you just want an OLAP-sum on top of the aggregate SUM: SELECT p.product_name, SUM(numOfCalls) TotalCalls, 100.00 * TotalCalls/ SUM(TotalCalls...

Pages