#DateForumTypeThreadPost
9827 Oct 2009 @ 01:28 PDTDatabaseReplyrank() (partition by....) helpLike Arun, I thought you were talking rubbish! But you are correct - you cannot use Rows on a Rank function.The following will find the 200 preceding rows for every partition column in a subquery a...
9723 Oct 2009 @ 09:22 PDTAnalyticsReplywhat lock is when inserting dataPawan is right - TBL2 gets a write lock.If a user accesses the table, he will normally aquire a read lock, so will be delayed until the write lock is released. (This can be avoided by setting the N...
9620 Oct 2009 @ 11:08 PDTToolsReplyMLoad UpdateMultiload will only let you update records when the Where clause contains all the primary index fields (and preferably primary key), so you will have to create a file with the primary key of every ...
9520 Oct 2009 @ 10:51 PDTDatabaseReplycreate inserts on table dataIn SQL Assistant:create Volatile table MyOldTable( Col1 Smallint Not Null, Col2 Smallint, Col3 Char(6))Unique primary index (col1)On Commit Preserve Rows;Insert Into MyOldTable (Col1, Col2, Col...
9420 Oct 2009 @ 01:01 PDTDatabaseReplyPlease help with update conversion from oracle to TeradataApologies - you need to take the key details from your subquery and join them back to the main table.Try:update table_a FROM (select A.Request_Id, A.Report_Name, A.Report_Type, ...
9319 Oct 2009 @ 05:04 PDTDatabaseReplyPlease help with update conversion from oracle to TeradataTry:update table_a A FROM (select C.propvalue from table_b B, table_c C ,table_a Awhere B.request_ID = A.request_ID and B.report_Name = A.report_name and B.report_type = A.report_type and C.systemn...
9219 Oct 2009 @ 08:58 PDTDatabaseReplyStoring GMT offset in minutes with signHold the time in an interval hour to minute field, not an integer - it is much easier to do time arithmetic on it. See below:create Volatile table My_Table( city Varchar(60),gmt_offset_tm interval ...
9119 Oct 2009 @ 06:09 PDTDatabaseReplyAllowing access on DBC tables/view. How risky it is?I disagree with Random_Thought! Unless there are really good security reasons, give users access to DBC by granting public access for select. Many enquiry tools need access to map data - they u...
9013 Oct 2009 @ 01:30 PDTDatabaseReplyCOALESCENoCASE db_1.appl_no WHEN ' 'THEN db_2.appl_noWHEN NULLTHEN db_2.appl_noELSE db_1.appl_no END as Appl_nbrIf you look at the explain of a COALESCE, it is simply a short form of CASE.
8913 Oct 2009 @ 01:47 PDTDatabaseReplyCOALESCECASE WHEN db_1.appl_no = ' 'THEN db_2.appl_noELSE db_1.appl_no END as Appl_nbrLooks neater!
8812 Oct 2009 @ 02:49 PDTDatabaseReplyRename ViewYes.Be careful though; the name change is not carried through completely.Consider the following:Replace View Test_Y1 As Select Current_Date As RunDate;Replace View Test_Y2 As Select * From Test_Y1;...
8711 Oct 2009 @ 11:23 PDTGeneralReplyTo Find Codepage setting of DatabaseDatabases do not have character serts or codepages. The Chartype is set at column level in DBC.Columns. Chartype 1 is Latin, 2 is Unicode.
8608 Oct 2009 @ 11:17 PDTDatabaseReplyProblem with Keyword YEAR in a viewYou are running the query through SQL Assistant and if you look in Tools/ Options/ Query you will see that Allow Use of ODBC Extensions In Queries is allowed.Microsoft ODBC interface is translating...
8507 Oct 2009 @ 05:50 PDTUDAReplyDate Range SyntaxTwo ways:1. Have someone install the Oracle UDF's on Teradata - then it will work!OR2. If you want to go Teradata native, it is:Select * from db.table WHERE Created BETWEEN ('06/02/2009 10:11:06 P...
8407 Oct 2009 @ 04:17 PDTAnalyticsReplyHow to calculate 90th percentile in TeradataTeradata is slightly more generic - it uses QUANTILE which allows you to split into groups based on any fraction.So 90th percentile is:Selectblah,blah,blahFrom somewhereQualify Quantile(100,col1) =...
8306 Oct 2009 @ 02:58 PDTDatabaseReplyformat char fields to have leading zeroesIf it is already a character, and there is no sign, then just put the required number of zeroes on the front:Select Substr('0000000000',1,10-Chars(Trim(col1))|| Trim(col1) (Char(10)) ...
8206 Oct 2009 @ 12:47 PDTUDAReplyTable alter timestampDBC.Tables.LastAlterTimestampgive the date/ time that DDL was run on the table - eg Create/ Alter/ Create or Drop Index, etc
8104 Oct 2009 @ 12:24 PDTDatabaseReplyUser commentStringCOMMENT ON USER username AS 'anythingyoulike';
8002 Oct 2009 @ 07:45 PDTUDAReplyperformance tuning or query tuningThe Primary Index on both these tables do not make sense, at least for this job. The suggested index is far more paractical, as long as spread is OK.I enclose DDL for suggested primary index for bo...
7902 Oct 2009 @ 07:17 PDTDatabaseReplydbc.user macroTake the code from DBC.Users and edit out the criteria at the end of the view.You will need to store it somewhere which has Select With Grant on DBC.Users, or it will give security violations. I no...
7829 Sep 2009 @ 01:57 PDTDatabaseReplyLike - WildcardIt is just like you say:wherefirst_name like ANY ('ad%', 'w%', 'e%')will find adrian, william and eleanorfirst_name like ALL ('ad%', 'w%', 'e%')will not find anything because you cannot build a str...
7728 Sep 2009 @ 08:30 PDTDatabaseReplyStrange Problem on "qualify"I have not tried your query, but I agree with Balamurugan that I do not think your second query will work either.I think the following will get what you are after:SELECT Column1 ,Column2 ,SUM(Colum...
7628 Sep 2009 @ 06:30 PDTDatabaseReplyHow to Count Multiple StatusI used slightly different data to test - I added an extra Status 3 record in the middle (to valiodate what happens when you have more than two records in a sequence with the same status!).So my tes...
7528 Sep 2009 @ 04:23 PDTGeneralReplyConditional Merge IntoInstead of trying to rule out the unneeded updates in the Update part of the statement, you could use a derived table based on T1 witout the rows you want to ignore. So: merge into T2 using (Sel...
7425 Sep 2009 @ 04:35 PDTUDAReplyperformance tuning or query tuningStep 1 - Get rid of the correlated subquery by:DELETE FROM A_BCIDDB.CREDIT_ENTRIES_CT T1WHERE T1.MI_ACCOUNT_IDENTIFIER = L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.MI_ACCOUNT_IDENTIFIERAND T1.ENTRY_AMOUNT = L...

Pages