Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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.
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.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

939 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

Need Help in Real-Time?

Connect with top rated Experts

4 Experts available now in Live!

Get 1:1 Help Now