#DateForumTypeThreadPost
270323 Apr 2014 @ 02:40 PDTToolsReplyHow to change a date column to show only month and yearOf 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.
270223 Apr 2014 @ 02:37 PDTDatabaseReplyCumulative Sum by distinct customer ID by yearHi 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. ...
270122 Apr 2014 @ 03:19 PDTDatabaseReply2620: The format or data contains a bad characterCCT_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,...
270022 Apr 2014 @ 10:45 PDTDatabaseReplyFinding the latest dateI 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...
269922 Apr 2014 @ 10:31 PDTGeneralReplyHelp with Analytic FunctionHi 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...
269820 Apr 2014 @ 02:23 PDTGeneralReplyCompare current row with previous row in TEradataThis should translate to: CASE WHEN PK_COLUMN = MIN(PK_COLUMN) OVER (ORDER BY whatever ROWS BETWEEN 1 PRECEDING END 1 PRECEDING) THEN 'N' ...
269720 Apr 2014 @ 02:18 PDTDatabaseReplyTo 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 ...
269619 Apr 2014 @ 10:46 PDTDatabaseReplyRecursive orderingHi 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 ...  
269515 Apr 2014 @ 06:27 PDTDatabaseReplyConvert integer to TIMEHow is the time stored within the INT?  hhmissssssss?
269415 Apr 2014 @ 06:24 PDTAnalyticsReplyREGEXP_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....
269314 Apr 2014 @ 08:29 PDTAnalyticsReplyREGEXP_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)...
269209 Apr 2014 @ 01:25 PDTDatabaseReplyQUALIFY <cond_1> AND <cond_2> filtered out valid dataHi 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...
269109 Apr 2014 @ 11:58 PDTDatabaseReplyHelp needed in Bteq ExportSorry, 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...
269009 Apr 2014 @ 10:32 PDTDatabaseReplyHelp needed in Bteq Export.export REPORT file = /prod/user/sam/us/bank/bankma/non_npi/ghp501/iFRE_Testing/Bteq/Payment_file.txt
268909 Apr 2014 @ 09:03 PDTDatabaseReplyRowID issue with teradataIMHO 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_...
268809 Apr 2014 @ 08:54 PDTDatabaseReplyHelp needed in Bteq ExportYou export in DATA mode, which includes a two-byte record length. Why don't you use REPORT mode? 
268709 Apr 2014 @ 07:36 PDTDatabaseReplyNumber 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.
268609 Apr 2014 @ 07:32 PDTDatabaseReplyTeradata Manager - No ProfilesHi 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...
268509 Apr 2014 @ 07:28 PDTDatabaseReplyHOw to truncate a date from Time stamp to use in Business ObjectsHi 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)
268407 Apr 2014 @ 09:47 PDTDatabaseReplytable names with a period in themHow 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...
268307 Apr 2014 @ 09:44 PDTDatabaseReplyIS NOT IN SyntaxThis 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 :-)
268207 Apr 2014 @ 09:42 PDTDatabaseReplyusage of ^ in logical conditions"^" is a logical NOT, a leftover of Teradata's query language before SQL, Tequel.  
268103 Apr 2014 @ 02:31 PDTDatabaseReplyCan 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 ...
268003 Apr 2014 @ 10:44 PDTDatabaseReplyMERGE INTO and CONCATENATIONOf course MERGE supports concat. There must be something else, what's the datatype of those col2?
267902 Apr 2014 @ 03:12 PDTDatabaseReplyCan 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...

Pages