2703 | 23 Apr 2014 @ 02:40 PDT | Tools | Reply | How to change a date column to show only month and year | Of course you could simly change the FORMAT to 'yyyymm', but why do you cast to a char instead of using native DATEs?
DATE - (EXTRACT(DAY FROM DATE)-1) returns the first day of a month.
|
2702 | 23 Apr 2014 @ 02:37 PDT | Database | Reply | Cumulative Sum by distinct customer ID by year | Hi Carey,
I've done a similar query, new vs. existing customers for every period :-)
There's no way to do it with a simple SUM OVER as you can't use DISTINCT within an OLAP function.
... |
2701 | 22 Apr 2014 @ 03:19 PDT | Database | Reply | 2620: The format or data contains a bad character | CCT_External_ID between '0' and '999999' might still return non-numeric data which fails to convert to an int.
emp_id > '0' in a WHERE will not change the datatype,... |
2700 | 22 Apr 2014 @ 10:45 PDT | Database | Reply | Finding the latest date | I don't fully understand what you want, but it seems like you might utilize OLAP functions.
QUALIFY RANK() OVER (PARTITION BY ??? ORDER BY GRIP_DT DESC) = 1
or
QUALIFY GRIP_DT = MAX(GR... |
2699 | 22 Apr 2014 @ 10:31 PDT | General | Reply | Help with Analytic Function | Hi Sai,
you can't get this in a single pass, you need to use a Derived Table:
SELECT
dt.*,
SUM(A)
OVER (PARTITION BY AYEAR,ASEASON,AUNIT,ASUPPLIER, AMONTH, AWEEK) AS SumB... |
2698 | 20 Apr 2014 @ 02:23 PDT | General | Reply | Compare current row with previous row in TEradata | This should translate to:
CASE
WHEN PK_COLUMN
= MIN(PK_COLUMN)
OVER (ORDER BY whatever
ROWS BETWEEN 1 PRECEDING END 1 PRECEDING)
THEN 'N'
... |
2697 | 20 Apr 2014 @ 02:18 PDT | Database | Reply | To compare data in different columns for different rows of records: | Hi Vaish,
SELECT COUNT(*)
FROM
(
SELECT *
FROM tab
QUALIFY
ADMT_DT
- MIN(DSCHRG_DT)
OVER (PARTITION BY MBR_ID
ORDER BY ADMT_DT, DSCHRG_DT
... |
2696 | 19 Apr 2014 @ 10:46 PDT | Database | Reply | Recursive ordering | Hi Zsolt,
seems you only need to switch C1 and C2 :-)
...
UNION ALL
SELECT C1.content_type, trim(C2.product_offered) || ',' || trim(C1.product_offered), lvl+1
...
|
2695 | 15 Apr 2014 @ 06:27 PDT | Database | Reply | Convert integer to TIME | How is the time stored within the INT?
hhmissssssss?
|
2694 | 15 Apr 2014 @ 06:24 PDT | Analytics | Reply | REGEXP_SPLIT_TO_TABLE doesn't work | Hi Raja,
i don't know about 14.10, but in 14 the initial one was too close to 00.00 and quite buggy version :)
There's 14.10.01.01 available which is approx. 5 month later than the 14.10.... |
2693 | 14 Apr 2014 @ 08:29 PDT | Analytics | Reply | REGEXP_SPLIT_TO_TABLE doesn't work | Of course this works, the 1st query will be:
SELECT *
FROM
TABLE (REGEXP_SPLIT_TO_TABLE(' bnp,&abc&,dbc,@dbc@,NULL', ',', 'i')
RETURNS (res VARCHAR(100)... |
2692 | 09 Apr 2014 @ 01:25 PDT | Database | Reply | QUALIFY <cond_1> AND <cond_2> filtered out valid data | Hi Deepak,
move both QUALIFY functions to the SELECT and you can see what's wrong :-)
Without a unique sort column ROW_NUMBER is not deterministic.
It seems like you only need a single ROW_N... |
2691 | 09 Apr 2014 @ 11:58 PDT | Database | Reply | Help needed in Bteq Export | Sorry, i didn't read the first posts in detail :(
REPORT mode never writes trailing blanks (it was ment to be sent directly to a printer).
The only way to prevent that is to right-align the l... |
2690 | 09 Apr 2014 @ 10:32 PDT | Database | Reply | Help needed in Bteq Export | .export REPORT file = /prod/user/sam/us/bank/bankma/non_npi/ghp501/iFRE_Testing/Bteq/Payment_file.txt
|
2689 | 09 Apr 2014 @ 09:03 PDT | Database | Reply | RowID issue with teradata | IMHO you don't need ROWID at all, this query seems to be just a overly complex way to write (assuming the combination ENTITY_KEY, OR_UPDATE_DATE is unique):
UPDATE operations.entity_... |
2688 | 09 Apr 2014 @ 08:54 PDT | Database | Reply | Help needed in Bteq Export | You export in DATA mode, which includes a two-byte record length.
Why don't you use REPORT mode?
|
2687 | 09 Apr 2014 @ 07:36 PDT | Database | Reply | Number of AMPs Needed?? | Hi Stanley,
this is a bit confusing question and the short answer is: no.
There's no direct correlation between sessions, requests and AMP worker tasks.
|
2686 | 09 Apr 2014 @ 07:32 PDT | Database | Reply | Teradata Manager - No Profiles | Hi Pawan,
what's your system, a TD Express version?
Then you have to use the correct ip-address of your VMWare image (unless you're runnning a very old windows demo TD).
You can get it w... |
2685 | 09 Apr 2014 @ 07:28 PDT | Database | Reply | HOw to truncate a date from Time stamp to use in Business Objects | Hi Sara,
when you CAST a timestamp to a DATE the time part is truncated:
CAST(DLGSC_MIRS.PERMIT_HISTORY_LEVEL_1_VW.START_DT AS DATE)
|
2684 | 07 Apr 2014 @ 09:47 PDT | Database | Reply | table names with a period in them | How did you create that table with a period in it :-)
Special names must be in double quotes, DROP TABLE "blabla";
Regarding the maximum length of 30 chars: there's no worka... |
2683 | 07 Apr 2014 @ 09:44 PDT | Database | Reply | IS NOT IN Syntax | This is probably also a leftover of Teradata's pre-SQL query language, Tequel.
Syntatically correct, but proprietary to Teradata and deprecated since at least 20 years :-)
|
2682 | 07 Apr 2014 @ 09:42 PDT | Database | Reply | usage of ^ in logical conditions | "^" is a logical NOT, a leftover of Teradata's query language before SQL, Tequel.
|
2681 | 03 Apr 2014 @ 02:31 PDT | Database | Reply | Can Decimal and Integer be a mismatch for Join? | According to Standard SQL the shorter string is padded with trailing blanks to match the length of the longer string, which is similar to "ignore trailing blanks".
But this is only done ... |
2680 | 03 Apr 2014 @ 10:44 PDT | Database | Reply | MERGE INTO and CONCATENATION | Of course MERGE supports concat.
There must be something else, what's the datatype of those col2?
|
2679 | 02 Apr 2014 @ 03:12 PDT | Database | Reply | Can Decimal and Integer be a mismatch for Join? | Hi Dominiq,
1: If the decimal has no fractional digits (i.e. DEC(n,0) it's the same as an INT.
DEC(10,0) = DEC(20,0) = INT = BIGINT
2: If the number of fractional digits is the same both dec... |