3703 | 15 May 2015 @ 10:57 PDT | Database | Reply | Stored Procedure performance improvement | Sounds like your're using some cursor-based processing. Of course this is slow, in every DBMS, but especially bad in a parallel system.
E.g. you don't need an SP to find the next business ... |
3702 | 15 May 2015 @ 04:46 PDT | General | Reply | dbc.indices ; dbc.indexconstraints ; | Hi Srini,
there are two possible reasons:
You got multiple rows per table due to multi-column indexes, add "and columnposition = 1" to return only one row per table
There a... |
3701 | 15 May 2015 @ 04:38 PDT | General | Reply | Retain amounts where NULL and divide by number of NULLS | This should return the expected result (if Code and Amt are both NULL):
SELECT
Acc, dt,
--get the Code for each group
MAX(Code) OVER (PARTITION BY Acc, grp),
--get the Amt ... |
3700 | 14 May 2015 @ 06:29 PDT | General | Reply | Removing Logical Duplicates | What if there's a no flight back?
This will return the correct result:
SELECT
LOC_1,
LOC_2,
distance
FROM tab
QUALIFY
ROW_NUMBER() OVER (PARTITION BY
CASE WH... |
3699 | 14 May 2015 @ 02:23 PDT | Database | Reply | DBC.ColumnsV ColumnFormat | FORMATs are manly based on COBOL syntax, e.g. '9' means a leading zero, '(n)' repeats the previous character n times.
See "Data Type Formats and Format Phrases"... |
3698 | 14 May 2015 @ 02:19 PDT | Database | Reply | Comparing 2 different datatypes giving undesired results | Hi Cheeli,
a NULL in SQL is not a binary zero.
What you see seems to be a common problem when data is loaded from DB2, it's the so-called "low value". DB2 tends to add those in... |
3697 | 14 May 2015 @ 02:11 PDT | Database | Reply | comparison of 2 columns of different datatypes giving undesired results | Hi Cheeli,
why do you post the same question using a new account?
|
3696 | 13 May 2015 @ 02:30 PDT | Database | Reply | Partition | What's your Teradata release?
In TD14.10 this could be done with an INS/SEL into a normalized table or using
SELECT ACCNO, ACCNAME, SAL, BEGIN(pd)
FROM
(
SELECT NORMALIZE ACCNO... |
3695 | 13 May 2015 @ 02:13 PDT | Database | Reply | Derived table error / object contains restricted characters | Don't know how you typed it (did you write the code in MS Word?), but in line 85 there's an illegal character:
ELSE b.FA_AMT – daq.psamt END AS DIFF
The '-' is not U+... |
3694 | 13 May 2015 @ 08:03 PDT | Database | Reply | ERROR IN CASTING DECIMAL TO INTEGER | Hi Yuvana,
of course there's no way to cast a 14 digit number to an integer (and stripping of some digits by casting to a varchar(10) will result in garbage).
You need to change the target ta... |
3693 | 12 May 2015 @ 10:53 PDT | Database | Reply | ERROR IN CASTING DECIMAL TO INTEGER | Well, a decimal has a range of +/+ 2**31, of course a 14-digit decimal doesn't fit.
|
3692 | 12 May 2015 @ 09:36 PDT | Database | Reply | swapping values in a column oracle SQL plus | You can do it with a single query using CASE:
update emp set job=
case job
when 'SALESMAN' then 'CLERK'
when 'CLERK' then 'SALESMAN'
END
... |
3691 | 09 May 2015 @ 07:32 PDT | Database | Reply | Replacing symbols from input column | Hi Moutusi,
what's your Teradata release?
oTranslate(col, ' *-.', '') removes those characters.
|
3690 | 09 May 2015 @ 07:31 PDT | Tools | Reply | DUPLICATE RECORDS USAGE | Teradata implements MULTISET table because Standard SQL defines them and all other DBMSes do the same :-)
Of course nobody wants duplicate rows in a production database, but they might be useful i... |
3689 | 09 May 2015 @ 07:27 PDT | Tools | Reply | BTEQ IMPORT SKIP Command | There's a blank within the filepath, you must quote it:
.Import Vartext',' file="C:\Users\Sridhar\Desktop\teradata\BTEQ Scripts Outputs\sridhar4.txt", SKIP=10;
|
3688 | 08 May 2015 @ 11:40 PDT | Tools | Reply | TTU for Mac OS | There's also Wallet, ODBC & TPT for OS X :-)
|
3687 | 06 May 2015 @ 03:55 PDT | Database | Reply | Help with query- I receive error 3504 | Hi Ilana,
you need to add all columns without aggregation function to GROUP BY:
SELECT
TRUNC (CAST (created_at AS DATE), 'IW') AS first_day_week
,USER_ACCESS_mode
... |
3686 | 06 May 2015 @ 03:52 PDT | General | Reply | Super Basic: How do you use 'Date' as a qualifier in a query? | There's only one recommended way to write a date using Standard SQL syntax:
where dateofhire >= DATE '2010-12-31'
|
3685 | 03 May 2015 @ 01:52 PDT | Tools | Reply | FastLoad Error -incorrect number of bytes returned | Check if there's a final linebreak after the last line
|
3684 | 03 May 2015 @ 04:27 PDT | Tools | Reply | FastLoad Error -incorrect number of bytes returned | You define your input as fixed width, but the actual data is variable width.
Use set record VARTEXT;, change all fields to VARCHAR, remove all delims and the final newlinechar.
And because y... |
3683 | 01 May 2015 @ 05:12 PDT | Database | Reply | Selecting with WHERE clause leads to all-row scan . | Without the actual SELECTs it's hard to tell if both queries return the same result.
Compare EXPLAINs and chekc if the condition is pushed into the Derived Table in Q2.
Otherwise you mi... |
3682 | 01 May 2015 @ 03:49 PDT | Database | Reply | Parellal insert statement against same global temporary table | No, because every session uses it's own version of that table.
Do you finally Insert/Select into a permanent target table?
|
3681 | 30 Apr 2015 @ 02:36 PDT | Database | Reply | Windows Aggregate Function | Hi Abhinav,
this should return the expected result:
SELECT .....
CASE
WHEN ExistingRole = 'R_PROD_SALES_ANLYTCS' THEN ExistingRole
WHEN ExistingRole IN ('R_PROD_WIL&... |
3680 | 30 Apr 2015 @ 01:53 PDT | Extensibility | Reply | Issue with Stored Procedure | If you want to abort unconditionally you don't need dynamic SQL.
ABORTSESSIONS returns a single row with the count of aborted sessions, so simply return this into a variable:
SELECT sys... |
3679 | 30 Apr 2015 @ 01:46 PDT | Database | Reply | SQL Server Query to Teradata : XML PATH | If your TD system supports XML (native or as addon) there's an XML aggregate function. And you better replace the Scalar Subquery with a join:
CREATE VOLATILE TABLE #tmpFinal AS (
SELECT... |