3428 | 14 Jan 2015 @ 04:32 PST | Database | Reply | Delete statement consuming more resource | Hi Karthick,
if you need to be able to rollback the delete there's no other way than both delete and ins/sel in one transaction.
But then you have to pay the price, in worst case the ins/sel ... |
3427 | 14 Jan 2015 @ 03:38 PST | Database | Reply | Delete statement consuming more resource | Hi Karthick,
what about two macros or a Stored Procedure?
|
3426 | 14 Jan 2015 @ 03:08 PST | Database | Reply | Delete statement consuming more resource | Hi Karthick,
the solution is self-evident, use seperate statements/transactions instead of a macro.
|
3425 | 14 Jan 2015 @ 01:38 PST | Database | Reply | Delete statement consuming more resource | Hi Karthick,
assuming the delete is a DELETE ALL followed by an INS/SEL:
DELETE ALL is fast if it's the last statement within a transaction and the optimizer knows it will be commited (n... |
3424 | 13 Jan 2015 @ 11:27 PST | Database | Reply | Help coding on count historical event within a fix time period, greatly thanks! | Hi Vrushang,
based on the description the result should be the number of rows within 60 minutes before the current row's eventtime, i.e. a moving count (not cumulative as I wrote in my first p... |
3423 | 13 Jan 2015 @ 09:52 PST | Database | Reply | RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING | Hi Moh,
for UNBOUNDED there's no difference between RANGE and ROWS :)
Otherwise a possible workaround depends on what you actually need as a result...
E.g. there is another topic&nbs... |
3422 | 13 Jan 2015 @ 09:47 PST | Database | Reply | Help coding on count historical event within a fix time period, greatly thanks! | And for user_1 1 and eventtime 2:40 it should return 3, too?
I don't think there's an easy way to get this result, even a cursor or recursive query will fail.
Some fancy SQL using cross j... |
3421 | 13 Jan 2015 @ 09:35 PST | Analytics | Reply | standard deviation function with parition by | I don't think that the ODBC driver changed the STDDEV_SAMP, but maybe a calculation of a column used within, that's why I was asking for the exact error message and checking DBQL :-)
|
3420 | 13 Jan 2015 @ 06:27 PST | Analytics | Reply | standard deviation function with parition by | What's the exact error message?
Can you extract the actual SQL submitted from DBQL, maybe it was modified by the ODBC driver.
Try if checking "Disable Parsing" in the ODBC datasourc... |
3419 | 13 Jan 2015 @ 02:04 PST | Database | Reply | 2616 error numeric overflow with row_number() | It's a cumulative sum of 1s :-)
But I just noticed that the COUNT was wrong, should also include ROWS:
CAST(COUNT(*) AS BIGINT) OVER (ORDER BY col_name ROWS UNBOUNDED PRECEDING)
&n... |
3418 | 13 Jan 2015 @ 01:57 PST | Database | Reply | Find position of first non-digit or space from the back | Hi Eric,
are there any UDFs at your site or is there any chance you can install one?
|
3417 | 13 Jan 2015 @ 01:54 PST | Database | Reply | ALTER TO CURRENT | I don't know exactly, but this is probably related to CURRENT_DATE being dependent on your session time zone:
BTEQ -- Enter your SQL request or BTEQ command:
SET TIME ZONE -11;
SELECT C... |
3416 | 13 Jan 2015 @ 01:49 PST | Database | Reply | Find position of first non-digit or space from the back | Hi Bhaskar,
it's a substring based on a Regular Expression, TD14 also supports REGEXP_SIMILAR (extended LIKE), REGEXP_INSTR (extended POSITION), REGEXP_REPLACE and REGEXP_SPLIT_TO_TABLE (split... |
3415 | 13 Jan 2015 @ 01:41 PST | Analytics | Reply | Syntax error due to Stored Proc Parameter | Time zones are always hard to deal with :-)
Additionally different systems might return different results based on some internal settings...
The easiest way should be changing the session... |
3414 | 12 Jan 2015 @ 11:28 PST | Database | Reply | 2616 error numeric overflow with row_number() | Seems like the resulting datatype of ROW_NUMBER can't be changed (of course INTEGER is quite stupid), but ROW_NUMBER is just a shortcut for a COUNT:
CAST(COUNT(*) AS BIGINT) OVER (ORDER BY c... |
3413 | 12 Jan 2015 @ 10:13 PST | Database | Reply | Teradata - to solve a data issue with query or Procedure | This is a simple task for recursion:
WITH RECURSIVE cte (Col1, Col2, minCol) AS
(
SELECT Col1, Col2, Col1 AS minCol
FROM tab
WHERE Col1 = Col2
UNION ALL
SELECT ... |
3412 | 12 Jan 2015 @ 07:00 PST | Database | Reply | RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING | RANGE syntax is not implemented in Teradata, you need to user ROWS instead:
FIRST_VALUE (Col_name)
OVER (PARTITION BY ACCOUNT_ID
ORDER BY ACCOUNT_ID , VALID_FROM
ROWS BETWEEN UNBOUNDED PRE... |
3411 | 12 Jan 2015 @ 03:55 PST | Analytics | Reply | standard deviation function with parition by | Of course it's working, what's your exact error message?
"is not a valid Teradata SQL token" indicates some strange character in your source code.
|
3410 | 11 Jan 2015 @ 11:31 PST | Analytics | Reply | Syntax error due to Stored Proc Parameter | This should work, assuming there's no nov. 31 down under und it should december instead :-)
SELECT
TIMESTAMP '2014-12-31 13:34:56+00:00' AS Completed_Dt,
Completed_Dt AT '... |
3409 | 11 Jan 2015 @ 01:53 PST | Database | Reply | Syntax when concatenating string and number | You can't store leading blanks in a numeric column, but every column in Teradata has a default format applied when casted. You you can change it in the CREATE TABLE:
Store INTEGER FORMAT ... |
3408 | 11 Jan 2015 @ 10:50 PST | Database | Reply | Syntax when concatenating string and number | Seems you got a space character between after the first "|", remove it to get "||" instead of "| |"
And instead of directly concatenating Store/ID which ... |
3407 | 10 Jan 2015 @ 07:31 PST | Database | Reply | Change data capture | Hi Larry,
both joining on COALESCE and MINUS will result in redistribution steps, you better switch to NOT EXISTS instead:
SELECT *
FROM t1
WHERE NOT EXISTS
(SELECT * FROM t2
WHERE (t1.co... |
3406 | 10 Jan 2015 @ 04:16 PST | Database | Reply | How to create a loop | This is a simlpe task for Standard SQL's Windowed Aggregate Functions, you need a cumulative sum:
select
WK
,Year
,sum(costA)
over (partition by year
order by wk
... |
3405 | 09 Jan 2015 @ 01:30 PST | Database | Reply | Find position of first non-digit or space from the back | What's your TD release?
Regular expressions can easily extract both fields looking for a group of digits at the end of the string:
field1:
REGEXP_SUBSTR(txt, '.+?(?=[0-9]*$)')
f... |
3404 | 09 Jan 2015 @ 01:04 PST | Analytics | Reply | Syntax error due to Stored Proc Parameter | When you check DBQL you'll find that a closing bracket is removed from the SQL code submitted within the SP, seems to be a strange parser bug.
What are you trying to do wtih all those CASTs?
... |