Hello,
I’m new to the WmSalesforeAdapter.
Currently I have a business requirements which should extract data from multiple Saleasforce objects. Using CustomQuery we can get this, but I’m not familiar with SOQL so can you please help me with the custom query service with complex JOIN conditions.

Appreciate all experts help.

Regards
Chapa

Please google it and any SQL site can provide you the query syntax (make sure you have a better understanding on your source DB/tables that are scope in the project)

HTH,
RMG

@PC,

You need to read the Salesforce API at force.com. Ref: [http://techcommunity.softwareag.com/ecosystem/documentation/webmethods/adapters_estandards/Adapters/Salesforce/Salesforce_Adapter_8-2/8-2_Salesforce_Adapter_Install_and_Users_Guide.pdf]

Salesforce.com imposes restrictions on the number of IDs that can be passed for a single call. Ensure that the number of IDs passed to a single Retrieve operation does not exceed the maximum limit. For more information, see the Force.com Web Services API Developer’s Guide.

HTH.

Thanks,
Rankesh

Thanks Rankesh,
Actually I am able to get data from single object when I use Retrieve operation… however the current requirement is to extract data from 2 or more objects for that I’ve to use CustomQuery operation.

Regards
Chapa

@rmg,

I’ve been trying to google and get the correct syntax for the custom query… however the challenge I’ve is, some of the object I’ve to extract are custom objects… and not completely clear on how to give the join condition.

I’m looking at the salesfore websites as well.

Regards
Chapa

Any assistance you get from your DBA’s side since they might know more on the objects you are taking about?

HTH,
RMg

Below is the Query I’m using and I’m getting below error… Can you please let me know what this error means
I’m trying to get data from Account object with CVR__c (Customer Visit Report) object.

Query:

SELECT Id, Name, (SELECT Id, Visit_Date__c from CVR__c WHERE LastModifiedDate >= LAST_N_DAYS:7) FROM Account

Error message:

com.wm.app.b2b.server.ServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service service:TestCustomSOQLwithSubQuery.
[ADA.505.1011] API Fault occurred while executing the call [CustomQuery], Code: INVALID_TYPE, Message:
(SELECT Id, Visit_Date__c from CVR__c WHERE LastModifiedDate >=
^
ERROR at Row:1:Column:49
Didn’t understand relationship ‘CVR__c’ in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the ‘__r’ after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.

However when I’m querying the above SELECT statements separately I’m getting results individually.

SELECT Id, Name FROM Account
SELECT Id, Visit_Date__c from CVR__c

These 2 queries working absolutely fine for me. It is my understanding that if there is an “__c” (underscore underscore) that means the object is custom object.

Can you try to run this whole SQL statement using a client tool (TOAD or similar tool ) and see if that is working?

SELECT Id, Name, (SELECT Id, Visit_Date__c from CVR__c WHERE LastModifiedDate >= LAST_N_DAYS:7) FROM Account

HTH,
RMg

@rmg

Thanks for your input.

Actually I’m not talking Database SQL… I’m trying to get data from Salesforce.com using SOQL (Salesforce Object Query Language).

So I think TOAD didn’t work.

Regards
Chapa

Please run this issue with SAG support also and they can troubleshoot more on the SFDC Adapter level.

Also what SF adapter fix level have you installed on?

HTH,
RMG

I’m working with SAG support as well.

I’ve installed WmSalesforceAdapter 8.2

Regards
Chapa

OK great.

Please make sure you have all the latest and greatest fixes installed for WmSalesForceAdapter.

HTH,
RMG

Your query is wrong. Use ForceExplorer - Workbench and validate your query.

@sury,

Thank you very much for your help.

The link you have provided to verify the query is very very helpful.

I was able to validate the query and correct it.

I appreciate your time.

Regards
Chapa

OK good resource:

Do you recall what is the final query ? I am stuck at the same type of query with the same type of error. Workbench does not provide joining objects queries.

Please use workbench(Workbench) and login into your SFDC developer instance to verify the same SOQL is working fine.

Thanks. I tried and also learnt that joining SQL queries and SOQL queries are totally different and we need to add __r which explains the relation between the tables.

HI Anupama,

Please read https://resources.docs.salesforce.com/sfdc/pdf/salesforce_soql_sosl.pdf and try yourself the SOQL in workbench connecting to your SFDC instance. You will pick up it in no time. Happy learning.

Regards,
Mike

Thanks Mike. I am going through that path now