Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> UNION ALL breaks analytics?
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 USAPlease see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
![]() |
![]() |