3053 | 25 Aug 2014 @ 08:51 PDT | Database | Reply | Rows to Column (Column-delimited) | You will get this if you use ROW_NUMBER +1 instead of ROW_NUMBER -1
|
3052 | 25 Aug 2014 @ 07:28 PDT | Database | Reply | Rows to Column (Column-delimited) |
Create a row number and then use some MOD/DIV:
SELECT Account#,
COALESCE( TRIM(MIN(CASE WHEN rn MOD 3 = 0 THEN amount END)), '') ||
COALESCE(',' || TRIM(MIN(CASE WH... |
3051 | 25 Aug 2014 @ 06:50 PDT | Database | Reply | Moving Differnces without using MDIFF function | You don't sort by MTD_AMT, you must sort by your logical sort order (which must be unique), using ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING you get the previous row's value.
Based on y... |
3050 | 22 Aug 2014 @ 11:47 PDT | Database | Reply | ROLLING 12 MONTHS | It would have been easier if you added your definition of "rolling 12 months" in the initial post.
WHERE calendar_date BETWEEN ADD_MONTHS(CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE)... |
3049 | 22 Aug 2014 @ 11:43 PDT | Database | Reply | Moving Differnces without using MDIFF function | According to the manuals:
MDIFF(x, w, y) is equivalent to:
x - SUM(x) OVER (ORDER BY y
ROWS BETWEEN w PRECEDING AND w PRECEDING)
In your case this should work:
MTD_AMT - COALESCE(... |
3048 | 22 Aug 2014 @ 05:05 PDT | General | Reply | TeraJDBC driver not supporting BIGINT Datatype | Download a recent version of the JDBC driver.
http://downloads.teradata.com/download/connectivity/jdbc-driver
|
3047 | 21 Aug 2014 @ 01:03 PDT | Connectivity | Reply | Question about parameter markers and .net | You might switch to MERGE instead of INSERT:
MERGE INTO mytable AS tgt USING VALUES (?,?,?) AS src (Id, FirstName, LastName)
ON src.Id = tgt.Id
WHEN NOT MATCHED THEN
INSERT VALUES (src.Id, ... |
3046 | 21 Aug 2014 @ 08:21 PDT | Database | Reply | This SELECT Failed 2616. ERROR is puzzling! | When you SUM(1.00) the result should be a DECIMAL. So what do you mean by "just integers"?
You were talking about percentage, did you eventually multiply the result * 100?
Then si... |
3045 | 21 Aug 2014 @ 02:00 PDT | Database | Reply | TIMESTAMP(6) WITH TIME ZONE, space between seconds and timezone, need to remove it | No, you can simply use the typecast, but you must do it the correct way ;-)
When you write
CAST('1985-09-12 13:20:53.64 +03:00' AS TIMESTAMP(2) WITH TIME ZONE) (FORMAT 'YYYY-MM-DDBH... |
3044 | 20 Aug 2014 @ 02:40 PDT | Database | Reply | TIMESTAMP(6) WITH TIME ZONE, space between seconds and timezone, need to remove it | If you're on TD14 you might simply use one of the new built-in Oracle functions
TO_TIMESTAMP_TZ( '2007-01-31 09:26:56.660000 +02:00')
Btw, your SUBSTRING ... will fail if yo... |
3043 | 20 Aug 2014 @ 01:53 PDT | Database | Reply | This SELECT Failed 2616. ERROR is puzzling! | Are you sure it's not caused by the DASHBOARD_REPORT?
This should work as-is, SUM(1) results in an INTEGER, you just might simplify it a bit removing the CASTs as 1.00 results in a D... |
3042 | 20 Aug 2014 @ 01:30 PDT | Private Forum |
3041 | 20 Aug 2014 @ 01:26 PDT | Database | Reply | Insert-Select Operation | Hi Prasad,
there's an easy answer for that, it depends :-)
If the target table is empty this will probably be processed using a so-called FastPath Insert/Select, i.e. there's no need to j... |
3040 | 20 Aug 2014 @ 01:20 PDT | Database | Reply | Datediff to date | Teradata is not Oracle :-)
There's no reason to use TO_DATE to get a date, better use DATE '2014-07-01' instead, it's shorter and there's only one possble format YYYY-MM-DD.
&... |
3039 | 19 Aug 2014 @ 02:51 PDT | Private Forum |
3038 | 19 Aug 2014 @ 09:39 PDT | Database | Reply | To find number of null values in each columns of a table |
SEL 'col1', COUNT(*)-count(col1) from table_name
union all
SEL 'col2', COUNT(*)-count(col2) from table_name
union all
SEL 'col3', COUNT(*)-count(col3) from table_name
... |
3037 | 19 Aug 2014 @ 07:13 PDT | Connectivity | Reply | Stored Procedure | Hi Emil,
did you read the Stored Procdures manual? There are multiple example SPs in there.
E.g. for TD14: http://www.info.teradata.com/edownload.cfm?itemid=113480020
And if you're usin... |
3036 | 19 Aug 2014 @ 06:15 PDT | Connectivity | Reply | Stored Procedure | Hi Emil,
there's no difference in creating a table in pure SQL or in a SP, you must follow the same rules.
I don't see any reason why you shouldn't do that without SP using a simple:
... |
3035 | 19 Aug 2014 @ 02:30 PDT | Database | Reply | COLLECT STATISTICS |
GRANT STATISTICS ON database/table TO user/role;
|
3034 | 18 Aug 2014 @ 06:36 PDT | Database | Reply | How do I retrieve the most recent used autonumber?? | In ODBC/JDBC/CLI/.NET support a feature called "Auto-generated key retrieval" where the new identity value is returned as an answer set to the client, e.g.
http://www.info.teradata.com/H... |
3033 | 18 Aug 2014 @ 03:48 PDT | Analytics | Reply | Total records from a Case when substring statement | This query will return a syntax error as you can't COUNT(multiple columns).
Based on your narration I don't understand what you want. but it might be
RULE1+RULE2+RULE3+RUL4 AS TOTAL
Rega... |
3032 | 17 Aug 2014 @ 08:13 PDT | General | Reply | Query performance issue |
Question)You said 'I did not change anything', well, I would call changing the PI a big thing :( :
Answer: I did choose a good PI based on join access freq from DBQL logs.
If ... |
3031 | 15 Aug 2014 @ 10:57 PDT | Database | Reply | Error: LOBs are not allowed to be hashed |
The maximum size for a VarChar is 64000 for Latin or 32000 for Unicode character set, so this is not causing a problem. DO a SHOW TABLE to check it's definiton.
This is from t... |
3030 | 15 Aug 2014 @ 09:08 PDT | Aster | Reply | Random number function? | When you're looking for some functionality you should check the manuals, in that case the SQL Functions and Operators :-)
There's a RANDOM(minval,maxval) function which returns a random in... |
3029 | 15 Aug 2014 @ 06:35 PDT | Tools | Reply | TPT export timeout issue | Hi Ravikumar,
there's no timeout on Teradata side, only for logons, otherwise it's always a restriction on client side.
But this might be due to a TASM rule automatically aborting pr... |