[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

Use CASE statements to create distinct records based upon values in two fields

Hi,
I have the following data in my table:
Cust_ID      HasMembershipDoc      HasDiscount
50000210                                       HasFlag
50000210      HasDoc      
50000217                                       HasFlag
50000261                                       HasFlag
50000261      HasDoc      
50000270      HasDoc      
50000307                                       HasFlag
50000315                                       HasFlag
50000325                                      HasFlag
50000373      HasDoc      
50000402                                       HasFlag
50000443                                       HasFlag
50000516                                       HasFlag
50000516      HasDoc      

I'm trying to get distinct records per Cust_ID by doing this:
SELECT DISTINCT cust_id
, CASE WHEN "HasMembershipDoc" IS NULL AND "HasDiscount" IS NOT NULL THEN 'DiscountFlag Only'
       WHEN "HasDiscount" IS NULL AND "HasMembershipDoc" IS NOT NULL THEN 'Doc Only'
       WHEN "HasMembershipDoc" IS NOT NULL AND "HasDiscount" IS NOT NULL THEN 'FlagAndDoc'
  END AS "Indicator"
FROM Cust_Discount

but this won't work because the third WHEN statement will never happen.
How do I smerge the duplicates to get one Indicator column per Cust_ID?

Best regards,
wdelaney




0
wdelaney05
Asked:
wdelaney05
2 Solutions
 
SharathData EngineerCommented:
try this.
SELECT DISTINCT cust_id, 
                CASE 
                  WHEN "HasMembershipDoc" IS NULL 
                       AND "HasDiscount" IS NOT NULL THEN 'DiscountFlag Only' 
                  WHEN "HasDiscount" IS NULL 
                       AND "HasMembershipDoc" IS NOT NULL THEN 'Doc Only' 
                  WHEN "HasMembershipDoc" IS NOT NULL 
                       AND "HasDiscount" IS NOT NULL THEN 'FlagAndDoc' 
                END AS "Indicator" 
  FROM (  SELECT Cust_ID, 
                 MAX(HasMembershipDoc) AS HasMembershipDoc, 
                 MAX(HasDiscount)      AS HasDiscount 
            FROM Cust_Discount 
        GROUP BY Cust_ID) AS t1

Open in new window

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
just a small correction to the query given already.

We don't need a unnecessary DISTINCT in the SELECT because that is already taken care in the inside inline view. So i believe this should be fine :

SELECT cust_id,
                CASE
                  WHEN "HasMembershipDoc" IS NULL
                       AND "HasDiscount" IS NOT NULL THEN 'DiscountFlag Only'
                  WHEN "HasDiscount" IS NULL
                       AND "HasMembershipDoc" IS NOT NULL THEN 'Doc Only'
                  WHEN "HasMembershipDoc" IS NOT NULL
                       AND "HasDiscount" IS NOT NULL THEN 'FlagAndDoc'
                END AS "Indicator"
  FROM (  SELECT Cust_ID,
                 MAX(HasMembershipDoc) AS HasMembershipDoc,
                 MAX(HasDiscount)      AS HasDiscount
            FROM Cust_Discount
        GROUP BY Cust_ID) AS t1
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now