#DateForumTypeThreadPost
50518 May 2009 @ 06:21 PDTUDAReplyConcatenating different values in a field...You will have to cook up a recursive query, something like this ....WITH RECURSIVE RECTABLE(ID_, NAME_, PREV_NAME_)AS(SELECT ID, MIN(NAME) (VARCHAR(1000)) , MIN(NAME)FROM MYDATAGROUP BY 1UNION ALLS...
50418 May 2009 @ 05:25 PDTDatabaseReplyHow to find disk space used by a table.SELECT SUM(CURRENTPERM) ACTUALSPACE, MAX(CURRENTPERM)*(HASHAMP()+1) EFFECTIVESPACEFROM DBC.TABLESIZEWHERE DATABASENAME = 'mydb' AND TABLENAME = 'mytable';
50315 May 2009 @ 06:42 PDTToolsReplyHow to find a primary key in teradata tables?As a practice, database is not the right place to sort out what is the primary key of the table, that should be maintained in the data model.If you explicitly define a primary key in the table def...
50213 May 2009 @ 04:42 PDTToolsReplyImproving Loading Mechanism using FastloadThat's the named pipe access module.Teradata utilities are capable of reading/writing into a fifo (aka named pipe). This reduces the necessity for filesystem storage etc, especially when transferri...
50113 May 2009 @ 04:04 PDTUDAReplyStoring TD Stored Proceduresput the stored procedure definition into a file ( mysp.sql )REPLACE PROCEDURE TESTIT()begindeclare o_msg varchar(10) ;end;then in bteq do a.compile file = mysp.sql;The other option is put the def...
50012 May 2009 @ 05:57 PDTToolsReplyTpump- Pack factor and sessions ?That's true in that case there's a possibility of dead lock, one way to avoid that is to put pack factor 1 (very lousy in terms of performance).
49912 May 2009 @ 05:45 PDTDatabaseReplyCaseCase is always evaluated and processed from the first, and the first true condition is what you always get as output. Besides once case statement can output only one attribute.So in your case, you ...
49812 May 2009 @ 05:13 PDTTrainingReplynew to TeradataIn SQL Assistant, press F2
49711 May 2009 @ 07:23 PDTDatabaseReplyWhat's the quickest way to find out all the values under an attributesYou should probably ask your boss to get you some kind of data mining tool ... :oNow if you are really stuck, the below SQL to "generate" all the required SQLs for you.SELECT 'SELECT ' || TRIM(COL...
49611 May 2009 @ 07:03 PDTDatabaseReplyValid query or not???You are probably trying an oracle syntax that doesn't work on Teradata.you will have to try something like this ...UPDATE AFROM TblA A, (subquery returning a single value) BSET cola = B.colBWHERE A...
49511 May 2009 @ 06:40 PDTToolsReplyTpump- Pack factor and sessions ?If we both are talking the same stuff (oh..) then there are no "multiple" tpump jobs. There's only one tpump job, with multiple sessions. And the job takes care of routing records to proper session...
49410 May 2009 @ 08:51 PDTToolsReplyTpump- Pack factor and sessions ?Actually it "depends"For example, if you had serialize on, and all the three records had the same value for key, you would end up using just one session. On the other hand if all the three records ...
49308 May 2009 @ 08:10 PDTToolsReplyTpump- Pack factor and sessions ?In simpler terms pack factor helps you to bundle up a bunch on instructions into a single package and send it to TD. That's faster than sending one instruction, getting back it's ACK/RESP from TD, ...
49208 May 2009 @ 08:06 PDTToolsReplyTpump - Serialize ?The purpose of serialize option is to reduce the locking/blocking overhead between different sessions, with serialize feature you have the ability to specify that all rows with a given PI value get...
49108 May 2009 @ 07:43 PDTDatabaseReplyAutomating packdisk and checktable?As an example, to run packdisk, first you need to decide what is the criteria on which u need to pick the tables to pack .The below command will list tables occupying more than 100 cylinders with a...
49008 May 2009 @ 07:07 PDTDatabaseReplyFor loop problemThis is what I could make to work ...(in Teradata mode)CREATE VOLATILE TABLE MYUSERID.Test1(location_id INTEGER) ON COMMIT PRESERVE ROWS;INSERT INTO MYUSERID.Test1 VALUES(1000);INSERT INTO MYUSERID...
48920 Jan 2009 @ 11:51 PSTDatabaseReplyANSI versus Teradata Mode TransactionsYes they are treated like DDL statements.
48819 Jan 2009 @ 01:44 PSTToolsReplyHow to get return code after BTEQ excuted SQL in VBScriptI am not a VB guy, but I suspect that you would also want to add a quit statement at the end of all this.WshShell.SendKeys ".quit {ENTER}"bteq won't terminate in interactive mode without something ...
48719 Jan 2009 @ 01:38 PSTUDAReplywhat is the sqlloader equivalent in TeradataTeradata SQL assistant also allows you to load data from files to tables. but is a bad idea for a large amount of data. generally good for small samples / reference tables kind of scenarios.And the...
48615 Jan 2009 @ 09:38 PSTToolsReplyBTEQ loopI don't think that's possible, because even if you try to use some kind of logic and put a GOTO command to a previous label, it won't work as GOTO commands read labels only forward, it doesn't go b...
48513 Jan 2009 @ 12:10 PSTUDAReplyHow to CREATE , INSERT TABLES INTO PERSONAL DATABASE FROM THE COMPANY'S DATA WAREHOUSEYou can go through the data definition and sql manuals from teradata to learn detailed syntax,but here are some samples/* Table JOHN.Jump already exists and the column data types match those of the...
48411 Jan 2009 @ 06:47 PSTTrainingReplyRecommended SQL Editor?If you are looking for Freely available SQL clients, there's the Squirrel SQL client which works over JDBC and the Teradata Eclipse plugin which can be used to connect to To teradata via Eclipse (&...
48309 Jan 2009 @ 10:19 PSTUDAReplyLooking for dynamic SQL for cloning stats from a table.depends ...if you want to duplicate an entire table with its stats,create table a as b with data and stats;but if you already have the table structure in place and copy over the stats definitions a...
48208 Jan 2009 @ 09:29 PSTDatabaseReplyTeradata equivalent of Oracle RAISE_APPLICATION_ERROR or MS SQL RAISERROR?I think you are missing a few arguments ?void setExc_xsp(VARCHAR_LATIN *inputString,char sqlstate[6]SQL_TEXT fncname[129],SQL_TEXT sfncname[129],,SQL_TEXT error_message[257])
48106 Jan 2009 @ 09:34 PSTUDAReplyReturn a version id as at a particular dateI didn't test, but you can try something like this.inner joinon version.reference = instruction.referenceand instruction.instructiondate >= version.effectivedatequalify rank() over(partition by ...

Pages