UNION ALL breaks analytics?

From: Jesse, Rich <Rich.Jesse_at_quadtechworld.com>
Date: Fri, 23 Apr 2004 13:27:26 -0500
Message-ID: <FBE1FCA40ECAD41180400050DA2BC54004E938BA@qtiexch2.qgraph.com>

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_at_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_at_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

Received on Fri Apr 23 2004 - 13:24:53 CDT