#DateForumTypeThreadPost
440321 Apr 2016 @ 12:15 PDTDatabaseReplyNeed help in writing a SQL to extract a code from the column which is a varying URLThere's a '?' instead of a '&', you might simply remove the first character from the regex: REGEXP_SUBSTR(col, '(?<=source=)\d*',1,1,'i')  
440221 Apr 2016 @ 11:31 PDTDatabaseReplyNeed help in writing a SQL to extract a code from the column which is a varying URLUse a regular expression, look for the string '&source=' and return the following digits: REGEXP_SUBSTR(col, '(?<=&source=)\d*',1,1,'i')  
440121 Apr 2016 @ 10:27 PDTDatabaseReplyWhy are these two dates not equal?The 2nd date is 3915-11-30, you forgot to subtract 1900 from the year: CAST ((EXTRACT(YEAR FROM ADD_MONTHS(snap_dt,1))-1900)*10000+EXTRACT(MONTH FROM ADD_MONTHS(snap_dt,1))*100 + 1 AS DATE)...
440021 Apr 2016 @ 07:59 PDTDatabaseReplyFetching maximum value from a VarChar FieldHi Kathir, of course the result might change for different collations, you must check which collation is used in DB2 and if there's an equivalent in Teradata...
439921 Apr 2016 @ 07:45 PDTDatabaseReplyOLAP function: CSUM / SUM with Reset valueYou can't use this approach when you need multiple IDs, the only workaround is TIME_DAY_LKP CROSS JOIN (SELECT DISTINCT ID FROM POINT_TBLE) to create all possible combinations of da...
439821 Apr 2016 @ 07:29 PDTDatabaseReplyTeradata fallback fastload error tables though the default protectiontype of the database is NHi Karthik, afaik you can't change that. But why is this affecting your jobs?
439721 Apr 2016 @ 07:23 PDTDatabaseReplyMultiple with clauseHi Vivek, can you show your actual SQL?
439620 Apr 2016 @ 01:43 PDTDatabaseReplyMultiple with clauseWell, multiple WITH are not implemented correctly in Teradata, you must define them in the wrong order: WITH CTE2 AS ( select CTE1.c1 from table_name) , CTE1 AS (SELECT ...
439520 Apr 2016 @ 09:48 PDTDatabaseReplyFetching maximum value from a VarChar FieldHi Kathir, this is based on the sort order, see: SET SESSION COLLATION ASCII; SEL MAX(NAME), MIN(NAME) FROM tab1; Teradata Forum Teradata FORUM SET SESSION COLLATION ebcdic; SEL MAX...
439420 Apr 2016 @ 07:38 PDTDatabaseReplyConverting from byte to char generate minus sign SELECT 'FFFF4D7CD5E8A2AA0000'xb AS GenId ,LPAD(FROM_BYTES('00'xb||GenId, 'BASE16'), 20) returns FF-FF-4D-7C-D5-E8-A2-AA-00-00 FFFF4D7CD5E8A2AA0000  
439320 Apr 2016 @ 05:30 PDTAnalyticsReplyStored procedure conversion oracle to TDYou posted the code already, but what is this code doing? Can you add some actual data and what's returned?
439220 Apr 2016 @ 05:29 PDTDatabaseReplyConverting from byte to char generate minus sign@denilsson10: You need to add a leading binary zero to GenId:  FROM_BYTES('00'xb||GenId, 'BASE16') And to get a fixed size with leading zeroes you better use LPAD: LPA...
439119 Apr 2016 @ 10:20 PDTAnalyticsReplyStored procedure conversion oracle to TDOracle's CONNECT BY is proprietary syntax, usually rewritten using WITH RECURSIVE, but I don't know if you can do that in a cursor. Looks like splitting data into multiple rows, there...
439019 Apr 2016 @ 10:15 PDTDatabaseReplyHow to do partitioning on a ID columnsTry CAST(decimal_col AS INT)
438919 Apr 2016 @ 09:54 PDTDatabaseReplyHelp with Recursive queryFinding the last row of a group is simple, no need for NOT EXISTS, it's WHERE col1 = col2. WITH RECURSIVE cte (col1, col2, last_col2) AS ( SELECT col1, col2, col2 AS last_col2 ...
438818 Apr 2016 @ 07:09 PDTDatabaseReplyHelp with Recursive queryStart with the last record per group: SELECT Col1, Col2, Col2 AS lastCol2 FROM tab AS t1 WHERE NOT EXISTS ( SELECT * tab AS t2 WHERE t1.Col2 = t2.Col1 ) and then go up the hie...
438717 Apr 2016 @ 11:34 PDTDatabaseReplyInteger to TimestampYou can also utilize TO_TIMESTAMP(1460961231)
438617 Apr 2016 @ 11:31 PDTDatabaseReplySelecting duplicate records from a table - Complete record and not just the duplicate keysThere are no yellow marked records, you must add the rows using the code editor, there you can highlight.   It's either based on COUNT OVER or maybe a simple ROW_NUMBER...
438517 Apr 2016 @ 11:28 PDTDatabaseReplyDate operation in TeradataThe FORMAT of a DATE is just for display, you can simply compare them. But you data show a DATE and a TIMESTAMP, so you must add a typecast: WHERE DATE1 > CAST(DATE2 AS DATE) AND DATE1 &l...
438417 Apr 2016 @ 11:18 PDTTeradata ApplicationsReplyHow to replicate the valuesThere are several ways to get this result:   #1: classical SQL would be a JOIN to a number-table with sequential values ON n BETWEEN 1 AND Repeat, depending on the actula data might ne...
438314 Apr 2016 @ 01:49 PDTGeneralReplyHow to Find Partition Range Already DefinedHi Ahmad, you could use REGEXP_SUBSTR or INSTR to extract the last date found in PartitioningConstraintsV.   Regarding Q2 & Q3, there's ADD RANGE and ranges can't overlap, but t...
438214 Apr 2016 @ 01:44 PDTGeneralReplyCriteria to select Multivalue compressionCompression will also result in quicker access, but mainly because the table is smaller and thus scanned faster, e.g. 30% less space results in 30% faster Full Table Scans. But partitioning might ...
438114 Apr 2016 @ 01:40 PDTDatabaseReplyCOUNT - Numeric overflow ocurred during computationHi Sergio, this is independant of the column's data type, by default COUNT returns an INTEGER in a Teradata mode session (in ANSI mode it's a DECIMAL with at least 15 digits). You must us...
438014 Apr 2016 @ 01:35 PDTGeneralReplyHow to sum up the columns total value in the last rowYou can do it using a seperate SELECT, but you should use "UNION ALL". Of course, if you already do any aggregation it will be more efficient to use "GROUPING SETS" Why do you ...
437913 Apr 2016 @ 05:23 PDTDatabaseReplyCurrent Perm Vs Skew FactorWhat's the number of rows and the number of AMPs in your system? Nobody cares about skew for a small table like this...   Check if the 2nd table is defined with FALLBACK

Pages