#DateForumTypeThreadPost
347803 Feb 2015 @ 02:19 PSTGeneralReplyLoading data into TableCreate a table with all the odd number between 1 and the maximum number of columns, e.g. 27 and then cross join: CREATE TABLE odd_numbers(n int); INSERT INTO odd_numbers VALUES (1); INSERT INT...
347702 Feb 2015 @ 09:40 PSTAnalyticsReplyUsing Case When on Teradata Hi Wiyana, is the date hard-coded?   SELECT Col1 ,MAX(CASE WHEN DATE = 20150101 THEN col2 END) ,MAX(CASE WHEN DATE = 20150102 THEN col2 END) FROM tab GROUP BY 1  
347602 Feb 2015 @ 09:32 PSTDatabaseReplywanted to get interval time in the format of Hour:Minute:SecondsThere's no datatype to hold an HOUR TO SECOND with more than 9999 hours. So go with DAY TO SECOND or extract the various parts, calculate hours from days and then concatenate the parts to a st...
347502 Feb 2015 @ 09:27 PSTGeneralReplyLoading data into TableI would load the data as-is and then process it within database. E.g. load as a single VarChar and extract the columns using STRTOK: STRTOK(x, ',', n) n will be hard-coded (one SELECT ...
347402 Feb 2015 @ 09:16 PSTDatabaseReplyPartition of a Timestamp columnHi Samir, 150130 is 1915-01-30, which definitely doesn't fit in any partition :-)   #1 and #2 are exactly the same, but #1 is preferred because it's more readable.   But what...
347302 Feb 2015 @ 09:11 PSTToolsReplyConcatenate one column values into single row valueIf your TD system includes XML, this is the simplest way: SELECT DatabaseName ,TABLENAME ,IndexNumber ,IndexType ,TRIM(TRAILING ',' FROM (XMLAGG(TRIM(Columnname)|| ',...
347231 Jan 2015 @ 08:07 PSTDatabaseReplyDecimal issueWhen you divide by 0.9 you increase the number of fractional digits to 8. Simply cast back to 7 digits using cast(sum(data_volume_month_01)*0.9 as dec(22,7))  
347131 Jan 2015 @ 05:39 PSTGeneralReplyTIME STAMP Difference in DAYSGreat you got the solution, but could you share it, too? And could you mention which DBMS returns one day if it's actually less than one? What does it return for  SELECT (TIMESTAMP '...
347031 Jan 2015 @ 05:36 PSTDatabaseReplyHow to Round Timestamp(6) to nearest hourAdd 30 minutes to the timestamp and then truncate it: ((x + INTERVAL '30' MINUTE) (FORMAT 'YYYY-MM-DDBHH') (CHAR(13))) || ':00:00' (TIMESTAMP(0)) x + (INTERVAL '30...
346931 Jan 2015 @ 05:29 PSTDatabaseReplyHierarchy Query using Recursive functionDo you really need the result as a comma-delimited list or as multiple rows?
346831 Jan 2015 @ 05:28 PSTGeneralReplyTeradata-Combine multiple rows of a column to multiple columns in a rowTwo approaches come to mind: Old style MAX(CASE) over a ROW_NUMBER: SELECT NAME ,MIN(CASE WHEN rn = 1 THEN Department end) ,MIN(CASE WHEN rn = 1 THEN salary end) ,MIN(CASE WHEN...
346731 Jan 2015 @ 05:19 PSTDatabaseReplyREGEXP_INSPR works on litteral data, but not in SELECTThe oREPLACEs only replace a single character, so you better use oTRANSLATE instead: (oreplace(oreplace(oreplace(Transaction_Amount,'0D'XC,''),',',''),' '...
346631 Jan 2015 @ 04:01 PSTDatabaseReplyhow to query large rows in Teradata SQL?Also make shure you set the "Maximum batch size for simple imports" in Tools-Options-Import to 999 (seems to be the max). Additionally using .NET instead of ODBC should be faster.  
346526 Jan 2015 @ 09:02 PSTDatabaseReplyMissing/Invalid SQL statement'E(3707):Syntax error, expected something like ';'Every single quote within a string must be doubled: AND SublineaProductoCD IN (''1'',''2'',''3'',''4'','...
346426 Jan 2015 @ 08:16 PSTDatabaseReplywhy coalesce around time interval calculation does not work ?Hi Ivan, the COALESCE fails because it's you can't mix both INTERVAL and INTEGER. I doubt the first cast returns the correct age, you get the number of days between start and end and then...
346326 Jan 2015 @ 02:34 PSTDatabaseReplyQuestion regarding RIsThere's no way to get the different types of RI from the system tables, only SHOW will return it.   Regarding the original post: WITH CHECK OPTION checks using EXISTS/NOT EXISTS on a st...
346226 Jan 2015 @ 02:14 PSTDatabaseReplyError TableHi Marco, this info is found in dbc.ErrorTblsV.
346125 Jan 2015 @ 11:25 PSTJobsReplyHow to reinitiatite the BTEQ after a sleep time if SQL code outputs an error?Better use EXISTS instead: Sel 1 where exists ( select * from SOURCE_TABLE where cast(CMPGN_RUN_DATE as date)=current_date-1 and TRANS_DATE =current_date-1 ); .IF ACTIVITYCOUNT ...
346025 Jan 2015 @ 08:22 PSTConnectivityReplyTeradata ODBC SoutrceYes, that's the full file path.
345925 Jan 2015 @ 04:39 PSTConnectivityReplyTeradata ODBC SoutrceI don't know about the settings in Windows VM Player as I run it on a Mac using VMWare Fusion. But the Teradata VM is running Linux (SUES Enterprise Linus 10 or 11).    The PANIC m...
345824 Jan 2015 @ 10:59 PSTConnectivityReplyTeradata ODBC SoutrceNo, there's no more Teradata on Windows. But as it's a virtual machine the OS doesn't matter, you just need VMWare Player for Windows.
345724 Jan 2015 @ 09:36 PSTConnectivityReplyTeradata ODBC SoutrceYou can't connect to a database server if there's no server :-)   SQL Assistant is just a client (so you could connect to your existing MySQL server using ODBC), but for Teradata you...
345624 Jan 2015 @ 08:43 PSTConnectivityReplyTeradata ODBC SoutrceIf you work on the PC where the Teradata VM is running you don't connect to localhost (only from within the VM). You need the VM's IP instead: Open a terminal window in the VM and do a ifc...
345524 Jan 2015 @ 04:14 PSTDatabaseReplyConverting a date timeYou can't reduce the precision of a timestamp (blame Standard SQL), so there are two solutions: - don't use TIMESTAMP(6) for this column if only need TIMESTAMP(0) - CAST to a string using...
345424 Jan 2015 @ 04:08 PSTDatabaseReplyNeed help in tuning a QueryHi Lalitha, if you sum up 5 billion rows you will always need a lot spool space :-) Estimated times are just estimates, actual run time might be totall different. Wall clock times can't be c...

Pages