CODEWITHSUNDEEP
oracle-databaseplsql
Sebastian Hubard
Sebastian Hubard

Reputation: 163

Using case function to categorize values

I'm trying to basically make a categorization filed called "Activity_Group" for a list of event names. I would like to bucket all our major events together, however, the naming convention for these events are all over the place so I wanted to basically do the following logic:

If value contains string 'X' then return 'Event 1', 
If value contains string 'Y' then return 'Event 2',...

Below is my first attempt at doing this with a case statement, however, I'm not sure how to filter out all those that I declare 'null' in the case statements. I'm also not sure if this is event the best method of accomplishing my goal since it seems to open the query up to double-counting events (which I would like to avoid)

select e.attendee_id                AS id_number, 
       'Events'                     AS Activity_Category,
       e.Event_Name                 AS activity_item,
       (case when e.event_name like '%Keyword 1A%' then 'Event 1' else null end)  AS activity_group,
       (case when e.event_name like '%Keyword 1B%' then 'Event 1' else null end)  AS activity_group,
       (case when e.event_name like '%Keyword 2A%' then 'Event 2' else null end)  AS activity_group,
       (case when e.event_name like '%Keyword 2B%' then 'Event 2' else null end)  AS activity_group,
       e.venue_desc                 AS Activity_Desc,
       e.Participant_status_desc    AS status,
       e.event_start_datetime       AS start_date,
       e.event_stop_datetime        AS stop_date
 from Events_Table e where Activity_Group is not null

Any help on this would be greatly appreciated. Thank you

Upvotes: 0

Views: 38

Answers (1)

psaraj12
psaraj12

Reputation: 5072

Use like the below with only one case statement

SELECT *
    FROM   (SELECT e.attendee_id             AS id_number,
                   'Events'                  AS Activity_Category,
                   e.event_name              AS activity_item,
                   ( CASE
                       WHEN e.event_name LIKE '%Keyword 1A%' THEN 'Event 1'
                       WHEN e.event_name LIKE '%Keyword 1B%' THEN 'Event 1'
                       WHEN e.event_name LIKE '%Keyword 2A%' THEN 'Event 2'
                       WHEN e.event_name LIKE '%Keyword 2B%' THEN 'Event 2'
                       ELSE NULL
                     END )                   AS activity_group,
                   e.venue_desc              AS Activity_Desc,
                   e.participant_status_desc AS status,
                   e.event_start_datetime    AS start_date,
                   e.event_stop_datetime     AS stop_date
            FROM   events_table e)
    WHERE  activity_group IS NOT NULL;

Upvotes: 1

Related Questions