Home » SQL & PL/SQL » SQL & PL/SQL » View Creation
View Creation [message #20853] Tue, 25 June 2002 18:33 Go to next message
Messages: 60
Registered: December 1998
I am trying to create this view from 3 tables... but i only have problem with a particular table.... "usgmgr.media_tbl" where the 3 fields (number fields)plain_best, plain_normal, plain_draft may sometimes have values less than 1. but they may not be less than 1 all together in a tuple. sometimes plain_best > 0 and the rest < 1 and so on....
looking at the buggy view that i am stuck with now:

create or replace view media_usage_view as
select printer_cd, m.year, q.quarter, count(plain_best) plain_best, count(plain_normal) plain_normal, count(plain_draft) plain_draft
from usgmgr.media_tbl m, valid_cust_view v, usgmgr.quarter_ref_tbl q
group by printer_cd, m.year, q.quarter

is there anyway i can count the plain_best, plain_normal and plain_draft only if their values are more than 0??

thanks a million in advance...

Re: View Creation [message #20857 is a reply to message #20853] Tue, 25 June 2002 23:27 Go to previous messageGo to next message
Messages: 85
Registered: October 2000
Try using where cluase as
where plain_best>1 or plain_normal>1 or plain_draft>1
in your create view command
Re: View Creation [message #20869 is a reply to message #20853] Wed, 26 June 2002 13:21 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
try adding:
having count(plain_best) + count(plain_normal) + count(plain_draft) > 1
Previous Topic: how to uninstall OS
Next Topic: Can I do this with triggers?
Goto Forum:

Current Time: Tue Oct 22 18:36:26 CDT 2019