• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

multiple "group by" queries in 8i

 
Ranch Hand
Posts: 925
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 925
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
rewrote it using sum & decode, problem solved.
 
Ranch Hand
Posts: 336
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Simon,
That was a very nice SQL, could you please post your solution?
Thanks,
 
SJ Adnams
Ranch Hand
Posts: 925
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Sure!
 
SJ Adnams
Ranch Hand
Posts: 925
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It's probably more efficient then my first solution too.
Can you figure out how not to use the "least()" bit?
 
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic