#DateForumTypeThreadPost
395329 Aug 2015 @ 08:28 PDTGeneralReplyerror: expecting ON keyword between sgsg_ck and where. please help meSimply remove the parens around the join condition: INNER JOIN ( SELECT SGSG_CK, SGSG_ID FROM ODS_PRD_STG_VW.CMC_SGSG_SUB_GROUP_STG WHERE SGSG_ID ='H001' ) SSG ON MPE.SGSG_C...
395226 Aug 2015 @ 11:21 PDTDatabaseReplyRemove characters after '-' in a stringHi Sakthi, switch to either INSTR instead of position or REGEXP_SUBSTR: SUBSTRING('1-1234-1' FROM 1 FOR instr('1-1234-1''-', '-', 1, 2)-1) -- find the 2nd dash ...
395126 Aug 2015 @ 11:09 PDTDatabaseReplySQL Warning 5815 Function is not ANSICAST is no SQL92 entry level: 2) The following restrictions apply for Entry SQL in addition to any Intermediate SQL restrictions:               a)...
395024 Aug 2015 @ 12:38 PDTDatabaseReplyMerge and update errorYou need to use the logical Primary Key of your target table for matching, i.e. the target row must be unique. Regarding the error during MATCHED, you might have multiple rows for the same PK, in ...
394924 Aug 2015 @ 12:34 PDTGeneralReplyusing a calculation done in the previous row in this row's calculationTo create a new topic: Go to the forum you want to post the question, e.g. General, above the first question there should be a big button CREATE A NEW FORUM TOPIC.   You can't pass...
394824 Aug 2015 @ 07:21 PDTGeneralReplyusing a calculation done in the previous row in this row's calculationPlease post new questions a new topic. You missed some single quotes: OREPLACE('789778,i897990',',',''',''') But I doubt this is what you actually want...
394724 Aug 2015 @ 06:23 PDTDatabaseReplyHow to convert UTC millisecond to Timestamp(6) SELECT CAST(1439374041111 AS BIGINT) AS ut, TO_TIMESTAMP(ut/1000) + ut MOD 1000 * INTERVAL '0.001' SECOND  
394624 Aug 2015 @ 01:35 PDTUDAReply TIMESTAMP in TeraDATAHi Josh, What's the actual data in this column, always a time or a timestamp or something else, too? Why is that column defined as Varchar(1500) then?
394524 Aug 2015 @ 01:34 PDTDatabaseReplyHow to convert UTC millisecond to Timestamp(6)Your example is a Unixtime, but doesn't include milliseconds. to_timestamp(1439374041)  
394423 Aug 2015 @ 09:21 PDTHadoopReplyNeed to get common users for every combination of user IDs SELECT t1.UserId, t2.UserId, COUNT(*) FROM tab AS t1 JOIN tab AS t2 ON t1.UserId < t2.UserId -- different user id AND t1.UserId1 = t2.UserId1 -- same user id 1 GROUP BY 1,2  
394323 Aug 2015 @ 04:49 PDTPrivate Forum
394223 Aug 2015 @ 04:37 PDTDatabaseReplySubtracting Timestamps as decimals hoursThis is a generic SQL UDF I wrote a few years ago for calculating the difference of two timestamps in seconds: REPLACE FUNCTION TimeStamp_Diff_Seconds ( ts1 TIMESTAMP(6) ,ts2 TIMESTAMP(6...
394121 Aug 2015 @ 04:06 PDTToolsReplyFastload Script without Drop and Create tableYou can DELETE the target table if the previous load was successful. Otherwise it will return a 2652, table being loaded.   If the error tables still exist, the previous load didn't fin...
394021 Aug 2015 @ 03:55 PDTDatabaseReplyFind which logon id locked the userHi John, SELECT * FROM dbc.LogonOffV WHERE Event = 'Bad Password' AND USERNAME = 'READQ' ORDER BY LogDate DESC, LogTime DESC; Depending on your Teradata release you might l...
393920 Aug 2015 @ 09:48 PDTAnalyticsReplyAnalytical Output - groupingWhat's your Teradata release? TD14.10 supports a new SELECT option: SELECT f1,f2,BEGIN(pd),LAST(pd) FROM ( SELECT NORMALIZE f1, f2, PERIOD(f3, f3+1) AS pd FROM dropme ...
393818 Aug 2015 @ 09:51 PDTGeneralReplyBTEQ Import Data file errorThere will be automatic type casts if source and target data types don't match. If the automatic cast fails you must add a manual cast.   Input columns must be defined as Varchars if the...
393718 Aug 2015 @ 09:47 PDTDatabaseReplySQL to get first day of month and last day of month but for 2 months agoTRUNC is an Oracle-function which strips of the time part of a Timestamp, the same as CAST(col AS DATE) in Teradata. The optional parameter 'MON' truncates to the first of month, ther...
393618 Aug 2015 @ 05:35 PDTDatabaseReplyIs it possible to convert query written for SQL server to be run on Teradata?You will need more than minor modifications... Datetime calculations usually differ a lot across DBMSes. CROSS APPLY is MS proprietary syntax which doesn't exist in Teradata. But in your case...
393518 Aug 2015 @ 04:41 PDTDatabaseReplyTeradata Non-commercial Database There's a virtual machine: VMware Within the VM there's the Teradata Linux client installed. If you got a Windows laptop you might want to run queries from it, either using Tera...
393413 Aug 2015 @ 02:09 PDTDatabaseReply[Teradata Database] [3932] Only an ET or null statement is legal after a DDL StatementSQL is a language and not a specific product, even if Microsoft tries to brainwash people to use it for SQL Server :-)   #1: if OBJECT_ID('temp db.#temp_tick') is not null DROP T...
393312 Aug 2015 @ 02:09 PDTDatabaseReplyIssue in Substr and Index with CASEHi Nik, you should open an incident with Teradata support for this issue.
393212 Aug 2015 @ 02:04 PDTDatabaseReplyInsert- Select data from UPI to NOPI tableThe rows will be insertd as-is, i.e. no additional sort.
393112 Aug 2015 @ 02:03 PDTDatabaseReplyTricky stats collection As you're limited to 32 multicolumn stats you might try if single column stats might work, too.
393012 Aug 2015 @ 02:02 PDTGeneralReplyGet Minimum Price from Overlapping valid dates Have a look at forums.teradata.com/forum/database/overlapping-time-periods-competing-records-by-date-created Your task might be similar...
392912 Aug 2015 @ 10:31 PDTDatabaseReplyIssue in Substr and Index with CASEHi Nik, //ABC.com/arti is really strange, seems like it didn't find 'artist' and then started at 0 + 6, but returned only 14 instead of 16 characters.   SELECT 'http://AB...

Pages