3503 | 11 Feb 2015 @ 11:24 PST | Extensibility | Reply | Passing result set from stored procedure to Cognos | You 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... |
3502 | 10 Feb 2015 @ 12:32 PST | Extensibility | Reply | Passing result set from stored procedure to Cognos | What'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),
... |
3501 | 10 Feb 2015 @ 09:48 PST | Database | Reply | Calculating 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... |
3500 | 10 Feb 2015 @ 09:36 PST | Analytics | Reply | How 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(... |
3499 | 10 Feb 2015 @ 08:53 PST | Extensibility | Reply | Passing result set from stored procedure to Cognos | Of 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... |
3498 | 09 Feb 2015 @ 10:27 PST | Database | Reply | DBC.Objectusage table is empty but ObjectUseCountCollectRate = 10 | Hi Guru,
you need to enable it for specific databases using BEGIN QUERY LOGGING WITH USECOUNT
|
3497 | 09 Feb 2015 @ 01:48 PST | Database | Reply | Cumulative Sum by distinct customer ID by year | You 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... |
3496 | 08 Feb 2015 @ 08:34 PST | Database | Reply | Selecting 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... |
3495 | 08 Feb 2015 @ 03:50 PST | Extensibility | Reply | TDAT.c | I don't know if this code actually exists, but Glenn McCall's wrote what you need:
Access External Data - A Table UDF
|
3494 | 08 Feb 2015 @ 03:47 PST | Database | Reply | Cumulative Sum by distinct customer ID by year | What are the rules to get the desired result?
What is "Sum(distinct count) order by col4"?
|
3493 | 06 Feb 2015 @ 08:23 PST | Database | Reply | Where clause using case statement | REVN_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 ... |
3492 | 05 Feb 2015 @ 12:24 PST | Database | Reply | Multi-Table Insert Statements | Teradata 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 ... |
3491 | 05 Feb 2015 @ 11:45 PST | Cloud Computing | Reply | BYNET 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... |
3490 | 05 Feb 2015 @ 11:29 PST | Database | Reply | Rounding: Result of division being forced to .000 | What'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 ... |
3489 | 04 Feb 2015 @ 02:27 PST | Database | Reply | A Script for Spool Space | This 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... |
3488 | 04 Feb 2015 @ 01:25 PST | Database | Reply | Error: 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)
|
3487 | 04 Feb 2015 @ 01:21 PST | Database | Reply | Tuning 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... |
3486 | 04 Feb 2015 @ 09:09 PST | Analytics | Reply | Parallel Loads to same target table | You'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... |
3485 | 04 Feb 2015 @ 08:51 PST | Tools | Reply | FAST LOAD Failing in mainframe job | The syntax looks ok, are you sure it's not due to a missing semicolon for the previous statement?
|
3484 | 04 Feb 2015 @ 08:46 PST | Database | Reply | Hierarchy Query using Recursive function | If 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
... |
3483 | 04 Feb 2015 @ 08:32 PST | Database | Reply | A Script for Spool Space | WITH 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.
|
3482 | 04 Feb 2015 @ 08:18 PST | Database | Reply | Help Stats Displays Incorrect Statistics | Hi 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... |
3481 | 04 Feb 2015 @ 08:07 PST | General | Reply | Importing a file from text | Assuming this is done using SQL Assistant, change the "Maximum batch size for simple imports" in Tools -> Option - Import to the maximum 999.
|
3480 | 04 Feb 2015 @ 07:43 PST | Database | Reply | How 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... |
3479 | 03 Feb 2015 @ 02:23 PST | Database | Reply | Partition of a Timestamp column | Hi Samir,
Teradata stores DATEs using following formula: (year -1900) * 10000 + month * 100 + day
select cast(150130 as date), cast(1150130 as date);
150130 1150130
---------- ----... |