RE: stupid SQL question - SOLVED

--openmail-part-1775b517-00000001

Content-Type: text/plain; charset=US-ASCII Content-Disposition: inline

        ;Creation-Date="Thu, 14 Dec 2000 08:47:14 -0600" Content-Transfer-Encoding: 7bit

Kudos to Dara Vaughn and Michael for showing me that I was trying to make this *way* to complicated. The solution was, of course, to use sum(decode(...)) with group by.

Thanx much!

-----Original Message-----

From: gregory.t.norris [mailto:gregory.t.norris_at_mail.sprint.com] Sent: Wednesday, December 13, 2000 3:37 PM To: ORACLE-L; gregory.t.norris
Subject: stupid SQL question

I'm trying to determine object totals, along with the breakdown of valid/invalid. No problem so far, but I'd like to format the report neatly by getting all three counts on the same line. Is there any way to do this using straight SQL, or should I give up and use PL/SQL instead? I've seen similar things done by giving a table multiple aliases in the query, but can't seem to make it work here. Suggestions and/or pointers would be most appreciated.

Here's an (obviously) broken example, just in case I wasn't clear above...

   select count(a.status), count(b.status), count(c.status)

      from user_objects a, user_objects b, user_objects c
      where a.object_type = 'VIEW'
        and b.object_type = 'VIEW'
        and c.object_type = 'VIEW'
        and b.status = 'VALID'
        and c.status = 'INVALID'

Thanx!

--

My employers like me, but not enough to let me speak for them.

Greg Norris
Sprint LTD Database Administration
Internet: gregory.t.norris_at_mail.sprint.com Received on Thu Dec 14 2000 - 08:47:14 CST