Salesforce.com input step with relationship query

[rhuelsey]

Trying to run a relationship query using the sfdc input step. The SOQL is:

select lead.id, lead.firstname, lead.lastname,
status, campaignId from campaignMember
where leadId !='' and campaignId ='[some campaign id]'

The query as such works fine when firing it to SFDC manually - however: the input step only recognizes the fields that reside in CampaignMember, not the ones that I want to pull from the Lead object (the field list actually just shows 'Lead' as one field, not its the individual properties).

I am sure I am overlooking something here ... anybody able to help?

Thanks!

Rupert

[slivey]

I am having the same issue doing SOQL queries across multiple Salesforce objects.
SOQL employs a dot-notation for joining objects in queries.
SELECT Contact.FirstName, Contact.Account.Name from Contact

However, the Salesforce Input step does not seem to support SOQL queries across multiple objects. Any suggestions?

[vinayakchitre]

I am having the same issue. Does anyone has the solution?

Thanks,
Vinayak

[svogel]

I posted a similar request in April. Has anyone found a solution?

If not, it seems the only way to go about it is to use multiple Salesforce Input steps and join within the transformation itself.

[Sammo]

Don't suppose anyone found a solution to this yet?

[absrivas]

You can use the web services lookup with the relationship query and then parse the response XML as you want. Here is the Query and response that I was able to get through lookup step:
query: Select id,Account.Name from Opportunity
Response trace:

<soapenv:Body>
2013/01/28 12:29:03 - Web services lookup.0 - <queryAllResponse>
2013/01/28 12:29:03 - Web services lookup.0 - <result xsi:type="QueryResult">
2013/01/28 12:29:03 - Web services lookup.0 - <done>true</done>
2013/01/28 12:29:03 - Web services lookup.0 - <queryLocator xsi:nil="true"/>
2013/01/28 12:29:03 - Web services lookup.0 - <records xsi:type="sf:sObject">
2013/01/28 12:29:03 - Web services lookup.0 - <sf:type>Opportunity</sf:type>
2013/01/28 12:29:03 - Web services lookup.0 - <sf:Id>00690000006VxCbAAK</sf:Id>
2013/01/28 12:29:03 - Web services lookup.0 - <sf:Id>00690000006VxCbAAK</sf:Id>
2013/01/28 12:29:03 - Web services lookup.0 - <sf:Account xsi:type="sf:sObject">
2013/01/28 12:29:03 - Web services lookup.0 - <sf:type>Account</sf:type>
2013/01/28 12:29:03 - Web services lookup.0 - <sf:Id xsi:nil="true"/>
2013/01/28 12:29:03 - Web services lookup.0 - <sf:Name>GoodData</sf:Name>
2013/01/28 12:29:03 - Web services lookup.0 - </sf:Account>
2013/01/28 12:29:03 - Web services lookup.0 - </records>

I created a Job and added two transformations, one for login and other with queryAll

[jballment]

I've created this patch for http://jira.pentaho.com/browse/PDI-9599 which may resolve this issue. It works for child-to-parent relationships in soql but not for parent-to-child relationships. Parent-to-child relationships we would need to inject extra records record while parsing the child records. See http://help.salesforce.com/apex/HTVi...language=en_US for details.

[jballment]

The attached code fixes the child-to-parent relationship described in http://help.salesforce.com/apex/HTVi...language=en_US but to implement the parent-to-child relationship we would need to inject new records while parsing the child query. This would be a major change to the existing code.