Oracle SQL

[cassie99]

Hello.

Help. I cannot figaure out what is wrong with this SQL.
I am trying to display an average cycle time per site.
I end up getting the same result for all sites.

Thanks in advance.

-Cassie

select SITE, AVG(CycleTime) as AVG_CT
from
(select SITE, ROUND(DEC_DATE-VIS_DATE,2) as CycleTime
from
(select PROJ, SITE, BSLN, VIS_NUM,
VIS_DATE,max(EFF_DATE) as DEC_DATE
from
(select vm.proj_id as PROJ,
pi.waes_site_cd as SITE,
vm.bsln_nmbr as BSLN,
vm.actl_visit_nmbr as VIS_NUM,
vm.eff_dt as EFF_DATE,
av.visit_dt as VIS_DATE, vm.mlstn_type as MILESTN
from visit_milestones vm,
actual_visits av, primary_investigators pi
where vm.proj_id = av.proj_id
and vm.bsln_nmbr = av.bsln_nmbr
and vm.actl_visit_nmbr = av.actl_visit_nmbr
and av.proj_id = pi.proj_id
and vm.proj_id = 10999099
and vm.eff_dt between '01-SEP-2004'
and '30-SEP-2004'
and UPPER(vm.mlstn_type)='MILESTONE ABC')
group by PROJ, SITE, BSLN, VIS_NUM, VIS_DATE))
group by SITE

 

[SantaMufasa]

Cassie,

Obviously, if you are receiving results at all, you do not have a syntax problem. If your results are not what you expected then you have a logic issue. Although I am not familiar with your data, I am a bit puzzled by your two levels of "GROUP BY". So, as an experiment, could you try your code again, with this slight modification of your code: remarking out the "group by PROJ, SITE, BSLN, VIS_NUM, VIS_DATE" clause as below:

select SITE, AVG(CycleTime) as AVG_CT 
from
 (select SITE, ROUND(DEC_DATE-VIS_DATE,2) as CycleTime
  from
    (select PROJ, SITE, BSLN, VIS_NUM, 
         VIS_DATE,max(EFF_DATE) as DEC_DATE
     from
        (select vm.proj_id as PROJ, 
         pi.waes_site_cd as SITE, 
     vm.bsln_nmbr as BSLN, 
         vm.actl_visit_nmbr as  VIS_NUM, 
     vm.eff_dt as EFF_DATE,
     av.visit_dt as VIS_DATE, vm.mlstn_type as MILESTN
     from visit_milestones vm, 
         actual_visits av,  primary_investigators pi
     where vm.proj_id = av.proj_id
     and vm.bsln_nmbr = av.bsln_nmbr 
     and vm.actl_visit_nmbr = av.actl_visit_nmbr 
     and av.proj_id = pi.proj_id 
     and vm.proj_id = 10999099
     and vm.eff_dt between '01-SEP-2004' 
         and '30-SEP-2004'
     and UPPER(vm.mlstn_type)='MILESTONE ABC')
    -- group by PROJ, SITE, BSLN, VIS_NUM, VIS_DATE
    ))  
group by SITE;

Let us know the outcome.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 02:13 (21Oct04) UTC (aka "GMT" and "Zulu"),
@ 19:13 (20Oct04) Mountain Time

 

[cassie99]

Hello SantaMufasa,

I receive the following message:
ORA-00937: not a single-group group function

-Cassie

 

[SantaMufasa]

Cassie,

My bad (plus apologies)...I overlooked the "max(EFF_DATE)" expression in the middle SELECT, which turns that SELECT into a GROUP-expressions-only SELECT. So, go ahead and un-comment the GROUP BY at this point.

Are there too many rows to post the results of your query here? Have you hand-checked your data to know how far off your results are? If I were in your position, if the volume of data is not prohibitive, I would start executing your SELECT statement from the inner-most outward to confirm the validity of the intermediate results until you isolate the problem.

Please post your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 06:14 (21Oct04) UTC (aka "GMT" and "Zulu"),
@ 23:14 (20Oct04) Mountain Time

 

[cassie99]

Santa M...

Mission accomplished. I had an issue with one of the joins between two views. Thanks.

-Cassie