98 | 27 Oct 2009 @ 01:28 PDT | Database | Reply | rank() (partition by....) help | Like 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... |
97 | 23 Oct 2009 @ 09:22 PDT | Analytics | Reply | what lock is when inserting data | Pawan 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... |
96 | 20 Oct 2009 @ 11:08 PDT | Tools | Reply | MLoad Update | Multiload 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 ... |
95 | 20 Oct 2009 @ 10:51 PDT | Database | Reply | create inserts on table data | In 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... |
94 | 20 Oct 2009 @ 01:01 PDT | Database | Reply | Please help with update conversion from oracle to Teradata | Apologies - 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, ... |
93 | 19 Oct 2009 @ 05:04 PDT | Database | Reply | Please help with update conversion from oracle to Teradata | Try: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... |
92 | 19 Oct 2009 @ 08:58 PDT | Database | Reply | Storing GMT offset in minutes with sign | Hold 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 ... |
91 | 19 Oct 2009 @ 06:09 PDT | Database | Reply | Allowing 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... |
90 | 13 Oct 2009 @ 01:30 PDT | Database | Reply | COALESCE | NoCASE 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. |
89 | 13 Oct 2009 @ 01:47 PDT | Database | Reply | COALESCE | CASE WHEN db_1.appl_no = ' 'THEN db_2.appl_noELSE db_1.appl_no END as Appl_nbrLooks neater! |
88 | 12 Oct 2009 @ 02:49 PDT | Database | Reply | Rename View | Yes.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;... |
87 | 11 Oct 2009 @ 11:23 PDT | General | Reply | To Find Codepage setting of Database | Databases do not have character serts or codepages.
The Chartype is set at column level in DBC.Columns. Chartype 1 is Latin, 2 is Unicode. |
86 | 08 Oct 2009 @ 11:17 PDT | Database | Reply | Problem with Keyword YEAR in a view | You 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... |
85 | 07 Oct 2009 @ 05:50 PDT | UDA | Reply | Date Range Syntax | Two 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... |
84 | 07 Oct 2009 @ 04:17 PDT | Analytics | Reply | How to calculate 90th percentile in Teradata | Teradata 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) =... |
83 | 06 Oct 2009 @ 02:58 PDT | Database | Reply | format char fields to have leading zeroes | If 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)) ... |
82 | 06 Oct 2009 @ 12:47 PDT | UDA | Reply | Table alter timestamp | DBC.Tables.LastAlterTimestampgive the date/ time that DDL was run on the table - eg Create/ Alter/ Create or Drop Index, etc |
81 | 04 Oct 2009 @ 12:24 PDT | Database | Reply | User commentString | COMMENT ON USER username AS 'anythingyoulike'; |
80 | 02 Oct 2009 @ 07:45 PDT | UDA | Reply | performance tuning or query tuning | The 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... |
79 | 02 Oct 2009 @ 07:17 PDT | Database | Reply | dbc.user macro | Take 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... |
78 | 29 Sep 2009 @ 01:57 PDT | Database | Reply | Like - Wildcard | It 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... |
77 | 28 Sep 2009 @ 08:30 PDT | Database | Reply | Strange 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... |
76 | 28 Sep 2009 @ 06:30 PDT | Database | Reply | How to Count Multiple Status | I 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... |
75 | 28 Sep 2009 @ 04:23 PDT | General | Reply | Conditional Merge Into | Instead 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... |
74 | 25 Sep 2009 @ 04:35 PDT | UDA | Reply | performance tuning or query tuning | Step 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... |