178 | 16 Aug 2007 @ 08:07 PDT | Database | Reply | Help needed | Hi Sakthi,this is an example using sys_calendar:SELECT EXTRACT(YEAR FROM calendar_date - 24), EXTRACT(MONTH FROM calendar_date - 24), MIN(calendar_date), MAX(calendar_date)FROM sys_calendar... |
177 | 14 Aug 2007 @ 03:27 PDT | Database | Reply | Query Performance | Hi Mahek,it's a typecast error, probably casting a varchar to a numeric value.Check the column definitions...Btw, there's no need to nest coalesce: COALESCE( i.borrowerGCI,COALESCE(s.borrowerGCI,' ... |
176 | 14 Aug 2007 @ 12:28 PDT | Database | Reply | Query Performance | Hi Mahek,most of your CASEs are wrong, there's no else, so they will result in just two values: 0/blank and NULL.The target table probably has a PI on one of those columns and is a SET table. All r... |
175 | 06 Aug 2007 @ 05:38 PDT | UDA | Reply | TeraData vs Oracle | Hi Amir,"most of guys claims that teradata is much quicker than oracle in parallel processing.why? any technical reason."Because Teradata is a parallel database system using a shared nothing approa... |
174 | 06 Aug 2007 @ 05:24 PDT | UDA | Reply | Difference in output | The main problem within that query is the OR, this is always bad for the optimizer, because it can't calculate a rowhash using that column.But the OR is not an or, in fact it's an XOR:Join on ACCT_... |
173 | 31 Jul 2007 @ 03:29 PDT | Database | Reply | Query Probelm !! | Snodgrass's book is available as PDF:http://www.cs.arizona.edu/people/rts/publications.htmlI did something similar for missing dates, the most efficient way was:Calculate the next date using OLAP-f... |
172 | 24 Jul 2007 @ 12:03 PDT | Database | Reply | "verbose" explain | Hi Sakthi,it's a separate command, just submit:DIAGNOSTIC VERBOSEEXPLAIN ON FOR SESSION;Any explain after that will be "verbose" until you logoff:Dieter |
171 | 20 Jul 2007 @ 03:17 PDT | Database | Reply | Fast Export | If the Logtable is not dropped after a FastExport then it was not finished successfully.Check the output for a return code > 0 and any error messages.Dieter |
170 | 20 Jul 2007 @ 02:51 PDT | Analytics | Reply | Time to Integer;Integer to Time? | Hi Benjamin,did you run that query using BTEQ?Looks like there's a FORMAT 'HHMISS' on usg_time and the CAST is changing it back to the default format.Dieter |
169 | 20 Jul 2007 @ 02:34 PDT | Database | Reply | "verbose" explain | Most people want the exact opposite of a verbose explain :-)Try this, it's exactly what you're looking for:DIAGNOSTIC VERBOSEEXPLAIN ON FOR SESSION;Dieter |
168 | 11 Jul 2007 @ 06:51 PDT | Database | Reply | Need a better way (query request) | This one not only looks cool, it returns the expected answer set ;-)select product_id, product_descfrom feroz_sourcegroup by 1,2qualify count(*) over (partition by product_id) > 1Dieter |
167 | 07 Jul 2007 @ 05:07 PDT | UDA | Reply | Sql help | Hi ray,select * from SAL_DTL where SAL_EFF_DATE |
166 | 06 Jul 2007 @ 03:07 PDT | Database | Reply | Using the 'Right' Keyword in a query? | Hi Daniel,LEFT and RIGHT are not Standard SQL, but ODBC SQL.Left only works in QueryMan/SQL Assistant, when you switch on"Allow Use of ODBC SQL Extensions in Queries" in Options -> QueryIt is recom... |
165 | 29 Jun 2007 @ 02:08 PDT | UDA | Reply | product Join | Hi marcmc,"The statistics have been updated."Are you shure about that?According to explain both Tmp_Reported_Claims_YTD and Tmp_Reported_Claims_YTD1 just got a single row.Of course the optimizer is... |
164 | 27 Jun 2007 @ 04:07 PDT | Database | Reply | Space saved using Compression is exceptionally different from calculated result.. | Hi Leo,"Suppose out of 20 columns in my table ,10 columns have comression.I would like to understand th following.(1)How many compression bytes are needed per row."At least two, but it depends on t... |
163 | 22 Jun 2007 @ 03:26 PDT | Database | Reply | Sample without duplicates | Hi Joe,of course you're right, i tested it with a row_number, but i couldn't sent an email from that PC, so i keyed it in again with a RANK ;-)Dieter |
162 | 21 Jun 2007 @ 09:47 PDT | Database | Reply | Sample without duplicates | select * from tabqualify rank() over (partition by deptno order by 0) = 1 -- one random row per deptsample 3Dieter |
161 | 21 Jun 2007 @ 09:40 PDT | Database | Reply | [HELP] Modify a column name that is also a NUPI Index... | "Waiting for your SKILLED answers..."Ok, there's a skilled "NO, it's not possible" ;-)Dieter |
160 | 21 Jun 2007 @ 09:36 PDT | Database | Reply | Space saved using Compression is exceptionally different from calculated result.. | If there are not enough compress bits available then a new compress bytes is added:So compressing a CHAR(1) will save you 0 or 1 bytes per compressed row, but not 0.75 ;-)In your example COMPRESS i... |
159 | 24 May 2007 @ 03:58 PDT | Database | Reply | Triggers | How about a simple HELP VIEW dbc.triggers?Dieter |
158 | 24 May 2007 @ 03:56 PDT | Database | Reply | Table lock | Hi Sakthi,no way to find that out using SQL.There's only a command line tool called LokDisp...Dieter |
157 | 24 May 2007 @ 03:48 PDT | Database | Reply | Physical Implemetation of the Tables - PPI | Hi Sushant,that example is just to show that one can have different partitions on a table, per day and per month.Btw, after adding ranges using ALTER TABLE a SHOW TABLE will show a line for each ad... |
156 | 24 May 2007 @ 01:43 PDT | Database | Reply | Avoiding Decimal Places in the query | Hi moras,try this:WHERE FC_cost MOD 1 = 0Dieter |
155 | 24 May 2007 @ 01:39 PDT | Database | Reply | Triggers | dbc.Triggers.EnabledFlagDieter |
154 | 24 May 2007 @ 01:35 PDT | Database | Reply | Physical Implemetation of the Tables - PPI | Hi Sushant,BETWEEN DATE '2000-01-01' AND DATE '2002-01-01' EACH INTERVAL '1' MONTH ,DATE '2002-01-02' AND DATE '2004-01-01' EACH INTERVAL '1' MONTH ,DATE '2004-01-02' AND DATE '2006-01-01' EACH INT... |