Solved

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

Posted on 2011-03-20
2
282 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 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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

724 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