CODEWITHSUNDEEP
sqlhive
sue
sue

Reputation: 13

Error: Select distinct with analytic function

Here is my query.

Can anyone help me why I am getting this error, and it can be fixed?

with cte_add as (
select m.*, case when 
round((m.tot_fee-m.fee_threshold),2)>0) then 1 else 0
end as account_flag
from 
(select distinct
a.report_date,
a.orgn_acct,
a.open_date,
b.fee_threshold,
c.fee_amt,
c.fee_type,
sum(nvl(c.fee_amt,0)) over (partition by a.orgn_acct) as tot_fee
from cte_base a
left join cte_min_credit b
on a.orgn_acct=b.orgn_acct
left join(select * from cte_combined_fees
where fee_type in('annual','balance transfer'))c
on a.orgn_acct=c.orgn_acct )m
where (case when round(m.tot_fee-m.fee_threshold),2)>0 then 1 else 0)=1)

Upvotes: 0

Views: 205

Answers (1)

Koushik Roy
Koushik Roy

Reputation: 7387

distinct can not be used with analytic function. So, create a subquery with analytic func sum() over (partition by order by) and then do a distinct.

Sample SQL would be

select distinct col1,col2,..., sm
from 
(select col1,col2,..., sum() over(partition by col3) sm from mytable) rs

Now, i did some change to your SQL and here it is.


with cte_add as (
select m.*, case when 
round((m.tot_fee-m.fee_threshold),2)>0) then 1 else 0
end as account_flag

from 

( select 
m.report_date,
m.orgn_acct,
m.open_date,
m.fee_threshold,
m.fee_amt,
m.fee_type,
m.tot_fee
from 
(select 
a.report_date,
a.orgn_acct,
a.open_date,
b.fee_threshold,
c.fee_amt,
c.fee_type,
sum(nvl(c.fee_amt,0)) over (partition by a.orgn_acct) as tot_fee
from cte_base a
left join cte_min_credit b on a.orgn_acct=b.orgn_acct
left join(select * from cte_combined_fees where fee_type in('annual','balance transfer'))c on a.orgn_acct=c.orgn_acct )m)m

where (case when round(m.tot_fee-m.fee_threshold),2)>0 then 1 else 0)=1)

Upvotes: 1

Related Questions