| View Creation [message #66466] |
Tue, 25 June 2002 18:30  |
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  |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
