?
Solved

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

Posted on 2011-03-20
2
Medium Priority
?
283 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 1000 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 1000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

777 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