Hello,
I know how to solve it in two query's but I want to merge this in one query.
I have records in a table with the status 1. I have also records in the table with the status 2 and 3. But the records of 2 and 3 can have a different period so from these must be first determined which period these are, drawback to a month by deviding through 3 or 12.
Double nesting is not allowed with Oracle, I have detected. There can be more than 1 record with the status 2 or 3 and I want to accumulate these and return as a sum value.
select clk.clickid
,clk.uidclickstatus
,clk.period
,clk.STARTTIME
,clk.stoptime
,clk.CREATETIME
,clk.VOLUME "Open click volume"
,sum(tot_volume) --This doesn't work and is my problem!
,(select case
when ec.period = 'M' then
ec.volume
when ec.period = 'Q' then
ec.volume / 3
when ec.period = 'Y' then
ec.volume / 12
else
0
end as tot_volume
from click ec
where ec.uidcontract = clk.uidcontract --807430
and ec.starttime = clk.starttime --Maybe the good reader will be saying why are you deviding, this is now directly related to keep it simple for now
and ec.stoptime = clk.stoptime --Maybe the good reader will be saying why are you deviding, this is now directly related to keep it simple for now
and ec.uidclickstatus in (2, 3)
) sourced_volume
from click clk
,lscmcontract ctr
where clk.UIDCONTRACT = ctr.UIDCONTRACT
and ctr.UIDCONTRACT = 807430
and clk.uidclickstatus = 1
order by clk.uidclickstatus asc, clk.period, clk.starttime
;
Can someone help me how to solve this?
I'm not a guru on SQL. Straight query's are not a problem but the what advanced...
Thanks
Nico