Solved

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

Posted on 2011-03-20
2
279 Views
Last Modified: 2012-06-27
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
Comment
Question by:wdelaney05
2 Comments
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 250 total points
ID: 35177858
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
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 250 total points
ID: 35178514
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question