How to nest two SQL statements

Question

How do I nest these if statements to return a single dataset where chargeType is determined by the description. This is an ETL script to take from source and map various descriptions to an optionset the destination system expects.

if (invoice_detail.description like 'Net Rental' or invoice_detail.description like 'Balance of Minimum Guarantee') 

    Select
        id.code_invoice,
        'License Fee' as ChargeType,
        id.amount,
        id.vat_amount,
        id.original_amount,
        id.original_amount - id.original_vat_amount as netTotal
        
        from invoice_detail id order by code_invoice desc;
         
        
        
if  (invoice_detail.description like 'FBM Download')

    Select
        id.code_invoice,
        'DLM' as ChargeType,
        id.amount,
        id.vat_amount,
        id.original_amount,
        id.original_amount - id.original_vat_amount as netTotal
        
        from invoice_detail id order by code_invoice desc;

Example Data

invoiceNumber	Description	ChargeType
123	net rental	License Fee
456	FBM Download	DLM
789	Balance of Minimum Guarantee	Licensee Fee

Answer 1

Try this below 
SELECT
    id.code_invoice,
    CASE
        WHEN id.description LIKE 'Net Rental' OR id.description LIKE 'Balance of Minimum Guarantee' THEN 'License Fee'
        WHEN id.description LIKE 'FBM Download' THEN 'DLM'
        ELSE 'Other' 
    END AS ChargeType,
    id.amount,
    id.vat_amount,
    id.original_amount,
    id.original_amount - id.original_vat_amount as netTotal
FROM
    invoice_detail id
ORDER BY
    code_invoice DESC;

Answer 2

You can merge them like this:

Select
    id.code_invoice,
    CASE WHEN id.description in ('Net Rental', 'Balance of Minimum Guarantee') 
             THEN 'License Fee'
         WHEN id.description = 'FBM Download' 
             THEN 'DLM' END  as ChargeType,
    id.amount,
    id.vat_amount,
    id.original_amount,
    id.original_amount - id.original_vat_amount as netTotal
    
from invoice_detail id 
where id.descriptoin in ('Net Rental', 'Balance of Minimum Guarantee', 'FBM Download')
order by code_invoice desc;

It works because the queries themselves not only have the same columns, but are also built nearly exactly the same way.

If you have two queries that have the same columns but are built less similarly, you may need to use a UNION (or UNION ALL), like this:

SELECT *
FROM 
(
    Select
        id.code_invoice,
        'License Fee' as ChargeType,
        id.amount,
        id.vat_amount,
        id.original_amount,
        id.original_amount - id.original_vat_amount as netTotal
        
    from invoice_detail id 
    where id.description in ('Net Rental' , 'Balance of Minimum Guarantee')
         
    UNION   

    Select
        id.code_invoice,
        'DLM' as ChargeType,
        id.amount,
        id.vat_amount,
        id.original_amount,
        id.original_amount - id.original_vat_amount as netTotal
        
    from invoice_detail id
    where id.description = 'FBM Download'
) t
ORDER BY code_invoice DESC

A final option is joining to a table-value constructor expression:

Select
    id.code_invoice,
    map.ChargeType,
    id.amount,
    id.vat_amount,
    id.original_amount,
    id.original_amount - id.original_vat_amount as netTotal
    
from invoice_detail id 
inner join ( VALUES
        ('Net Rental', 'License Fee'), 
        ('Balance of Minimum Guarantee', 'License Fee'),
        ('FBM Download', 'DLM')
    ) map(description, ChargeType) on id.description like map.description
order by id.code_invoice desc;

I like this last version because it is easily extensible with more mapping values, even up to adapting it to use a real table (with indexes!) maintainable by someone who doesn't command an engineer's salary.