Home » RDBMS Server » Networking and Gateways » View Creation
View Creation [message #66466] Tue, 25 June 2002 18:30 Go to next message
Simon
Messages: 60
Registered: December 1998
Member
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...

siMon
Re: View Creation [message #66469 is a reply to message #66466] Wed, 26 June 2002 06:52 Go to previous messageGo to next message
Suresh
Messages: 189
Registered: December 1998
Senior Member
instead of count(plain_best) use
count(decode(sign(plain_best-1), 1, 1,0))

use the same code for other two fields plain_normal and plain_draft.

If you have null values for these columns use nvl function also.
Re: View Creation [message #66471 is a reply to message #66466] Wed, 26 June 2002 19:34 Go to previous message
Simon
Messages: 60
Registered: December 1998
Member
Thanks Suresh

it works ... u make my day... i just made a little adjustment to make the constant in the sign function to be minus off by 0.01 intead of 1 so as to fufil the requirement of > 1

thanks a lot
Previous Topic: Re: Can't Install 8i Client on Pentium 4 , NT 4 machine??
Next Topic: Database link
Goto Forum:
  


Current Time: Tue Feb 27 00:06:37 CST 2024