Results 1 to 9 of 9
  1. #1
    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. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    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
    Regards,
    Mark.

  3. #3
    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. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Insert your select statement returning millions of data instead of:
    values (...), ..., (...)
    Regards,
    Mark.

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

  6. #6
    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. #7
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    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 )
    ...
    Regards,
    Mark.

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

  9. #9
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Nobody can help you until you provide your full select statement with error code and error message...
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

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