COALESCE returns the first usable value from an ordered set of expressions, falling back when an earlier expression is NULL. It is useful when a query combines optional data from related tables but the API or UI still needs a predictable field. section 10.5.1 section 11.6.1
Keep a company name available
An ACH batch can belong to a company absent from the companies table. COALESCE(c.name, abh.company_name, '') first uses the stored company name, then the batch-header name, and finally an empty string. A LEFT JOIN permits the missing company row without losing the batch result. section 10.5.1
Listing 10.30 SQL query
SELECT art1.ach_files_id AS file_id,
art5.ach_records_type_5_id AS batch_header_id,
af.file_name AS filename,
COALESCE(c.name, abh.company_name, '') AS company_name, #1
abh.company_identification AS company_identification,
abcr.total_credit_entry_dollar_amount
AS total_credit_entry_dollar_amount,
abcr.total_debit_entry_dollar_amount
AS total_debit_entry_dollar_amount,
abcr.entry_addenda_count AS entry_addenda_count
FROM ach_files af
INNER JOIN ach_records_type_1 art1 USING ( ach_files_id )
INNER JOIN ach_records_type_5 art5 USING ( ach_records_type_1_id )
INNER JOIN ach_records_type_8 art8 USING ( ach_records_type_5_id )
INNER JOIN ach_batch_headers abh USING ( ach_records_type_5_id )
INNER JOIN ach_batch_control_records abcr USING ( ach_records_type_8_id )
LEFT JOIN companies c ON #2
abh.company_identification = c.ach_company_id #2
The query shows COALESCE in a search response assembled from ACH file, record, batch-header, and company data. section 10.5.1
The fallback value can support a downstream interface decision. When exception details always return at least an empty company-name string, the modal can show the Company Name label only when that value is not empty. This differs from the UI nullish-coalescing operator (??), which supplies default recovery text separately. section 10.7.3
Read fields from alternative batch headers
A batch header ID exists in only one of two header tables. COALESCE can select the ordinary batch-header field or, when it is NULL, the IAT batch-header field for company identification, batch number, and ordering. This relies on the participating fields remaining NOT NULL when their respective header exists. section 11.6.1
Listing 11.19 Updated get_batches
SELECT COALESCE(abh.company_name, '') AS company_name, #1
COALESCE(abh.company_identification, #2
aibh.originating_dfi_identification) AS company_id,
art5.ach_records_type_5_id AS id,
COALESCE(abh.batch_number, aibh.batch_number) #3
AS batch_number, #4
abcd.total_debit_entry_dollar_amount AS debit_total,
abcd.total_credit_entry_dollar_amount AS credit_total,
abcd.entry_addenda_count
FROM ach_files AS af
…
LEFT JOIN ach_batch_headers AS abh #4
USING (ach_records_type_5_id) #4
LEFT JOIN ach_iat_batch_headers AS aibh #4
USING (ach_records_type_5_id) #4
WHERE af.ach_files_id = %s
ORDER BY COALESCE(abh.originating_dfi_identification, #5
aibh.originating_dfi_identification); #5
The updated batch query uses COALESCE across ordinary and IAT header tables while retaining the existing endpoint shape. section 11.6.1
