Is there a better way of creating this case query

I'm creating a case subquery that does work, however I am sure there must be an easier way of doing this?

The aim of the query is when billgrp_desc like '30%', then display billgrp_desc from tbm.billgrp table.

Otherwise display matter_code from hbm.matter table when billgrp_desc not like '30%'

The query is below:

select 
 case
    when bllgrp开发者_C百科_desc like '30%' then 'billgrp_desc'
 end
from tbm.billgrp
union 
select 
 case
    when exists (select billgrp_desc
                 from tbm.billgrp
                 where billgrp_desc not like '30%') then 'matter_code'
  end
from hbm.matter

I would probably go this route:

select 
 case
    when bllgrp_desc like '30%' then 'billgrp_desc'
    else 'matter_code'
 end
from tbm.billgrp

Since you are not selecting anything from the matter table I don't see a reason to query it... The is also no reason to union that I can see. Just select all the rows you want the first time.

Try this, I am assuming Matter_code is a field from the matter table

select 
    case
        when bllgrp_desc like '30%' then billgrp_desc
     end
from tbm.billgrp
union 
    select 
           isNull(gp.billgrp_desc,mt.matter_code)
    from hbm.matter mt
    left join billgrp gp on billgrp_desc not like '30%'