Need help deciphering this SQL code

Question

Could someone please explain the following code in simple terms, it landed on my desk this morning and i can read bits of it but other parts just simply confuse me, can some please just give a brief overview of what this code would return in respect of results?

Thanks here it is....

drop table selection_all_journey  purge;   
create table selection_all_journey  as        

select  distinct
    ca.activity_id,
    ca.campaign_code,
    --ca.agent_activity_description,

    case when sl.filter3 = 'DB' then 'RES'
         when sl.filter3 = 'SB' then 'EON'
         when ca.agent_activity_description = 'Additional DM' then 'Trial 6 - DM'
            else ca.agent_activity_description end as agent_activity_description,

    case when ca.channel_id = 'DM' then 'Direct Mail'
         when ca.channel_id = 'EM' then 'Email'
         when ca.channel_id = 'TS' then 'Outbound_Calling'
            else ca.channel_id end as channel_id,

    sl.ice_customer_id,
    sl.account_reference,
    sl.load_date,
    sl.mail_date,
    sl.filter1

from    cam.campaign_activity ca 

join  cam.selection_log sl
    on  ca .activity_id = sl.activity_id  [ OK ]

Answer 1

The best thing to do with SQL is to look at the underlying tables; usually, the JOIN conditions or WHERE clause will give you insight into how the author of the procedure understands the relationships between tables.

So, starting with:

from    cam.campaign_activity ca 

join  cam.selection_log sl on  ca .activity_id = sl.activity_id

This is what is known as an INNER JOIN.

The authors intent here is to get a list of all rows from campaign_activity that has a matching row in selection_log So this query will take all rows that have a matching row in selection log based on having a matching activity id.

Jeff Atwood has a great introduction to SQL JOIN's here.

The next part is to consider the SELECT list.

There are two things here that should give you pause for thought; the first is the use of DISTINCT. This will filter out duplicate records. So effectively, if you have two rows in the underlying data that have exactly the same values, the output will only consist of one of those rows.

The second is the use of the CASE statement. The author here is adding extra meaning to the data; what they are saying is that, for example:

when the filter3 field is the string 'SB', the query should return 'EON'.

It's the same for the channel id.

People often do this when they want to translate a database field into a value that means something to someone looking at the results of the query. They often also use it to group values of results together.

The query as it stands is fairly straightforward; if I had to guess, I would say it's bringing back a list of distinct activities that have happened on marketing campaigns for a number of clients.

Once you understand the schema, and the intent, the rest is just SQL.

I've voted to close because the question is too localized, but I hope this gives you some help on your way to understanding the schema and queries!

Answer 2

Basically you are selecting records from two tables cam.campaign_activity and cam.selection_log and you are doing an inner join on the activity_id. An INNER JOIN produces only the set of records that match both tables. (See Visual Explanation of JOINs)

The CASE statements explained:

First CASE:

case when sl.filter3 = 'DB' then 'RES'
     when sl.filter3 = 'SB' then 'EON'
     when ca.agent_activity_description = 'Additional DM' then 'Trial 6 - DM'
        else ca.agent_activity_description end as agent_activity_description

This is telling you which field to get for the agent_activity_description.

• If sl.filter3 = 'DB' then the value is 'RES'
• If sl.filter3 = 'SB' then the value is 'EOS'
• If ca.agent_activity_description = 'Additional DM' then the value is 'Trial 6 - DM'
• If it doesn't meet any of those criteria then you will use the value of ca.agent_activity_description

Second CASE:

case when ca.channel_id = 'DM' then 'Direct Mail'
     when ca.channel_id = 'EM' then 'Email'
     when ca.channel_id = 'TS' then 'Outbound_Calling'
        else ca.channel_id end as channel_id,

This is telling you which field to get for the channel_id.

• If ca.channel_id = 'DM' then the value is 'Direct Mail'
• If ca.channel_id = 'EM' then the value is 'Email'
• If ca.channel_id = 'TS' then the value is 'Outbound_Calling'
• If it doesn't meet any of those criteria then you will use the value of ca.channel_id