I am trying to get the count of distinct c.deal_no and use this value in my further calculations, but I am facing error.
Below is the query:
Select a.Oa_Permid, a.NAME, select count(distinct c.deal_no), round(8*log(1000,count(distinct c.deal_no)),5) as derived_score,
case when count(distinct c.deal_no)=1 then round(8*log(1000,1.5),5)
when count(distinct c.deal_no)>1 then round(8*log(1000,count(distinct c.deal_no)),5) else null end derived_score2
From HAIRY.BE_VIEW@PREQDB A, HAIRY.company_view@PREQDB B, HAIRY.financing_event@PREQDB C, HAIRY.portfolio_status@PREQDB D
Where A.Company_Id=B.Company_Id
And d.in_portfolio='Y'
and a.pubstatus<>'P'
And nvl(a.Oa_Permid,0)<>0
and nvl(rounddate,update_stamp) >= add_months( sysdate, -12*5 )
and count(distinct c.deal_no)>=1
order by count(distinct c.deal_no) desc;
Error encountered:
ORA-00934: group function is not allowed here 00934. 00000 - "group function is not allowed here" *Cause: *Action: Error at Line: 1,541 Column: 646