aspose file tools*
The moose likes Oracle/OAS and the fly likes multiple Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "multiple "group by" queries in 8i" Watch "multiple "group by" queries in 8i" New topic
Author

multiple "group by" queries in 8i

SJ Adnams
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
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
SJ Adnams
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
rewrote it using sum & decode, problem solved.
Leslie Chaim
Ranch Hand

Joined: May 22, 2002
Posts: 336
Simon,
That was a very nice SQL, could you please post your solution?
Thanks,


Normal is in the eye of the beholder
SJ Adnams
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
Sure!
SJ Adnams
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
It's probably more efficient then my first solution too.
Can you figure out how not to use the "least()" bit?
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1749
    
    2
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!
Michael Matola
whippersnapper
Ranch Hand

Joined: Mar 25, 2001
Posts: 1749
    
    2
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: multiple "group by" queries in 8i