# 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
Nobody can help you until you provide your full select statement with error code and error message...

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•