From oracle-l-bounce@freelists.org Fri Apr 23 13:24:53 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i3NIORu04193 for ; Fri, 23 Apr 2004 13:24:38 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i3NIOH604168 for ; Fri, 23 Apr 2004 13:24:27 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D7AAD72CE8F; Fri, 23 Apr 2004 13:16:32 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 30433-41; Fri, 23 Apr 2004 13:16:32 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1515572DB4D; Fri, 23 Apr 2004 13:16:32 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 23 Apr 2004 13:15:19 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id AC1D172C9AF for ; Fri, 23 Apr 2004 13:15:18 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 30021-18 for ; Fri, 23 Apr 2004 13:15:18 -0500 (EST) Received: from qescan1.qgraph.com (QESCAN2.qgraph.com [206.158.124.8]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 688CB72C7DF for ; Fri, 23 Apr 2004 13:15:18 -0500 (EST) Received: by sxsmtp1.qgraph.com with Internet Mail Service (5.5.2657.72) id ; Fri, 23 Apr 2004 13:27:25 -0500 Message-ID: From: "Jesse, Rich" To: "ORACLE-L (E-mail)" Subject: UNION ALL breaks analytics? Date: Fri, 23 Apr 2004 13:27:26 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2657.72) Content-type: text/plain; charset=iso-8859-1 X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 3673 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Rich.Jesse@quadtechworld.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org In 8.1.7.4.0 on HP-UX 11.0, I've got this table: TX_COUNT NUMBER TIMESTAMP DATE (thanks Oracle for making "TIMESTAMP" a reserved word in 9i, despite it being used as column names in the DD!) It contains several thousand rows of data. The TX_COUNT continually increases unless the DB was down. In that case, the number starts back at 1. I want to capture that event, so I wrote this: SELECT TX FROM ( SELECT (QTL.TX_COUNT - LAG(QTL.TX_COUNT) OVER (ORDER BY QTL.TIMESTAMP)) "TX" FROM QT_TRANSACTION_LOG QTL WHERE QTL.TIMESTAMP >= TRUNC(SYSDATE) - 365 ) WHERE TX < 0 The subquery is necessary, but I don't think it's pertinent to this question. Anyway, this works, giving the following four rows: -14291798 -9439049 -7392918 -10123545 I've been experimenting, and decided to try and fudge a row of data. Since I don't want to fudge the table, I tried this: SELECT TX FROM ( SELECT (QTL.TX_COUNT - LAG(QTL.TX_COUNT) OVER (ORDER BY QTL.TIMESTAMP)) "TX" FROM QT_TRANSACTION_LOG QTL WHERE QTL.TIMESTAMP >= TRUNC(SYSDATE) - 365 UNION ALL SELECT -1 FROM DUAL ) WHERE TX < 0 But all I get back is a single row: -1 What happened? I expected five rows instead of one. Because I'm using this as a subquery, moving the UNION to the outside query won't work. Slightly confused... Rich Rich Jesse System/Database Administrator rich.jesse@quadtechworld.com QuadTech, Sussex, WI USA ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------