Help get this topic noticed by sharing it on Twitter, Facebook, or email.

Table Joins

I have a problem joining table together I'm using DBAmp with MS SQL server Management Studio... This query works fine...

select * from OpenQuery(CTFC_SANDBOX2, '
SELECT

OpportunityLineItem.Opportunity.Account.Name,
OpportunityLineItem.Outstanding_Balance__c,
OpportunityLineItem.Pay_Online__c

FROM OpportunityLineItem

WHERE

OpportunityLineItem.Opportunity.Account.Name LIKE ''k%'' AND
OpportunityLineItem.Opportunity.Account.BillingStreet <> NULL AND
OpportunityLineItem.Opportunity.Account.BillingState <> NULL

')

----------------------------------------------------------
select * from OpenQuery(CTFC_SANDBOX2, '
SELECT

OpportunityLineItem.Opportunity.Account.Name,
OpportunityLineItem.Opportunity.Account.Contact.Email,
OpportunityLineItem.Outstanding_Balance__c,
OpportunityLineItem.Pay_Online__c

FROM OpportunityLineItem

WHERE

OpportunityLineItem.Opportunity.Account.Name LIKE ''k%'' AND
OpportunityLineItem.Opportunity.Account.BillingStreet <> NULL AND
OpportunityLineItem.Opportunity.Account.BillingState <> NULL

')

This results in the following error

OLE DB provider "DBAmp.DBAmp" for linked server "CTFC_SANDBOX2" returned message "Error 2032 : Relationship Contact is not valid.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "DBAmp.DBAmp" for linked server "CTFC_SANDBOX2".

---------------------------------------------------------

How is the best way to execute this...I have also tried carrying out joins but get nowhere
1 person has
this question
+1
Reply
  • "Error 2032 : Relationship Contact is not valid"

    In SOQL this relationship is invalid: OpportunityLineItem.Opportunity.Account.Contact.

    because an Account can have multiple contacts and a 1 to many relationship is not allowed at this point in SOQL.

    What Contact are you trying to pull ? There is a join table called OpportunityContactRoles that enumerates the Contacts associated with an opportunity. Are you trying to pull one (or all) of those contacts ?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • It is the Email address of the Lead contact, which is what I am trying to get to. I'll check out OpportunityContactRoles

    It has been described to me as there is a lead which is linked to an oppotunity, from the lead it picks one of the contacts.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • I’m happy
    Select ContactId FROM OpportunityContactRole how would you go about joining all this together...SalesForce doesn't seem to follow in a logical programmitcal manner
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • SOQL does handle relationships differently then SQL.

    But SOQL is the fastest because the joins are done on the salesforce server.

    Here's a shot a query that connects the tables you want:

    Select * from Openquery(SALESFORCE,'Select (select Contact.Email From OpportunityContactRoles Where (IsPrimary = True) ), Name, (select TotalPrice, Quantity From OpportunityLineItems ), Account.Name From Opportunity')

    Notice that you have to start with Opportunity as the base object to get data from both OpportunityLineItems and OpportunityContactRoles
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • select * from OpenQuery(CTFC_SANDBOX2, '
    SELECT

    OpportunityLineItem.Opportunity.Account.Name,
    OpportunityLineItem.Opportunity.OpportunityContactRole.ContactId,
    OpportunityLineItem.Outstanding_Balance__c,
    OpportunityLineItem.Pay_Online__c

    FROM OpportunityLineItem

    WHERE

    OpportunityLineItem.Opportunity.Account.Name LIKE ''k%'' AND
    OpportunityLineItem.Opportunity.Account.BillingStreet <> NULL AND
    OpportunityLineItem.Opportunity.Account.BillingState <> NULL

    ')
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • OpportunityContactRole - I can't work out where or who this is related to?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • It is a child object of the Opportunity but is one to many.

    Therefore you CANNOT use the 'dot' notation: Opportunity.OpportunityContactRole.ContactId

    You must use a subselect: 'Select (select Contact.Email From OpportunityContactRoles Where (IsPrimary = True) )
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • Thanks for your help Bill - I'm nearly there - I can write this select * FROM OpenQuery(CTFC_SANDBOX2, 'Select ( select Contact.Email From OpportunityContactRoles Where (IsPrimary = True) ) FROM Opportunity')
    and get results, but when I include this in the main query it says "Error 2032 : Relationship OpportunityContactRoles is not valid."

    select * from OpenQuery(CTFC_SANDBOX2, '
    SELECT
    ( select Contact.Email From OpportunityLineItem.Opportunity.OpportunityContactRoles Where (IsPrimary = True) ),
    OpportunityLineItem.Opportunity.Account.Name,
    Opportunity.OpportunityContactRole.ContactId,
    OpportunityLineItem.Outstanding_Balance__c,
    OpportunityLineItem.Pay_Online__c

    FROM OpportunityLineItem

    WHERE

    OpportunityLineItem.Opportunity.Account.Name LIKE ''k%'' AND
    OpportunityLineItem.Opportunity.Account.BillingStreet <> NULL AND
    OpportunityLineItem.Opportunity.Account.BillingState <> NULL

    ')
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • So the SOQL rule is that dot notation can only be used for one to one relationships.

    OpportuntiyLineItem.Opportunity OK 1-1
    Opportunity.OpportunityContactRoles NOT OK 1 to many

    So you have to have your base object be different. See the example I wrote above where I used Opportunity as the base object of the query.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated

  • SLowly...slowly :)

    select * from OpenQuery(CTFC_SANDBOX2, '
    SELECT
    ( select Contact.Email From OpportunityContactRoles Where (IsPrimary = True) ),
    Opportunity.Account.Name

    FROM Opportunity

    WHERE

    Opportunity.Account.Name LIKE ''k%'' AND
    Opportunity.Account.BillingStreet <> NULL AND
    Opportunity.Account.BillingState <> NULL

    ')

    So this is running ok - but now I'm working on getting the OpportuntiyLineItem joined back in
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited sad, anxious, confused, frustrated