CODEWITHSUNDEEP
sqlteradata
user3901666
user3901666

Reputation: 409

order analytical functions not allowed in where clause

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions