Re: stupid SQL question

From: Dara Vaughn <dara.vaughn_at_wcom.com>
Date: Wed, 13 Dec 2000 16:28:09 -0600
Message-Id: <10709.124508@fatcity.com>

Greg,

Try this:

select object_type,

            sum(decode(status,'VALID',1,0)) valid,
            sum(decode(status,'INVALID',1,0)) invalid

from user_objects
where object_type = 'VIEW'
group by

           object_type;

Dara Vaughn
Oracle DBA
Quality Software Engineering

• Original Message ----- From: <gregory.t.norris_at_mail.sprint.com> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Wednesday, December 13, 2000 3:37 PM 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 Wed Dec 13 2000 - 16:28:09 CST