#DateForumTypeThreadPost
252820 Jan 2014 @ 11:53 PSTAnalyticsReplyTranspose rows to columnsHi Cheeli, select min(case when sales_month = 'jan' then sales_amnt else 0 end) as jan, min(case when sales_month = 'feb' then sales_amnt else 0 end) as feb, ... min...
252720 Jan 2014 @ 08:29 PSTAnalyticsReplymultiple join in sub quaryWhen i try to explain my query it fails with a "table not found", which means it's syntactically correct. Btw, line 60 is commented out in my query, did you run this or your original...
252620 Jan 2014 @ 07:13 PSTDatabaseReplyConvert Teradata Mode Procedure to ANSI ModeODBC Options: Session Mode Or within the connection steing: SessionMode=ANSI
252520 Jan 2014 @ 06:40 PSTDatabaseReplyCast-DATEHi Sri, use your existing calendar or the built-in sys_calendar to calculate all possible values for your expected date range and then [left] join to it: SELECT (EXTRACT(MONTH FROM calenda...
252420 Jan 2014 @ 12:03 PSTDatabaseReplyCast-DATE a11.XYZ_SK is an INTEGER column with YYYYMMDD, e.g. today is 20140120. Substracting 19000000 results in an INTEGER with Teradata's internal date representation: 1140120 And th...
252319 Jan 2014 @ 11:54 PSTDatabaseReplyInserting into a timestamp(6) columnIf you're on TD14 you can use TO_TIMESTAMP, which is a bit more flexible than Teradata's FORMAT: TO_TIMESTAMP(dt, 'YYYY-MM-DD-HH24:MI:SS:FF6' )  
252219 Jan 2014 @ 02:50 PSTDatabaseReplyHow to transpose rows to columns without the use of .sql and Bteqs SELECT WUNO, MAX(CASE WHEN rnk = 1 THEN x ELSE '' END) || MAX(CASE WHEN rnk = 2 THEN '_' || x ELSE '' END) || MAX(CASE WHEN rnk = 3 THEN '_' || x E...
252119 Jan 2014 @ 02:39 PSTToolsReplyFastExport script and output delimitersHi Prashant, this has been discussed several times, the best solution is to switch to TPT, the latest versions can directly export delimited data without need for TRIM/concat. Otherwise the "...
252019 Jan 2014 @ 02:36 PSTGeneralReplydupliacate rowsThese are no duplicate rows. You probably have some rows violating your Primary Key, but i don't have a clue about your rules why it's wrong and how to fix it
251919 Jan 2014 @ 02:34 PSTDatabaseReplyIssue with TIME(6) datatype field in TeradataHi Ramkumar, this is a known problem due to some settings in the ODBC driver. There are several solutions: #1: in older versions of SQL Assistant there's "Allow Use of ODBC SQL Exte...
251818 Jan 2014 @ 08:32 PSTDatabaseReplyJoin tables and replalace NULLSTry this: SELECT tab.*, MIN(tab2.Manager) OVER (PARTITION BY tab.CustId ORDER BY tab.MonthID RESET WHEN tab2.Manager IS NOT NULL ROWS UNBOUNDED PRECE...
251717 Jan 2014 @ 01:22 PSTDatabaseReplyFind usage PMon data is always snapshot data. You might find historical data within DBQL, but Viewpoint should be the easiest way to find resource intensive queries using the Query Spotlight portlet.
251617 Jan 2014 @ 12:36 PSTDatabaseReplyHow to grant a role to publicTrying to grant a role to public should return an error message. Public is a role and it's always active for every user.  So simply grant the right directly to public.
251517 Jan 2014 @ 09:58 PSTAnalyticsReplymultiple join in sub quarySeems you did some modifications of Derived Tables resulting in too many ')', easy to spot when you format the SQL: SELECT MIN(trs_dt) ,MAX(trs_dt) ,COUNT(DISTINCT a.cus_ian_...
251417 Jan 2014 @ 09:29 PSTDatabaseReplyFind usage You can easily sort the Query Portlet in Viewpoint by CPU-Delta. If you want to write a query do that on your own there's the SQL PMon API, e.g. SELECT 100 * AvgAmpCPUSec / SUM(AvgAmpCPUSec...
251317 Jan 2014 @ 12:24 PSTDatabaseReplyAGGREGATIONWhat are you trying to achieve? Any column which is not part of the GROUP BY must be used within an aggregate function.  Without showing the actual query it's hard to tell what's wro...
251216 Jan 2014 @ 01:09 PSTDatabaseReplyHow to use regex in Teradata or 'LIKE' operator in teradataHi Raja, this will fail for data like '1a1'.   Using a regular expression it's: WHERE REGEXP_SIMILAR(id, '[0-9]+', 'c')=1  
251116 Jan 2014 @ 12:55 PSTDatabaseReplyHow to grant a role to publicWhat exact error is returned? For SPs there's a SECURITY option either CREATOR/DEFINER/OWNER or INVOKER. You should check the DDL manual which one you actually need.
251015 Jan 2014 @ 02:41 PSTDatabaseReplyAccount balance SQLDo you need this for a single account or multiple accounts? What's your TD release?  
250915 Jan 2014 @ 12:58 PSTDatabaseReplyDate ConversionIt's FORMAT 'ddmmmyyyy'
250815 Jan 2014 @ 12:52 PSTDatabaseReplyGetting week of the year from a dateYou should change the calculation to the ISO calendar: TD_SYSFNLIB.WEEKNUMBER_OF_YEAR (CURRENT_DATE, 'ISO') And you probably need the ISO-year, too: YEARNUMBER_OF_CALENDAR(current_da...
250715 Jan 2014 @ 12:38 PSTGeneralReplyOmit Rows From TableBased on your description this sounds like a simple Inner Join. You should give more details, maybe some rows plus estimated result. 
250614 Jan 2014 @ 02:33 PSTDatabaseReplySELECT MAX(timeValue), MIN(timevalue) FROM OurDB.DimTime - Returns wrong resultYour session timezone is set to -6, try SELECT timeValue AT 0 FROM OurDB.DimTime ORDER BY 1 SQL Data Manipulation Language Chapter 1: The SELECT Statement ORDER BY Clause Unexpected Sort ...
250514 Jan 2014 @ 10:57 PSTDatabaseReplySQL helpROWS 3 PRECEDING is equivalent to ROWS BETWEEN 3 PRECEDING AND CURRENT ROW. Simply move the functions as columns into the SELECT and check the result :-)
250412 Jan 2014 @ 04:09 PSTDatabaseReplyWay to optimize query for bigdata processingAs you access two months of a table partitioned by day why don't you simply process all the rows for a single day = 59 batches?   This will return all batches in one query, but it mig...

Pages