#DateForumTypeThreadPost
3126 Sep 2013 @ 06:06 PDTDatabaseTopicCalculating compound interest in SQLI've been trying to figure out a way to calculate compound interest in SQL and I think that I can only do it using recursive SQL or actually return the rows using a cursor and calculate it in t...
3027 Jul 2012 @ 05:55 PDTDatabaseReplyGroup AMP Join in a 13.10 Explain plan Hi Mike, A "Group-AMP" join occurs when the optimizer detects that it has a few hash values that will be involved, but not enough to warrant an "all-AMP" join. The advant...
2926 Jul 2012 @ 06:27 PDTDatabaseReplyGet the number of sessions of a job I don't know if it's 100% reliable, but I've used the LogonSource column in dbc.logonoff to do this in the past. In the case of mainframe, the mainframe job name appears in a co...
2826 Jul 2012 @ 06:16 PDTDatabaseReplySliding Window and Single Window Merge Join When a sliding window merge join is done, the rows are not sorted beforehand. They are in the order that they appear in the table (by partition, then by the hashcode). If you have one table/...
2726 Jul 2012 @ 05:15 PDTDatabaseReplyCollect Stats Multi-Column The second statement is not redundant. Statistics from the first statement would be used to determine the cardinality and value distribution when both the dept and emp_no are used in a const...
2625 Jul 2012 @ 10:04 PDTDatabaseReplySYSLIB database SYSLIB is for storing global UDF's as well as tables that are used for distributing new packages. So, you can make it smaller as long as you have enough space for new global UDF's you mi...
2525 Jul 2012 @ 09:45 PDTDatabaseReplyCreating a volatile table and selecting from it in the same query. Are you trying to create the volatile table and select from it in the same unit of work? If so, you can't do that as only one DDL statement is allowed in a unit of work. So, the SELECT s...
2425 Jul 2012 @ 07:58 PDTDatabaseReplySingle column and multicolumn stats Just a general recommendation... if you are going to collect any stats on a table at all, the first statistic you should collect on is the primary index columns (together, not separately). ...
2325 Jul 2012 @ 07:50 PDTDatabaseReplyColumn parititioning - why does explain show an extra partition? I guess I should have read the Orange Book first. This is from the Orange Book: Note that the count of 27 for the number of column partitions includes the 26 user-specified column partitions...
2225 Jul 2012 @ 07:40 PDTDatabaseReplyConnections to Teradata Answers below: 1) Connectivity can be set up to some or all nodes in the system using DNS. Teradata handles the round-robing of sessions across all nodes that are set up in the DNS provided ...
2125 Jul 2012 @ 07:20 PDTDatabaseReplyBackup consistency At the start of the backup. Normally, Teradata places a read lock on the table while it's doing a backup, so the data in a table can't be updated until the lock is rel...
2025 Jul 2012 @ 06:39 PDTDatabaseTopicColumn parititioning - why does explain show an extra partition? I've just started playing around with column partitioning in R14. I created a table using a recent example that Paul Sinclair put into a recent Teradata Magazine article: CREATE TABLE Sa...
1920 Dec 2011 @ 01:22 PSTGeneralReplyDECLARE CURSOR (Macro Form) - Calling Macro Inside SP using CURSORS I have the release 12 manuals for stored procedures and under "Restrictions for stored procedures", it says that you cannot execute a macro from within a stored procedure, so I believe...
1820 Dec 2011 @ 01:11 PSTGeneralReplyJoin Index with derived tables or subquery This will first need to do the aggregation for your GROUP BY clause. That process will not be very efficient because it will need to do a global aggregation (vs. local) because your GROUP B...
1720 Dec 2011 @ 12:55 PSTGeneralReplyUnderstanding the expression USING _spVV0 (DATE)LOCKING TABLE DWH_DATA.ACCT ACCESS That is just indicating that whatever statement was running had host variables that were passed into the SQL. Here is an example of one I pulled out of our query log: USING   &nb...
1626 Aug 2011 @ 10:51 PDTDatabaseReplyIs there a "reserved words" table/view? When you do your SELECT, put the phrase "LOCKING ROW FOR ACCESS" in front of it: LOCKING ROW FOR ACCESS SELECT... ; That will cause the SQL to use access locking which wi...
1526 Aug 2011 @ 10:41 PDTDatabaseReplyIs it possible to run dynamic queries outside of a stored procedure? If you can generate the SQL into a file, you can run the SQL from within BTEQ using ".run" command. If you're trying to run this process in a batch mode rather than interactive...
1426 Aug 2011 @ 10:34 PDTDatabaseReplyMore elegant way of stripping spaces from a postcode? If you can use UDF's, there is a UDF available that does the equivalent of a "Replace" in Oracle. It's called OReplace So, if you implemented that UDF, you could simply say...
1326 Aug 2011 @ 10:27 PDTDatabaseReplyCapacity Planning steps. The chapter that you're referring to mainly deals with predicting the capacity in terms of space that your newly defined tables will use. Once you are in Production, capacity planning become...
1226 Aug 2011 @ 09:53 PDTDatabaseReplyCalculate query dollar cost I don't know if there is a standard for this or not. What I have done in the past is to take the annual cost (or a shorter period if you don't have a year's worth of data) and divide...
1122 Aug 2011 @ 06:02 PDTGeneralReplyDifference between Single Table Join Index, NUSI and Hash Index Hi Alison, your points are all well taken. What I meant by my comment was that you could create a single table join index to do what a hash index does, plus you can do a lot more with join indexes...
1019 Aug 2011 @ 10:07 PDTDatabaseReplyContinuous Date SpansI think this does it. There might be a better way, but it seems to work for the data you provided. SELECT CUSTOMER_ID ,CASE WHEN EFF_ROW_IND = 'E' THEN EFFECTIVE END EFFECTIVE ,CASE W...
919 Aug 2011 @ 06:45 PDTDatabaseReplyNeed help on SQLWhat are you trying to get? The rows with the earliest 3 week_ending_date's? If so, it's like this: SELECT Account ,Product_Key ,Week_Ending_Date ,Price_Amt FROM...
817 Aug 2011 @ 11:50 PDTDatabaseReplyApplication of SOFT RIs in TeradataYes, that's correct.
716 Aug 2011 @ 11:33 PDTDatabaseReplyHelp with Bteq IMPORTYou cannot replace the tablename with a host variable. You can only use it for column values. Example: select * from tableName where columnName = :Name;

Pages