I'm trying to come up with an MIS query to summarise the number of updates to a set of rows in the past 3,6 12 months etc. I came up with this:
unfortunatly the 2nd subquery seems to overwrite the 1st subquery results (bug maybe?) can anyone see why this shouldn't work in 8i? I tried it using 9i and the query works fine. i.e. in 8i the last 2 columns have identical results, but in 9i they have different (correct) results. Cheers, Simon
I think the "case" statement could help you out here. Try something along the lines of the following as the basis of your query
You'll probably be able to get rid of the inline view with this approach. Quick notes on case statement. I *believe* it's the ANSI replacement for decode(), if() and so forth. It's been in Oracle since a few versions back. It's *much* more powerful than decode in that it can test for conditions other than equality. The syntax is a bit wonky: case when <condition> then <expression> [else <expression>] end. You can have an arbitray number of when...then...'s too. If the condition evaluates true, then the expression as a whole takes the value of the "then" expression. If the condition evaluates false, then the expression as a whole either takes the default value of null or the value of the optional else expression. In the third case statement above, the "else null" is redundant. I just put it in to help show syntax. In the second case statement above, the 'SimonLee' shows that the "1" in "then 1" is essentially a dummy value and reminds us how count() works. For a given row, count() evaluates to 1 or 0 depending on whether the value inside count is not null or null. So we have to make the case statement produce not null or null. I chose to use "1" for not null as reminder that I'm counting here, but really anything would do. Anyhow, not a single least() used!
Joined: Mar 25, 2001
One other small thing: the distinct() in Simon's solution is redundant, I believe. Any time you have an aggregating function in your select list, everything else is automatically distinct.