Reputation: 409
I am trying to achieve the below but facing error like order analytical functions not allowed in where clause
select a.araccno,a.ARBDTE,
sum(aramt) over( partition by araccno,ARBDTE ORDER BY ARAMT asc) sum1
,sum(b.Bill_Stmt_Discount_Amt) over( partition by b.bill_statement_id order by b.Bill_Stmt_Discount_Amt asc) sum2
from stg.ars_etl a
left outer join tgt.bill_sta_dis b
on 'LL'||a.araccno||a.arbdte=b.bill_statement_id
where sum1<> sum2
How can i achieve this in Teradata?
Thanks a lot!!
Upvotes: 1
Views: 12215
Reputation: 1271231
In most databases, you would use a subquery or CTE. Teradata has the qualify
clause:
select a.araccno, a.ARBDTE,
sum(aramt) over( partition by araccno,ARBDTE ORDER BY ARAMT asc) as sum1,
sum(b.Bill_Stmt_Discount_Amt) over (partition by b.bill_statement_id order by b.Bill_Stmt_Discount_Amt asc) as sum2
from stg.ars_etl a left outer join
tgt.bill_sta_dis b
on 'LL'||a.araccno||a.arbdte = b.bill_statement_id
qualify sum1 <> sum2;
Upvotes: 8