4403 | 21 Apr 2016 @ 12:15 PDT | Database | Reply | Need help in writing a SQL to extract a code from the column which is a varying URL | There's a '?' instead of a '&', you might simply remove the first character from the regex:
REGEXP_SUBSTR(col, '(?<=source=)\d*',1,1,'i')
|
4402 | 21 Apr 2016 @ 11:31 PDT | Database | Reply | Need help in writing a SQL to extract a code from the column which is a varying URL | Use a regular expression, look for the string '&source=' and return the following digits:
REGEXP_SUBSTR(col, '(?<=&source=)\d*',1,1,'i')
|
4401 | 21 Apr 2016 @ 10:27 PDT | Database | Reply | Why 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)... |
4400 | 21 Apr 2016 @ 07:59 PDT | Database | Reply | Fetching maximum value from a VarChar Field | Hi 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...
|
4399 | 21 Apr 2016 @ 07:45 PDT | Database | Reply | OLAP function: CSUM / SUM with Reset value | You 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... |
4398 | 21 Apr 2016 @ 07:29 PDT | Database | Reply | Teradata fallback fastload error tables though the default protectiontype of the database is N | Hi Karthik,
afaik you can't change that.
But why is this affecting your jobs?
|
4397 | 21 Apr 2016 @ 07:23 PDT | Database | Reply | Multiple with clause | Hi Vivek,
can you show your actual SQL?
|
4396 | 20 Apr 2016 @ 01:43 PDT | Database | Reply | Multiple with clause | Well, 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 ... |
4395 | 20 Apr 2016 @ 09:48 PDT | Database | Reply | Fetching maximum value from a VarChar Field | Hi 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... |
4394 | 20 Apr 2016 @ 07:38 PDT | Database | Reply | Converting 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
|
4393 | 20 Apr 2016 @ 05:30 PDT | Analytics | Reply | Stored procedure conversion oracle to TD | You posted the code already, but what is this code doing?
Can you add some actual data and what's returned?
|
4392 | 20 Apr 2016 @ 05:29 PDT | Database | Reply | Converting 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... |
4391 | 19 Apr 2016 @ 10:20 PDT | Analytics | Reply | Stored procedure conversion oracle to TD | Oracle'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... |
4390 | 19 Apr 2016 @ 10:15 PDT | Database | Reply | How to do partitioning on a ID columns | Try CAST(decimal_col AS INT)
|
4389 | 19 Apr 2016 @ 09:54 PDT | Database | Reply | Help with Recursive query | Finding 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
... |
4388 | 18 Apr 2016 @ 07:09 PDT | Database | Reply | Help with Recursive query | Start 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... |
4387 | 17 Apr 2016 @ 11:34 PDT | Database | Reply | Integer to Timestamp | You can also utilize TO_TIMESTAMP(1460961231)
|
4386 | 17 Apr 2016 @ 11:31 PDT | Database | Reply | Selecting duplicate records from a table - Complete record and not just the duplicate keys | There 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...
|
4385 | 17 Apr 2016 @ 11:28 PDT | Database | Reply | Date operation in Teradata | The 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... |
4384 | 17 Apr 2016 @ 11:18 PDT | Teradata Applications | Reply | How to replicate the values | There 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... |
4383 | 14 Apr 2016 @ 01:49 PDT | General | Reply | How to Find Partition Range Already Defined | Hi 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... |
4382 | 14 Apr 2016 @ 01:44 PDT | General | Reply | Criteria to select Multivalue compression | Compression 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 ... |
4381 | 14 Apr 2016 @ 01:40 PDT | Database | Reply | COUNT - Numeric overflow ocurred during computation | Hi 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... |
4380 | 14 Apr 2016 @ 01:35 PDT | General | Reply | How to sum up the columns total value in the last row | You 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 ... |
4379 | 13 Apr 2016 @ 05:23 PDT | Database | Reply | Current Perm Vs Skew Factor | What'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
|