Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 4 of 4 total |
Bug in SQL with: "ORDER BY" |
Thu, Dec 27 2007 1:47 PM | Permanent Link |
Roger Oliveira | More one problem I found in SQL: Field UPTIME, result -499 "%" while I don't use DESC in ORDER BY, but when I use DESC, the result is correct! = 99,93 "%" DBISam with BUG????????? Whats is wrong? Roger Oliveira roger@puxtreme.com.br Underline Informática. www.puxtreme.com.br ================================================================================ SQL statement (Executed with 4.25 Build 5) ================================================================================ SELECT CAST(D.DTHIN,DATE) DATA,SUM(IF(D.FK_RESPONSIBLE = 1 THEN SECONDS ELSE 0)) MANUTENCAO, SUM(IF(D.FK_RESPONSIBLE = 2 THEN SECONDS ELSE 0)) PRODUCAO, SUM(IF(D.FK_RESPONSIBLE = 5 THEN SECONDS ELSE 0)) ESPERA, SUM(IF(D.FK_RESPONSIBLE = 6 THEN SECONDS ELSE 0)) BLOQUEIO, SUM(IF(D.FK_RESPONSIBLE IN (1,2,5,6) THEN SECONDS ELSE 0)) TOTAL, SUM(IF(D.FK_RESPONSIBLE IN (1,2,5,6) THEN SECONDS ELSE 0)) / AVG(C.MINUTES_AVAILABLE) DOWNTIME, 100 - SUM(IF(D.FK_RESPONSIBLE IN (1,2,5,6) THEN SECONDS ELSE 0)) / AVG(C.MINUTES_AVAILABLE) UPTIME, AVG(C.MINUTES_AVAILABLE) MINUTOS_DISPONIVEIS FROM TAG_DEBUGGED D INNER JOIN TAG_PATTERN P ON (D.TAGNAME = P.TAGNAME) INNER JOIN CALENDAR C ON (D.DTHIN > C.START AND D.DTHOUT < C.END AND C.SHIFT = D.SHIFT) WHERE D.FK_RESPONSIBLE IN (1,2,3,4) AND PRODUCTIVE = 'S' AND D.AREA_ID IN(1,2, 3) AND D.DTHIN >= '2007-10-08 16:00:00' AND D.DTHOUT <= '2008-02-24 01:30:00' GROUP BY DATA ORDER BY DOWNTIME Tables Involved --------------- TAG_DEBUGGED (D) table opened shared, has 1481 rows TAG_PATTERN (P) table opened shared, has 143 rows CALENDAR (C) table opened shared, has 2193 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more grouped rows Result set will be grouped by the following column(s) using a temporary index: DATA Result set will be ordered by the following column(s) using a case-sensitive temporary index: DOWNTIME ASC WHERE Clause Execution ---------------------- The expression: D.FK_RESPONSIBLE IN (1 , 2 , 3 , 4) AND PRODUCTIVE = 'S' AND D.AREA_ID IN (1 , 2 , 3) AND D.DTHIN >= '2007-10-08 16:00:00' AND D.DTHOUT <= '2008-02-24 01:30:00' has been rewritten and is PARTIALLY-OPTIMIZED, covers 1481 rows or index keys, costs 737241 bytes, and will be applied to the TAG_DEBUGGED table (D) before any joins Join Ordering ------------- The driver table is the TAG_DEBUGGED table (D) The TAG_DEBUGGED table (D) is joined to the TAG_PATTERN table (P) with the INNER JOIN expression: D.TAGNAME = P.TAGNAME The TAG_DEBUGGED table (D) is joined to the CALENDAR table (C) with the INNER JOIN expression: D.DTHIN > C.START AND D.DTHOUT < C.END AND D.SHIFT = C.SHIFT Optimizer will attempt to re-order the joins to a more optimal order Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the optimizer to leave the joins in their declared order The joins are already in optimal order and cannot be optimized any further Join Execution -------------- Costs ARE NOT being taken into account when executing this join Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the optimizer to consider costs when optimizing this join The expression: D.TAGNAME = P.TAGNAME is OPTIMIZED The expression: D.DTHIN > C.START AND D.DTHOUT < C.END AND D.SHIFT = C.SHIFT has been rewritten and is PARTIALLY-OPTIMIZED ================================================================================ >>>>> 6 rows affected in 0,735 seconds ================================================================================ |
Thu, Dec 27 2007 2:21 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roger,
<< More one problem I found in SQL: Field UPTIME, result -499 "%" while I don't use DESC in ORDER BY, but when I use DESC, the result is correct! = 99,93 "%" >> Could you send me the tables that you're using ? -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Dec 27 2007 2:30 PM | Permanent Link |
Roger Oliveira | OK, The tables is in Attach. PS: Please, I need one soluction for this. Roger Oliveira roger@puxtreme.com.br Underline Informática. www.puxtreme.com.br Attachments: Database.rar |
Thu, Dec 27 2007 3:19 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roger,
<< OK, The tables is in Attach. >> Okay, I found the problem and a fix will be available hopefully this week. The issue is the fact that the ORDER BY disrupting the AVG and STDDEV calculations. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |