| subquery [message #4463] |
Wed, 11 December 2002 15:21  |
yen chew
Messages: 6
Registered: May 2001
|
Junior Member |
|
|
can anyone point out my mistake?
It kept telling me there is an issues with line 10.
select
a.system_source_code,
a.account_no,
a.oli_no,
a.customer_no,
a.service_sequence_no,
a.sfy_ms_name,
a.user_name,
a.start_date,
a.end_date,
(usagetime/60/60) as hours
from
(
select
a.system_source_code,
a.account_no,
a.oli_no,
a.customer_no,
a.service_sequence_no,
a.sfy_ms_name,
a.user_name,
a.start_date,
a.end_date,
sum(b.usagesec)as usagetime
from
dssview.product_events a,
um_pop_user_daily_summ@emerald.net b
where a.registration_no in ('11705','13335','13617')
and a.user_name like 'mojo814'
and (sysdate-a.start_date >30 and sysdate-a.start_date<=60)
and b.usage_date >=start_date
group by
a.system_source_code,
a.account_no,
a.oli_no,
a.customer_no,
a.service_sequence_no,
a.sfy_ms_name,
a.user_name,
a.start_date,
a.end_date,
b.usagesec)
|
|
|
|
| Re: subquery [message #4464 is a reply to message #4463] |
Wed, 11 December 2002 15:54  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
For one thing, either drop the a. alias references in the outer select, or add an a alias to the inline view.
Really, though, you don't need the inline view. Just apply the calc (/60/60) to the usagetime column (sum).
|
|
|
|
| Re: subquery [message #4470 is a reply to message #4463] |
Thu, 12 December 2002 09:12 |
yen chew
Messages: 6
Registered: May 2001
|
Junior Member |
|
|
Thanks Todd for the advise, that did help and not getting an error.
What if I try to get the sum of usagesec on a monthly increment since his "start_date"? Is that possible?
For example, if he starts his service in 7/17/2002, I would like to get the total usagesec from that date till 08/17/2002.
Please advise.
Thanks.
Yen
|
|
|
|
| Re: subquery [message #4473 is a reply to message #4470] |
Thu, 12 December 2002 11:34 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
You would have to have a WHERE clause to restrict to that date range and then group the results by the customer and to_char(date_column, 'yyyymm').
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
