Complex query - IF ELSE or CASE or both (Replicate part of ETL process)

Not sure how to apporach this query (The aim of this query is to replicate part of an ETL process, thus validating it):

When a bill_type = C or M and payer type = C (client) or S (Subsidiary) or T (third party)

The payer type is defined as follows:

If BLT_BILLP.PAYR_CLIENT_UNO <> HBM_MATTER.CLIENT_UNO for Lead Matter 
and TBM_MATTER._HS_3PTY = 0 Then value = S ('Subsidiary')

If BLT_BILLP.PAYR_CLIENT_UNO <> HBM_MATTER.CLIENT_UNO for Lead Matter 
and TBM_MATTER._HS_3PTY = 1 Then value = T ('Third Party')

If BLT_BILLP.PAYR_CLIENT_UNO = HBM_MATTER.CLIENT_UNO for Lead Matter 
Then value = C (    'Client')

and

BLT_BILL._HS_CNTYPE = blank 

Then the transaction code = BC or BS or BT.

A case query would be best:

   SELECT CASE BILL_TYPE
          WHEN 'C' THEN 'BC'
          END 
   FROM DBO.SRC_BLT.BILL

Below is my attempt to create a query, is there a better way of doing this:

     SELECT CASE BILL_TYPE 
    WHEN  'C' THEN 
                    (CASE      
                        WHEN DBO.SRC_BLT_BILLP.PAYR_CLIENT_UNO <> DBO.SRC_HBM_MATTER.CLIENT_UNO THEN           
                            CASE DBO.SRC_TBM_MATTER._HS_3PTY             
                                WHEN 0 then 'S'             
                                WHEN 1 then 'T'
                            END 
                        WHEN DBO.SRC_BLT_BILLP.PA开发者_开发问答YR_CLIENT_UNO = DBO.SRC_HBM_MATTER.CLIENT_UNO THEN 'C'
                    END)

     WHEN  'M' THEN 
                    (CASE      
                        WHEN DBO.SRC_BLT_BILLP.PAYR_CLIENT_UNO <> DBO.SRC_HBM_MATTER.CLIENT_UNO THEN           
                            CASE DBO.SRC_TBM_MATTER._HS_3PTY             
                                WHEN 0 then 'S'             
                                WHEN 1 then 'T'
                            END 
                        WHEN DBO.SRC_BLT_BILLP.PAYR_CLIENT_UNO = DBO.SRC_HBM_MATTER.CLIENT_UNO THEN 'C'
                    END)

     END AS TRANS_CODE              


     FROM DBO.SRC_BLT_BILLP, DBO.SRC_HBM_MATTER, DBO.SRC_TBM_MATTER, DBO.SRC_BLT_BILL

     AND WHERE _HS_CNTYPE = ''

I'd say neither. I think "procedural" when I see "if" or "case". SQL works best when it's set-based and declarative.

Unless this is a stored procedure, I'd rework this to eliminate "if" and "case". Just my opinion.

If there are disparate data sets for each bill and player type, maybe you can access them as views and eliminate this logic from your queries.

A slightly more DRY approach to the CASE clause:

 SELECT CASE 
            WHEN BILL_TYPE IN ('C','M') THEN 
                CASE      
                    WHEN DBO.SRC_BLT_BILLP.PAYR_CLIENT_UNO <> DBO.SRC_HBM_MATTER.CLIENT_UNO THEN           
                        CASE DBO.SRC_TBM_MATTER._HS_3PTY             
                            WHEN 0 then 'S'             
                            WHEN 1 then 'T'
                        END 
                    WHEN DBO.SRC_BLT_BILLP.PAYR_CLIENT_UNO = DBO.SRC_HBM_MATTER.CLIENT_UNO THEN 'C'
                 END
         END AS TRANS_CODE

...