Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
lashaklashak 

Query for All accounts with Client ID and (opportunity stage 6 or Client ID)

Hello,

I am trying to get my query to only return Accounts that have a client Id as well as opportunities that are in stage 6 or have a client id.

 

This is what I have so far:

 

Select a.Website, a.Sub_Status__c, a.Status__c, a.Official_Name__c, a.Name, a.Industry, a.Id, a.Client_ID__c,

(Select o.Id, o.Name, o.StageName, o.AccountId From Opportunities o Where o.StageName = '6 - Closed/Won' Or o.Account.Client_ID__c != NULL ),

a.Business_Unit__c, a.BillingStreet, a.BillingState, a.BillingPostalCode, a.BillingCountry, a.BillingCity,

a.D_U_N_S__c, a.Tax_Number__c, a.Owner.Name, a.Owner.EmployeeNumber, a.Company_Reg__c, a.Channel_Owner__c From Account a

 

The above returns me all of the accounts for our company and joins the opportunities that are either stage 6 or have a client id. 

 

What I need the query to do is return only Accounts with a client ID as well as Accounts that have an opportunity in Stage 6 or opportunities that have a client ID.

 

If I add a where clause at the end of the main select such as WHERE a.Client_ID__c !=NULL, I get all the accounts that have client ids but I miss the accounts that have an opportunity in stage 6 that don't have a client ID.

 

I am really hoping someone out here can help me out.

 

Thank you,

 

lashaklashak

Good Morning,

Hoping that someone might be able to help.

 

Thanks,
SuperfellSuperfell

you should be able to do something like

 

select ... from account where clientId__c != null or id in (select accountId from opportunities where stageName='6 - Closed/Won' or clientId__c != null)
lashaklashak

Thanks for the reply.  I am getting the error that semi-joins aren't allowed in this version of the API.  What version do I need to be at for this to work?
SuperfellSuperfell
15 i think.
lashaklashak

I can see that we have version 17 of the salesforce API.

 

This is the error I am getting when trying to set the query up in force.com ide:

UNSUPPORTED_API_VERSION -
Where a.Client_ID__c != null or a.Id in (Select AccountId from Opportunities
                                ^
ERROR at Row:1:Column:372
Semi joins are not supported in this version of the API
[UnexpectedErrorFault [ApiFault  exceptionCode='UNSUPPORTED_API_VERSION'
 exceptionMessage='
Where a.Client_ID__c != null or a.Id in (Select AccountId from Opportunities
                                ^
ERROR at Row:1:Column:372
Semi joins are not supported in this version of the API'
 upgradeURL='null'
 upgradeMessage='null'
]
]

 
SuperfellSuperfell
You're not using v17, look at the wsdl/serverUrl that you're actually connecting to.
SaaniaSaania

Hi, I am running into the same issue of 'Semi joins are not supported in this version of the API'. Our API version is 21 I guess ! (From the following link in app.config file).

https://login.salesforce.com/services/Soap/c/21.0/0DF600000008Qkz

Query to execute:

 

Select Id, contact_ID__c From Contact  where Id IN (Select ContactId From CampaignMember where CampaignId = '70160000000M1hxAAC')

 

 

Thanks,

Saania
SuperfellSuperfell

Then you'll need to upgrade to a newer API version.
SaaniaSaania

How do you update to a newer API version? I already got the latest WSDL?

 

Thanks !
SaaniaSaania

I am still waiting on a response for this one.

 

Thanks !
SuperfellSuperfell

Get the latest WSDL, make sure your code is not overriding the endpointUrl with a hardocded URL with a lower version number in it.
SaaniaSaania

Thanks Simon, But as i had mentioned earlier, I do have the latest WSDLs for my Enterprise and MetaData. The app.config and Settings link do point to the latest version I just downloaded (24.0) ... I dont know what else to do here !

 

Link to enterprise WSDL from my app.config file is specified below.

 

<value>https://login.salesforce.com/services/Soap/c/24.0/0DF60000000H43w</value>

 

Thanks once again ...