# Thread: AGGREGATE FUNCTION TO BE USED ONLY IN SEARCH CRITERIA (where clause)

1. Registered User
Join Date
May 2015
Posts
6

## Unanswered: AGGREGATE FUNCTION TO BE USED ONLY IN SEARCH CRITERIA (where clause)

INPUT

Clabcd no C_DISP C_TYPE A_AMT B_AMT
222222222222200 1 1 50 40
222222222222201 4 1 -20 -10
3333333333333300 2 2 12 22
3333333333333302 3 2 -10 -15

Input comes as 15 digits for claim no but we take only the first 13 digits SUBSTR(Clabcd no , 1, 13)
For C_TYPE =1, A_AMT = 50 – 20 = 30 which is > 0 so it it displayed in the output ( for SUBSTR(Clabcd no , 1, 13) = 2222222222222)
For C_TYPE = 2 (C_TYPE other than 1), B_AMT= 22-15 = 7 which is > 0 so it is displayed in the output ( for SUBSTR(Clabcd no , 1, 13)= 33333333333333)

OUTPUT

SUBSTR(Clabcd no , 1, 13) C_DISP C_TYPE AMOUNT_PAID
2222222222222 1 1 50
2222222222222 4 1 -20
33333333333333 2 2 22
33333333333333 3 2 -15

Input has 4 records, SUM should be done on the 2 fields and output should display all the 4 records.
How do i use aggreagte function in the search criteria?
Subquery can be used i guess but since 2 amount fields are used its not possible. and also subquery causes performance issue.

My query already has 2 left outer joins and also one union. This is an additional change

Any help is appreciated. Thanks

2. Registered User
Join Date
Jul 2013
Location
Moscow, Russia
Posts
666
Code:
```with a (Clabcd_no, C_DISP, C_TYPE, A_AMT, B_AMT) as (values
('222222222222200', 1, 1, 50, 40)
, ('222222222222201', 4, 1, -20, -10)
, ('3333333333333300', 2, 2, 12, 22)
, ('3333333333333302', 3, 2, -10, -15)
)
, b as (
select a.*, sum(case c_type when 1 then a_amt else b_amt end) over (partition by SUBSTR(Clabcd_no , 1, 13), c_type) s_amt
from a
)
select SUBSTR(Clabcd_no , 1, 13), C_DISP, C_TYPE, case c_type when 1 then a_amt else b_amt end as AMOUNT_PAID
from b
where s_amt>0```

3. Registered User
Join Date
May 2015
Posts
6
Thanks Mark. But the tables have millions of data.
could you please tell me how i can modify the below code?

with a (Clabcd_no, C_DISP, C_TYPE, A_AMT, B_AMT) as (values
('222222222222200', 1, 1, 50, 40)
, ('222222222222201', 4, 1, -20, -10)
, ('3333333333333300', 2, 2, 12, 22)
, ('3333333333333302', 3, 2, -10, -15)
)

4. Registered User
Join Date
Jul 2013
Location
Moscow, Russia
Posts
666
values (...), ..., (...)

5. Registered User
Join Date
May 2015
Posts
6
Thanks Mark. will try it out

6. Registered User
Join Date
May 2015
Posts
6
WITH A (cola, colb, colc...) AS

SELECT cola, colb, colc....FROM TABLE

, B as (select A.*, sum(case c_typ when 1 then a_amt else b_amt end) over (partition by SUBSTR(clabcdno , 1, 13), c_typ) AMOUNT_PAID from A )

The above i tried but doesnt seem to work. could you help

7. Registered User
Join Date
Jul 2013
Location
Moscow, Russia
Posts
666
WITH A (Clabcd_no, C_DISP, C_TYPE, A_AMT, B_AMT) AS (

SELECT cola, colb, colc....FROM TABLE

), B as (select A.*, sum(case c_type when 1 then a_amt else b_amt end) over (partition by SUBSTR(clabcdno , 1, 13), c_type) AMOUNT_PAID from A )
...

8. Registered User
Join Date
May 2015
Posts
6
That doesnt work too.

9. Registered User
Join Date
Jul 2013
Location
Moscow, Russia
Posts
666