#DateForumTypeThreadPost
9622 Sep 2014 @ 11:08 PDTDatabaseReplyAccess Right 'G' abbreviationHi Nick2408, the docs have following abbreviations - see Data Dictionary, Appendix A View Column Values: GC - CREATE GLOP GD - DROP GLOP GM - GLOP MEMBER   Regards, Vl...
9522 Sep 2014 @ 11:03 PDTDatabaseReplySelecting all rows that have a unique columnHi Brim, if you want to find rows with unique ID, the following SQL applies.  The window analytic functions are great for such purposes.   select id, name, email from source_table qu...
9422 Sep 2014 @ 10:52 PDTDatabaseReplychecking I/O skewHi Moutusi, there are columns for maximum I/O and average I/O to get the I/O skew: max = MaxAMPIO avg = TotalIOCount / nullifzero( NumOfActiveAMPs )   skewfactor is  (100...
9322 Sep 2014 @ 10:43 PDTDatabaseReplySplit Percentage based on total.Hi Sreeni, try something like this (has to be tested):   select organization_code, product_family, the_sum, quarter_name,           the_sum * 100.00 / nu...
9207 Aug 2014 @ 11:25 PDTGeneralReplyQuery performance issueHi, you can try to use a join instead a correlated query, i.e. something like this:     SELECT        count(distinct(TBL_OUTPUT.C_CID)) as CNT FROM ...
9129 Jul 2014 @ 11:43 PDTDatabaseReplyThoughts on CPUBusy caclulations.Hi Ekladios,  there could be queries that run more than one hour, or at least span two hours.  If AMPCpuTime is summed up to "per hour", which is probably a query with a GROUP ...
9029 Jul 2014 @ 11:24 PDTAnalyticsReplyWhy subqueries in case are not working in teradata?Hi,   one of possible solutions is to do a left join with the lookup table, and see whether or not the record is found:   select ...    and B.TYP_CD IS NULL   /* and T...
8928 Jul 2014 @ 04:42 PDTDatabaseReplyViewpoint and Corresponding DBC Table p.s. forgot the alias, it is SELECT * FROM TABLE (SYSLIB.MonitorSession(-1,'*',0)) as t1; Both MonitorSession and DBQL do not have skew column pre-calcul...
8828 Jul 2014 @ 01:11 PDTDatabaseReplyViewpoint and Corresponding DBC TableHi Santanu,   for the near-real-time data, you might want to check MonitorSession:  SELECT * FROM TABLE (SYSLIB.MonitorSession(-1,'*',0)); for the history data, the DBQL ...
8727 Nov 2013 @ 10:17 PSTUDAReplyCancel Rollback on a table with referential integrityHi Raja, I've seen it here:  http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/Utilities/B035_1102_111A/RecoveryMgr.51.23.html With kind regards, Vlad.
8627 Nov 2013 @ 06:05 PSTTrainingReplydifference between soft referential integrity and hard referential integrity in teradataHi,  in the example above, the soft RI is not defined.    The phrase "-- here constraint is not used/commented" is not defining soft RI.      If there is ...
8527 Nov 2013 @ 05:58 PSTUDAReplyCancel Rollback on a table with referential integrityHi Noel, If you have found a solution for this, then please kindly share.   The documentation says that it is not allowed. Regards, Vlad.
8427 Nov 2013 @ 05:54 PSTDatabaseReplyExplain plan for set tableHi Abin, it is possible to measure.   Create two similar tables - one set and one multiset, with the same data.   Insert same new data into both tables.   Ant then measure CPU consu...
8326 Nov 2013 @ 09:28 PSTDatabaseReplyExplain plan for set tableHi Khurram, Thank you for agreeing with me.   However, I believe that there is no need to dedicate an additional post to say only that. :)   Regards, Vlad.
8226 Nov 2013 @ 08:55 PSTDatabaseReplyExplain plan for set table  Hello Abin, you have really provided the answer in the first paragraph, the are no additional steps.   The phrase "defined as set and then altered it as set" has the "s...
8122 Nov 2013 @ 12:00 PSTDatabaseReplyDatabase Performance: large Table getting redistributed causing performance problemsHi Naga, first thing is to check the statistics.    And information about 2nd table is, indeed, very needed. Apart from that, if you select only a few columns(4) from a table, a distrib...
8020 Nov 2013 @ 11:38 PSTDatabaseReplyrecursive query numeric overflow issueHello Arun,   if the recursion is not too deep, then one of the possible options is to store all the "previous" manager ids in a separate column, and then filter by that column, li...
7920 Nov 2013 @ 11:16 PSTDatabaseReplyCASE STATEMENT- The brackets [] should better be () - = ('WHS')    should better be     = 'WHS' apart from that, I just agree with Ulrich. Regards, Vlad.
7820 Nov 2013 @ 02:52 PSTDatabaseReplyCASE STATEMENTjust substitute: if -> case when use -> then and add the "end" at the end.
7720 Nov 2013 @ 12:20 PSTDatabaseReplymacro sql Optimizationsorry for my previous post, I've not seen the 2nd explain.
7616 Nov 2013 @ 01:34 PSTDatabaseReplyGet previous column vale till it reaches another not null valueHi Bikky,   There is one more option.   Apart from using the Ordered Analytical Functions, you might want to introduce the Start_date and End_date into the LKP table.   This approa...
7516 Nov 2013 @ 01:27 PSTDatabaseReplyregarding the conversion of currency from INR to n e other currencyHi Peiter,  when you join two tables, both of them having start_date and end_date, then the join condition for those columns (in addition to currency_code, etc.) should be:     &n...
7415 Nov 2013 @ 06:43 PSTDatabaseReplySpool Space Usagep.s. also take Skew into account, because spool space limit is divided equally across the AMPs.   So, in reality, it would be more precise to capture maximum value across AMPs also (group by b...
7315 Nov 2013 @ 06:41 PSTDatabaseReplySpool Space UsageHello Kishore, For the average, you can setup a script that would run, say, every one hour or every 10 minutes, and insert-select from dbc.databases the "currentspool" field, grouped by ...
7215 Nov 2013 @ 03:18 PSTDatabaseReplymacro sql Optimization  Hello,   your Explain shows that partition elimination does happen, both for PMART_ST.PRE_PAYMENT_DETAIL and PMART_ST.PRE_PAYMENT_RESETL_DETAIL tables (the phrase ...

Pages