#DateForumTypeThreadPost
360322 Mar 2015 @ 01:43 PDTGeneralReplyDiffrent values displayed for Timestamp (6) column on diffrent queriesThe first timestamp will not be generated by Teradata (only two fractional digits). Is this always shifting the fractional seconds one digit?   What client do you use? Before you open an i...
360222 Mar 2015 @ 01:32 PDTUDAReplyUsing qualify and group by togetherWell, this is the same as accessing a view with different Selects and everyone is doing that all the time. The optimizer will do his best to eliminate unused columns.
360122 Mar 2015 @ 01:25 PDTDatabaseReplyTranspose from columns to rowsYou need nested aggregrations, e.g. select count(case when typestring = 'FE' then 1 end) as count_FTET ,count(case when typestring = 'F' then 1 end) as count_FT ,count...
360017 Mar 2015 @ 03:35 PDTGeneralReplyMonth end data from a daily tableThis might be what you want, if there are no overlapping ranges: SELECT ... CASE -- already last day of month WHEN Validity_end_date = LAST_DAY(Validity_end_date) THEN Validity_e...
359917 Mar 2015 @ 03:04 PDTGeneralReplyNEED HELP !! - Merging Consecutive and OVERLAPPING date spansHard to tell with two example rows :) SELECT id, count, StartDate, COALESCE(MIN(StartDate) OVER (PARTITION BY id ORDER BY StartDate, EndDate ...
359817 Mar 2015 @ 02:54 PDTDatabaseReplyREGEXP_SIMILAR SyntaxThis should work as-is. Check what's returned by  select * from dbc.FunctionsV where FunctionName = 'REGEXP_SIMILAR' Should be two functions in td_sysfnlib with 2 and 3 parame...
359717 Mar 2015 @ 02:51 PDTDatabaseReplyOn Commit Preserve Rows taking much time It's always the same reason: The table is created as a SET table plus a bad Primary Index (usually no PI defined, so defaulting to first column)
359617 Mar 2015 @ 02:49 PDTGeneralReplyReverse engineer rules from dbqlruletbl rowsHi Abhishek, I wrote this to get the DBQL commands from DBQLRuleTbl in TD14.10/15, will need some modification for earlier releases: SELECT 'BEGIN QUERY LOGGING' || CASE WHEN Wi...
359517 Mar 2015 @ 10:56 PDTDatabaseReplyNon duplicate row selection queryThere's no way to avoid the GROUP/PARTITION BY all columns. Clean your data once and next time simply don't insert duplicates, your load process should take care of this.   There sh...
359417 Mar 2015 @ 10:51 PDTDatabaseReplyUsing Dynamic databasename and tablename in stored procedureMove the Handler to a nested BEGIN/END: REPLACE PROCEDURE MYSP ( IN SRC_DB_NM VARCHAR(30) , IN SRC_TBL_NM VARCHAR(30) , OUT MESSAGE VARCHAR(200) ) BEGIN DECLARE QUERY ...
359317 Mar 2015 @ 10:32 PDTDatabaseReplyTeradata decimal calculation behaviourThis is documened behaviour, Teradata rounds after every step when DECIMALs are involved. So the basic rule is "first multiply, then divide".   Or you cast the first operand to a ...
359216 Mar 2015 @ 04:01 PDTDatabaseReplyQuery Performance IssueHi Asish, did you check DBQL for estimated vs. actual number of rows? The optimizer's choice picking the index on transaction_country is bad.  There's a bad condition, comparing str...
359116 Mar 2015 @ 03:23 PDTDatabaseReplyQueryFIRST_VALUE cam be replaced by nested OLAP-functions: SELECT name, sum, MIN(min_sum) OVER () - sum FROM ( SELECT name, sum, CASE WHEN ROW_NUMBER() OVER (ORDER BY name) = 1 THEN...
359016 Mar 2015 @ 03:12 PDTDatabaseReplyOrdered Analytical Function/Iterative LoopWhat's your TD release? In TD14 you could use FIRST_VALUE/LAST_VALUE: SELECT party_id, fisc_week_val, lifecycle_stage, COALESCE(LAST_VALUE(lifecycle_stage IGNORE NULLS) OV...
358916 Mar 2015 @ 10:41 PDTDatabaseReplyStored procedure for passing date valuesHi Mahesh, why is using a SP more standard than using a table? If you use this the optimizer can do Incremental Planning in TD14.10: AND A.DLVR_DT BETWEEN CURRENT_DATE – (SEL MIN...
358816 Mar 2015 @ 10:33 PDTDatabaseReplyCompressValueList add blank spaces in DBC.Columns fieldFIELD_CMP is defined as CHAR(30), of course values are padded with blanks.
358716 Mar 2015 @ 10:30 PDTDatabaseReplyBest criteria for selecting NUPIHi John, if SEQID is not used it's useless as PI column, in fact you don't need it at all. What's the actual Primary Key of this table?  
358616 Mar 2015 @ 10:18 PDTAnalyticsReplyFailed [5484 : HY000]No ordered analytical function allowed in search condition for a joined tableOLAP-functions are calculated after where/join/group by/having, so you have move it to a Derifed Table: SEL FILLD_DT FROM CLM_TBL C INNER JOIN ( Sel EFF_DT, Max (EFF_DT) ov...
358516 Mar 2015 @ 10:10 PDTAnalyticsReplyHelp with lag/lead function in Teradata Hi Teresa, you can't use a column number in the ORDER or PARTITION BY of an OLAP-function.  Simply change it to ORDER BY Activity_date
358416 Mar 2015 @ 12:46 PDTDatabaseReplyStored procedure for passing date valuesHi Mahesh, you can't use an SP in a SELECT. Why do you actually need it? Your example can easily be rewritten without it. 
358316 Mar 2015 @ 12:40 PDTDatabaseReplysliding window merge joinSliding Window Join is applied only to partitioned tables, without partitioning there's no need for it. 
358216 Mar 2015 @ 12:39 PDTDatabaseReplyBest criteria for selecting NUPIHi John, this is not bad unless there are too many rows per PI value. Duplicate row checks will be done only for roews with the same PI (in fact, the same Row Hash).   If this sequence colu...
358116 Mar 2015 @ 12:35 PDTDatabaseReplyChoosing Primary Index for a tableQ1/Q2/Q3: yes, of course Q4: no, of course, there's only one PI per table (think of it as a kind of clustered index) Q5: columns used for joins (in best case also used in Where) if the distri...
358012 Mar 2015 @ 10:53 PDTDatabaseReplyLooking for TD 15 Database Installation on local systemHi Paras, Teradata Studio is only a client, you need a Teradata server. You can download Teradata Express and run it on VMWare Player: VMware
357912 Mar 2015 @ 09:57 PDTDatabaseReplysubtract- datediff 52 weeks to a date52 weeks are 52*7 days, simply do current_date - 52*7

Pages