273 | 28 Jun 2011 @ 05:58 PDT | General | Reply | confusion about using BETWEEN keyword for characters. | Last Name will be selected if the last name begins with "r" or the last name is "s" followed by spaces. This is because the 'r' and 's' constants in your between are expanded to the same size as la... |
272 | 20 Jun 2011 @ 10:27 PDT | Database | Reply | using UDF with TPT | If you want one single quote within a string, just use two.
So:
'Insert into table1 values ( nvl(:column, ''replacement string''));' |
271 | 19 Jun 2011 @ 01:06 PDT | Viewpoint | Reply | Getting the role of the current user | DBC.Users contains the default rolename for any user.
When a user logs on, a record is created for every session in DBC.SessionInfo - this also contains the username and rolename for that session.... |
270 | 14 Jun 2011 @ 11:16 PDT | General | Reply | Restarting Mload delete job. | It is, but there is a write lock on the table at that point.
You need to use an access lock on your read operation. |
269 | 12 Jun 2011 @ 10:52 PDT | Database | Reply | NUSI subtable distribution | NUSI entries are located on the same AMP as the base row. For this reason, access via NUSI is always an all amps operation.
See Database Design, Secondary Indexes. |
268 | 08 Jun 2011 @ 12:20 PDT | General | Reply | MULTILOAD CONSTRAINTS | Multiload cannot handle records which span vprocs.
If a table has a NUSI defined, the index entry for that record is on the same vproc as the record itself. If the table has a USI, the index entry... |
267 | 02 Jun 2011 @ 05:55 PDT | Database | Reply | How many bytes a unicode character set takes? | On the Teradata server, Unicode characters are held as UTF-16 - each character occupies 2 bytes. So in your case, NAME will occupy two bytes for the length and two bytes for each character. The fie... |
266 | 01 Jun 2011 @ 02:40 PDT | Tools | Reply | Bteq Import, Delete Statement on same table at once causing deadlock | Sorry, that should have been ERRORCODE.
Rollback will cause anything from the BT to be backed out of the database and restored to a state as it was whe the BT was issued. The message 'Operation ... |
265 | 31 May 2011 @ 06:48 PDT | Tools | Reply | How to test Checkpoints in FastLoad | Fastload does not load records directly into the target table during the acquisition (first ) phase. It puts them into a subtable; when it encounters an END LOADING it then moves them from the subt... |
264 | 31 May 2011 @ 06:33 PDT | Tools | Reply | Bteq Import, Delete Statement on same table at once causing deadlock | You need to ensure your delete and inserts all occur within a BT/ ET block and that the table is locked throughout the process.
So:
.Retry Off
BT ;
Locking Tbl1 for Write ;
Delete From Tbl... |
263 | 27 May 2011 @ 02:43 PDT | Database | Reply | Performance problem with insert in TD 13.0 | Yes - define a usable primary index on the target table.
If the primary index on the target has very few values, insert to a Set table will be very slow. Inset to a Multiset table is MUCH quicker ... |
262 | 27 May 2011 @ 02:39 PDT | Database | Reply | Does NULL actually acquire any bit of memory in Teradata or in anyother database? | 1. If a column is not compressed, then space is used for that column whether the value is null or not. So of the column spec is Char(20), 20 character are used by that column regardless of the valu... |
261 | 22 May 2011 @ 09:12 PDT | Database | Reply | count(*) | Select
Sum(Case When D1.TblName = 'TableA' Then TblCount End) As TableA
, Sum(Case When D1.TblName = 'TableB' Then TblCount End) As TableB
From
(Select 'TableA' As TblName, Count(*) As TblCou... |
260 | 22 May 2011 @ 06:49 PDT | Database | Reply | An Bteq Error Code return issue.Need Help | It is in an environment variable called errorlevel.
See:
http://stackoverflow.com/questions/334879/how-do-i-get-the-application-exit-code-from-a-windows-command-line |
259 | 21 May 2011 @ 08:38 PDT | General | Reply | BTEQ Export to override existing file | Delete the file before from within bteq before your export command:
.os rm exportfile
.export data file = exportfile
where exportfile is the path and filename of your data file. |
258 | 20 May 2011 @ 02:04 PDT | Database | Reply | Trimming Inner Spaces | The replace function does not rexist on Teradata.
There is an oreplace UDF available on the Teradata website. See :
http://downloads.teradata.com/download/extensibility/teradata-udfs-for-popular-... |
257 | 13 May 2011 @ 05:55 PDT | Tools | Reply | Record count from worktable in mload | Multiload locks the work table for Write.
If you want to look at it during the load, use an Access Lock.
Locking wt_tabl1 For Access
select Count(*) from wt_tabl1;
:-) |
256 | 13 May 2011 @ 05:42 PDT | Database | Reply | Update Set by Subquery | Use a standard ANSI update :-
UPDATE u_ukul_martdb.LB_MORE_BL_Price_Incentive a
SET Date_Accnt_Closed = u_ukul_martdb.LB_MORE_BL_accepts.rejection_date
Where
a.Cust_num = u_ukul_martdb.LB_M... |
255 | 12 May 2011 @ 02:57 PDT | Tools | Reply | How to load formatted file ( with indicator bits) using bteq | Import Indicdata will do it.
I would normally specify the Not Null against appropriate columns, but it looks like you can get away without it.
.import indicdata file= d:bteqtestorg.txt
.Repe... |
254 | 12 May 2011 @ 02:09 PDT | Database | Reply | Building heirarchal answerset from single table | You do not need a recursive query for this - just use a Join.
Create Table OrgHier
(User_Id Smallint Not Null
, First_Name Varchar(20) Not Null
, Last_Name Varchar(30) Not Null
, Super... |
253 | 12 May 2011 @ 01:44 PDT | Tools | Reply | MLOAD DELETE vs MLOAD Import with Delete statement | A MLoad DELETE task deletes records using a full table scan (normally a fairly high percentage of the table). It is normally used for removing aged records or removing all records for a business un... |
252 | 08 May 2011 @ 03:47 PDT | Database | Reply | Select records from previous week based on current date | Week To Date
------------------
Select * From MyTab MyT
Where MyT.Dt in
(Select D.Calendar_Date From Sys_Calendar.Calendar D
Join
(Select Max(C.Calendar_Date) As ST_Monday -- The Mond... |
251 | 06 May 2011 @ 04:38 PDT | General | Reply | How to recovert Teradate Query from Windows Standby | By way of explanation, when you rebooted your system, the IP connection was reset. When you restarted Teradata, your Teradata session was discarded because the IP session was reset; any updates bac... |
250 | 06 May 2011 @ 03:56 PDT | Analytics | Reply | Percentile on MS Excel vs Teradata | Select Max(Vals)
From
(SEL "Value", PERCENT_RANK() OVER(ORDER BY "Value") PER_VAL
FROM T1) As A
Where Per_Val LE 0.5
;
I suggest you try with a couple more examples, but looks OK to m... |
249 | 06 May 2011 @ 12:12 PDT | Database | Reply | Select records from previous week based on current date | Select * From MyTab MyT
Where MyT.Dt in
(Select D.Calendar_Date From Sys_Calendar.Calendar D -- all days of last week
Join
(Select Max(C.Calendar_Date) As ST_Monday -- The Monday of las... |