Coalesce thru a hierarchy? 1

[wbodger]

I have a table with a business structure; Branch (required) Warehouse, ProfitCenter and AccountNumber are optional. I will pass in all four values and I want the first record that matches in a specific order:
Branch and AccountNumber
Branch and ProfitCenter
Branch and Warehouse
Branch

So, this rules table may have a record for a specific branch and then an override rule for a specific profitcenter within that branch. Since Branch and ProfitCenter overrides just Branch I want to return the record with Branch and ProfitCenter (there is other data in that row, these are just the criterion I use to identify the row). I have gone around and around with myself how best to accomplish this. I had thought I could do a coalesce in the select statement, something like

coalesce((select mincasecount from tbl where branch=@branch and account=@account),(select mincasecount from tbl where branch=@branch and profitcenter=@profitcenter),(select mincasecount from tbl where branch = @branch and warehouse=@warehouse),(select mincasecount from tbl where branch = @branch))
from tbl
where branch=@branch and (account=@account OR profitcenter=@profitcenter OR warehouse=@warehouse)

but this fails in that it can choose more than one record in the where clause. I need something like

where branch=@branch and coalesce(account=@account,profitcenter=@profitcenter,warehouse=@warehouse)

but I can't find the correct syntax for what I need.

Any thoughts, suggestions or correction?

thanks,
Willie

 

[fredericofonseca]

this should do it based on the little information supplied

select mincasecount
from (
select mincasecount
     , row_number() over(order by case
                                  when account = @account then 1
                                  when profitcenter = @profitcenter then 2
                                  when warehouse = @warehouse then 3
                                  else 4
                                  end
                        ) rownum
from tbl
where branch=@branch and (account=@account OR profitcenter=@profitcenter OR warehouse=@warehouse or 1=1) -- 1=1 used so any record that matches @branch but not the others is selected 
) t
where rownum = 1

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[wbodger]

Nice solution, thank you!