CODEWITHSUNDEEP
sqloracle-database
george
george

Reputation: 3211

where if oracle sql clause

I have got the following query

    select
    Case
    -- Here 
    When count(distinct g.mobile_no) > 0 Then con.IR_BILLING_GROUP_CODE = bil.billing_group_code Else g.mobile_no END mobile_no,     
    cu.customer_no, bil.billing_group_code bil
from customers cu
  inner join billings bil on bil.code = cu.code
  inner join mobile_no g on g.mobile_no = bil.mobile_no
  inner join billing_group con on con.code = bil.code

Where it says "Here" above I want to add the following restriction. If from the retrieved mobile numbers there are duplicates (which will be because there are different billing groups in the column bill for the same numbers) then I want to add the restriction con.IR_BILLING_GROUP_CODE = bil.billing_group_code which will return only the relevant billing group and thus the wrong duplicate mobile numbers won't be displayed? The code above says missing keyword?

Example

  Customer    Mobile No    Bil
  123         01243473     02
  123         05532234     02
  123         05532234     05

So because 05532234 has duplicate due to different billing groups then I need to filter on the column bil. If I add the restriction con.IR_BILLING_GROUP_CODE = bil.billing_group_code then either bil = 02 or bil = 05 so that I would know which is the correct billing group for the number 05532234. I hope that makes sense.

Upvotes: 0

Views: 101

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

You cannot place a where clause criteria in your select list. You also cannot use an aggregate function (COUNT) along with non-aggregated columns without grouping.

You can add row numbers to your records per customer and bill where the best match (con.ir_billing_group_code = bil.billing_group_code) comes first. Then filter your result such as to get only the best matching rows, i.e. only row numbers 1.

select customer_no, bil, mobile_no
from
(
  select
    cu.customer_no, bil.billing_group_code bil, g.mobile_no,
    row_number over (partition by cu.customer_no, bil.billing_group_code
                     order by 
                       case when con.ir_billing_group_code = bil.billing_group_code
                         then 0 
                         else 1 
                       end
                    ) as rn
  from customers cu
  inner join billings bil on bil.code = cu.code
  inner join mobile_no g on g.mobile_no = bil.mobile_no
  inner join billing_group con on con.code = bil.code
)
where rn = 1;

Upvotes: 1

Related Questions