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

How are tables "flattened" when using DBAmp and OPENQUERY ?

I don't understand the difference between SQL and SOQL in terms of the output table produced.
1 person has
this question
+1
Reply
  • When DBAmp returns the result of an OPENQUERY with a SOQL statement containing subqueries, it uses a special hierarchical format for the result.

    With SQL queries, the result table is the normal SQL Cartesian product of the joined tables:

    select A.Id, A.Name, C.FirstName, O.Amount
    from SALESFORCE...Account A
    inner join SALESFORCE...Contact C on C.AccountId = A.Id
    inner join SALESFORCE...Opportunity O on O.AccountId = A.Id
    where A.Id ='0016000000G8lSn'

    Id Name FirstName Amount
    0016000000G8lSnAAJ Dickenson plcs Babs 80000.00
    0016000000G8lSnAAJ Dickenson plcs Josh 80000.00
    0016000000G8lSnAAJ Dickenson plcs Babs 120000.00
    0016000000G8lSnAAJ Dickenson plcs Josh 120000.00

    With SOQL queries, the subqueries are returned without a Cartesian product.

    select * from openquery(salesforce,
    'Select Id, Name,
    (select Firstname from Contacts) ,
    (Select Amount from Opportunities)
    from Account where id=''0016000000G8lSn'' ')

    Id Name Contacts_FirstName Opp_Amount
    0016000000G8lSnAAJ Dickenson plcs Josh NULL
    0016000000G8lSnAAJ Dickenson plcs Babs NULL
    0016000000G8lSnAAJ Dickenson plcs NULL 80000.00
    0016000000G8lSnAAJ Dickenson plcs NULL 120000.00
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

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