CODEWITHSUNDEEP
sqlamazon-redshift
Eugene Sukh
Eugene Sukh

Reputation: 2737

Rewrite CASE to COALESCE

I have part of code in my query , that have CASE, and I want to rewrite it to COALESCE

Here is original part of code

CASE WHEN u.ClientCharges IS NULL THEN 0 ELSE u.ClientCharges END -
                            CASE WHEN LAGClientCharges IS NULL THEN 0
                            ELSE LAGClientCharges
                            END AS my_Delta,

    CASE WHEN (CASE WHEN u.ClientCharges IS NULL THEN 0 ELSE u.ClientCharges END - CASE WHEN LAGClientCharges IS NULL THEN 0
                            ELSE LAGClientCharges END) > 0 THEN 'Gain'
         WHEN CASE WHEN u.ClientCharges IS NULL THEN 0 ELSE u.ClientCharges END - CASE WHEN LAGClientCharges IS NULL THEN 0
                            ELSE LAGClientCharges END < 0 THEN 'Loss'
                            ELSE NULL END AS GainsLosses,
    CASE WHEN u.ClientCharges >=0 AND LAGClientCharges IS NULL AND t.MonthBilled <> '2015-01-01' THEN 'New' ELSE 'Existing' END AS ClientType,

    CASE WHEN u.ClientCharges IS NULL THEN 'InActive' ELSE 'Active' END AS ActiveStatus,

    CASE WHEN LAGClientCharges IS NOT NULL AND t.MonthBilled <> '2015-01-01' THEN 1 ELSE NULL END AS ActiveLastMonth,

    CASE WHEN u.ClientCharges IS NULL AND (LAGClientCharges <> 0 AND LAGClientCharges IS NOT NULL) AND t.MonthBilled <> '1/1/2015' THEN 1 ELSE NULL END AS OneMonthChurn,

    u2.FeatureKeyCount,

    CASE WHEN u.ClientCharges IS NOT NULL AND u.ClientCharges > 0 THEN 1 ELSE 0 END AS OrgCounter

Here is part , where I rewrite some of CASE statements

    COALESCE(u.ClientCharges, 0) - COALESCE(LAGClientCharges, 0) AS my_Delta,

    CASE WHEN (COALESCE(u.ClientCharges , 0) - COALESCE(LAGClientCharges , 0)) > 0 THEN 'Gain'
         WHEN COALESCE (u.ClientCharges , 0) - COALESCE(LAGClientCharges , 0) < 0 THEN 'Loss'
                            ELSE NULL END AS GainsLosses,
    CASE WHEN u.ClientCharges >=0 AND LAGClientCharges IS NULL AND t.MonthBilled <> '2015-01-01' THEN 'New' ELSE 'Existing' END AS ClientType,

   COALESCE (u.ClientCharges, 'InActive') AS ActiveStatus,

    CASE WHEN LAGClientCharges IS NOT NULL AND t.MonthBilled <> '2015-01-01' THEN 1 ELSE NULL END AS ActiveLastMonth,

    CASE WHEN u.ClientCharges IS NULL AND (LAGClientCharges <> 0 AND LAGClientCharges IS NOT NULL) AND t.MonthBilled <> '2015-01-01' THEN 1 ELSE NULL END AS OneMonthChurn,

    u2.FeatureKeyCount,

    CASE WHEN u.ClientCharges IS NOT NULL AND u.ClientCharges > 0 THEN 1 ELSE 0 END AS OrgCounter

But how I can rewrite rows with AND, or maybe it has some better variant , how to rewrite it?

Upvotes: 0

Views: 96

Answers (1)

Andrew
Andrew

Reputation: 27294

The question appears to be that you are struggling to re-write the logic for the case's that involve AND

CASE WHEN u.ClientCharges IS NOT NULL AND u.ClientCharges > 0 THEN 1 ELSE 0 END AS OrgCounter

It's debatable whether it is clearer for the reader, but you are asking for logic along these lines:

CASE WHEN coalesce(u.ClientCharges,0) > 0 THEN 1 ELSE 0 END AS OrgCounter

This works when the clauses in the AND are using the same field, but for the ones where you are checking multiple different fields, you need to leave them as they are.

A lot of interpretation of the data is going on within this piece of code - in effect business logic is being embedded into the SQL - you may wish to reconsider that.

Upvotes: 2

Related Questions