Nested Union query?

  • Hello,

    I'm trying to develop a report using Dynamics CRM tables using BIDS connecting to a SQL Server.

    It's a marketing report using the following entities:

    Campaign

    Campaign Response

    Activity Party

    Contacts

    Leads

    Accounts

    Opportunities

    For those not familiar with Dynaimcs CRM entities and their relationships activity partty is essentially a bridge table linking one entity to another. In this case campaign response is connected to contacts, leads and accounts.

    I would like to report on all entites in 1 dataset but i'm unsure how I'd do this and I'm looking for some guidance.

    If I could have a UNION subquery that looks something like this:

    SELECT accountid AS entityid FROM FilteredAccount

    UNION

    SELECT contactid AS entityid FROM FilteredContact

    UNION

    SELECT leadid AS entityid FROM FilteredLead

    within the existing SQL i think that would work, though i'm not even sure that this is possible, hence why I'm asking here.

    Any ideas?

  • I'm not familiar enough with Dynamics databases specifically to say yes/no on what you're doing.

    However, the basic practice of what you're doing looks sound. Do that in a CTE or regular derived table (we need a bacronym for those!), and then use that for the rest of the query.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Thanks, That seems a very obvious answer once I read it. D'oh! Which leads me to see a fatal floor in my plan and subsequently onto my next question.

    So for contactid and accountid link to customerid within an Opportunity record but leads link to another field within that record - originatingleadid. I guess that I need some sort of OR clause...

    Any further suggestions?

    Here's the SQL so far with the emboldened text highlighting the problem that needs resolving:

    SELECT FilteredCampaign.name AS CampaignName, FilteredOpportunity.estimatedvalue, FilteredOpportunityProduct.synergy_monthlyrevenue,

    FilteredOpportunityProduct.baseamount, FilteredOpportunityProduct.productdescription, FilteredOpportunityProduct.productidname,

    FilteredOpportunityProduct.synergy_quantity, FilteredOpportunityProduct.synergy_stagename, FilteredOpportunityProduct.synergy_tenuremonths,

    FilteredOpportunityProduct.synergy_statusname, FilteredSystemUser.fullname, derivedtbl_1.entityid

    FROM FilteredCampaign INNER JOIN

    FilteredCampaignResponse ON FilteredCampaign.campaignid = FilteredCampaignResponse.regardingobjectid INNER JOIN

    FilteredActivityParty ON FilteredCampaignResponse.activityid = FilteredActivityParty.activityid INNER JOIN

    FilteredOpportunity ON FilteredCampaign.campaignid = FilteredOpportunity.campaignid INNER JOIN

    FilteredOpportunityProduct ON FilteredOpportunity.opportunityid = FilteredOpportunityProduct.opportunityid INNER JOIN

    FilteredSystemUser ON FilteredOpportunityProduct.ownerid = FilteredSystemUser.systemuserid INNER JOIN

    (SELECT accountid AS entityid

    FROM FilteredAccount AS FilteredAccount_1

    UNION

    SELECT contactid AS entityid

    FROM FilteredContact AS FilteredContact_1

    UNION

    SELECT leadid AS entityid

    FROM FilteredLead) AS derivedtbl_1 ON FilteredActivityParty.partyid = derivedtbl_1.entityid AND FilteredOpportunity.customerid = derivedtbl_1.entityid AND

    FilteredOpportunity.originatingleadid = derivedtbl_1.entityid

  • I'm not clear enough on the data structure you're dealing with.

    You don't have to link a table to every other table with a matching column. Are you trying to match some rows to one part of the query and other rows to another part?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Yes and no.

    I'll try to explain. The way our sales process works after a marketing campaign is like this:

    Campaign is sent out to multiple entities(essentially a different tables for each type in this case accounts, contacts and leads). Campaign responses are generated for interested parties. When campaign responses are converted to opportunities a unique link is created in the activityparty table between campaign response and either a lead, contact or account.

    With me so far? I hope so. I need data from the campaign, opportunity and opportunityproduct tables to show in the report but I need the closed loop from opportunity to campaign and that's where the campaign response comes in.

    Thanks for your time btw.

  • This seems to work:

    SELECT DISTINCT

    FilteredCampaign.name AS CampaignName, FilteredOpportunity.estimatedvalue, FilteredOpportunityProduct.synergy_monthlyrevenue,

    FilteredOpportunityProduct.baseamount, FilteredOpportunityProduct.productdescription, FilteredOpportunityProduct.productidname,

    FilteredOpportunityProduct.synergy_quantity, FilteredOpportunityProduct.synergy_stagename, FilteredOpportunityProduct.synergy_tenuremonths,

    FilteredOpportunityProduct.synergy_statusname, FilteredSystemUser.fullname, derivedtbl_1.entityid

    FROM FilteredCampaign INNER JOIN

    FilteredCampaignResponse ON FilteredCampaign.campaignid = FilteredCampaignResponse.regardingobjectid INNER JOIN

    FilteredActivityParty ON FilteredCampaignResponse.activityid = FilteredActivityParty.activityid INNER JOIN

    FilteredOpportunity ON FilteredCampaign.campaignid = FilteredOpportunity.campaignid INNER JOIN

    FilteredOpportunityProduct ON FilteredOpportunity.opportunityid = FilteredOpportunityProduct.opportunityid INNER JOIN

    FilteredSystemUser ON FilteredOpportunityProduct.ownerid = FilteredSystemUser.systemuserid INNER JOIN

    (SELECT accountid AS entityid

    FROM FilteredAccount AS FilteredAccount_1

    UNION

    SELECT contactid AS entityid

    FROM FilteredContact AS FilteredContact_1

    UNION

    SELECT leadid AS entityid

    FROM FilteredLead) AS derivedtbl_1 ON FilteredActivityParty.partyid = derivedtbl_1.entityid AND FilteredOpportunity.customerid = derivedtbl_1.entityid OR

    FilteredOpportunity.originatingleadid = derivedtbl_1.entityid

    I don't totally understand SQL so I'm curious to know whether this is sound code?

    Lea

  • I really can't speak to the validity of the code. Yes, it's a valid query, in that in compiles and returns results. But as far as being business-correct, there's no way I can speak to that.

    I suggest testing it by breaking it in three parts, one for each "union" query, and running each of those. That way, you make sure you're getting results for each. Otherwise, you might be masking an internal error. If you're only getting expected results from one of the queries, and one or both of the others are empty, you won't see that when they're all unioned together, but you will if you isolate them for testing.

    That'll at least help you validate the results.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply