#DateForumTypeThreadPost
350311 Feb 2015 @ 11:24 PSTExtensibilityReplyPassing result set from stored procedure to CognosYou need to return a single answer set to Cognos? Why don't you do it directly from the SP using DYNAMIC RESULT SETS, afaik Cognos supports one result set returned by a SP (I don't know if...
350210 Feb 2015 @ 12:32 PSTExtensibilityReplyPassing result set from stored procedure to CognosWhat's your TD release? Are XML services installed?   There's a nice XMLAGG function for concatenating multiple rows into one: create volatile table vt ( locn varchar(10), ...
350110 Feb 2015 @ 09:48 PSTDatabaseReplyCalculating Fiscal Year Get the next sunday after jan 31: NEXT_DAY(TO_DATE(TO_CHAR(ADD_MONTHS(CURRENT_DATE, -36), 'YYYY') || '0131', 'yyyymmdd'), 'sun')   You probably need this inf...
350010 Feb 2015 @ 09:36 PSTAnalyticsReplyHow to get previous quarter total (SQL)There's no way to get this result without nesting OLAP-functions. Assuming there's always a row per customer/quarter: Select Customer , Quarter , Month , QuarterSales , sum(...
349910 Feb 2015 @ 08:53 PSTExtensibilityReplyPassing result set from stored procedure to CognosOf course you could do it with a SP, but there might be much simpler ways to do it. How do you get the rows to be concatenated? Can you elaborate on the "far more complex, with multiple nest...
349809 Feb 2015 @ 10:27 PSTDatabaseReplyDBC.Objectusage table is empty but ObjectUseCountCollectRate = 10 Hi Guru, you need to enable it for specific databases using BEGIN QUERY LOGGING WITH USECOUNT
349709 Feb 2015 @ 01:48 PSTDatabaseReplyCumulative Sum by distinct customer ID by yearYou need to nest OLAP functions, tag the first row with a new col5 value and then do a cumulative sum: SELECT customer, col4, col5, SUM(x) OVER (PARTITION BY customer ORDER B...
349608 Feb 2015 @ 08:34 PSTDatabaseReplySelecting with WHERE clause leads to all-row scan .Of course this is a full table scan, you do some calculation on the Call_Start_Date. Additionally it will fail due to the interval calculation when you run this on 2015-03-29. WHERE Ca...
349508 Feb 2015 @ 03:50 PSTExtensibilityReplyTDAT.cI don't know if this code actually exists, but Glenn McCall's wrote what you need: Access External Data - A Table UDF
349408 Feb 2015 @ 03:47 PSTDatabaseReplyCumulative Sum by distinct customer ID by yearWhat are the rules to get the desired result? What is "Sum(distinct count) order by col4"?
349306 Feb 2015 @ 08:23 PSTDatabaseReplyWhere clause using case statementREVN_CAL_MO.MO is a CHAR? Simply substract 5 days from today and use this date instead: REVN_CAL_MO.MO between trim(add_months(date - 5,-14) (format 'yyyymm')) and ...
349205 Feb 2015 @ 12:24 PSTDatabaseReplyMulti-Table Insert StatementsTeradata SQL doesn't support multi-table inserts. Only Teradata's load utilities (MLOAD/TPUMP/TPT LOAD/TPT STREAM) can do what you want using multiple APPLYs. You might split the inserts ...
349105 Feb 2015 @ 11:45 PSTCloud ComputingReplyBYNET kernel driver installation fail, after new kernel installatoin.There's a reason for PUT, it would either prevent a wrong installation or do it correctly :-)   Instead of fiddling with the VM (which is btw probably not covered by the TD Express licen...
349005 Feb 2015 @ 11:29 PSTDatabaseReplyRounding: Result of division being forced to .000What's the datatypes of the operands?   The easiest solution is to change the percentage calculation to multiply first and then divide: (100*74.06 / 72.09 - 100)    Or cast ...
348904 Feb 2015 @ 02:27 PSTDatabaseReplyA Script for Spool SpaceThis old syntax is only supported by BTEQ/CLI.   You might switch to GROUPING SETS instead: SELECT DATABASENAME, VPROC, SUM(CURRENTSPOOL) AS CURRENTSPOOL FROM DBC.DISKSPACE--; --a w...
348804 Feb 2015 @ 01:25 PSTDatabaseReplyError: Syntax error: expected something between the word 'a' and '.'Your SUBSTRING uses ODBC syntax, which is not valid, it's either substring(a11.zz from 2 for 1) or substr(a11.zz ,2,1)
348704 Feb 2015 @ 01:21 PSTDatabaseReplyTuning qry- COALESCE is just a shortcut for a CASE, see explain. - For a UPI table there's no real difference between SET and MULTISET. - Estimated time doesn't matter, what's the actual runtim...
348604 Feb 2015 @ 09:09 PSTAnalyticsReplyParallel Loads to same target tableYou're directly loading into the target table? Are the sources flat files? Do they arrive at the same time? What' s the size of the target and the updated percentage?   Maybe swit...
348504 Feb 2015 @ 08:51 PSTToolsReplyFAST LOAD Failing in mainframe jobThe syntax looks ok, are you sure it's not due to a missing semicolon for the previous statement?
348404 Feb 2015 @ 08:46 PSTDatabaseReplyHierarchy Query using Recursive functionIf this is always for a single root you might use this top-down query: WITH RECURSIVE REC_EMP_CHILD ( EMP_ID , EMP_NAME , lvl ) AS ( SELECT ...
348304 Feb 2015 @ 08:32 PSTDatabaseReplyA Script for Spool SpaceWITH is supported since V2R6, only multiple CTEs are a TD14 feature. Can you show the script? Spool space is returned by a simple SELECT FROM dbc.DiskSpaceV.
348204 Feb 2015 @ 08:18 PSTDatabaseReplyHelp Stats Displays Incorrect StatisticsHi Stephen, are you on TD14.10? Then it might be related to the new stats features, the optimizer might have skipped the collection. See Carrie Ballinger's Statistics Threshold Function...
348104 Feb 2015 @ 08:07 PSTGeneralReplyImporting a file from textAssuming this is done using SQL Assistant, change the "Maximum batch size for simple imports" in Tools -> Option - Import to the  maximum 999.
348004 Feb 2015 @ 07:43 PSTDatabaseReplyHow to find all the tables in Teradata with specific column names in them?Please change the view to dbc.ColumnsV, dbc.Columns is an old legacy version, deprecated since TD12. Starting with TD14.10 it might return wrong results if object names longer than 30 characters ar...
347903 Feb 2015 @ 02:23 PSTDatabaseReplyPartition of a Timestamp columnHi Samir, Teradata stores DATEs using following formula: (year -1900) * 10000 + month * 100 + day select cast(150130 as date), cast(1150130 as date); 150130 1150130 ---------- ----...

Pages