function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

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

System.QueryException: unexpected token: WHERE

String assesment_query = 'SELECT a,b,c,d,e,f,g,h,i,j'+ 
+'FROM Assesment__c WHERE Candidate__c IN '													+'(SELECT Candidate_Contact__c '+																		+'FROM Application__c '+																		+'WHERE Job__c =:'+JobOrder+' '+																		+'AND Stage__c =:'+offer+')'+
		        +' AND RecordTypeId =:'+ProfileId+';';
List<Assesment__c> assesment = Database.query(assesment_query);

 Hi all,

 

I have the code above and gives me this error:

 

System.QueryException: unexpected token: WHERE

 If I execute this directly (eg.: List<Assessment__c> assessment = [the query here]; Works perfectly.

 

In the string query:

JobOrder is an ID.

offer is a String.

ProfileId is an ID.

 

Does anyone knows why this happens?. Do you know of any solution?

 

Thank you!,

 

MGA

Best Answer chosen by Admin (Salesforce Developers) 
soofsoof

Several issues I can see...  The error message that you're getting is b/c there's no space b/w your last column name in the SELECT (which is 'j'), and the keyword FROM.  You'll still see several other errors even if you fix this.  Try the below query:

String assesment_query = 
	'SELECT a,b,c,d,e,f,g,h,i,j '+ 
	'FROM Assesment__c ' +
	'WHERE Candidate__c IN ' +
		'(SELECT Candidate_Contact__c ' +
		'FROM Application__c ' +
		'WHERE Job__c = \'' + JobOrder + '\' ' +
			'AND Stage__c = \'' + offer + '\') ' +
		'AND RecordTypeId = \'' + ProfileId + '\'';
List<Assesment__c> assesment = Database.query(assesment_query);

 

Here's the issues that I've fixed:

1) Removed the colons (:) in where clause - you don't need them in dynamic query.

2) Surrounded variables w/ single-quotes (\') - you need them in dynamic query.

3) Removed the semi-colon at the end of the query - you don't need it in dynamic query.

 

I haven't executed this query b/c I don't have the data-model, so there' might be a few fixes required here and there.  Hope this helps.

 

Thanks.

All Answers

Richa KRicha K

Hi,

Try replace

String assesment_query = 'SELECT a,b,c,d,e,f,g,h,i,j'+ 
+'FROM Assesment__c WHERE Candidate__c IN '													+'(SELECT Candidate_Contact__c '+																		+'FROM Application__c '+																		+'WHERE Job__c =:'+JobOrder+' '+																		+'AND Stage__c =:'+offer+')'+
		        +' AND RecordTypeId =:'+ProfileId+';';

 

With,

 

 

String assesment_query = 'SELECT a,b,c,d,e,f,g,h,i,j'+'FROM Assesment__c WHERE Candidate__c IN '+'(SELECT Candidate_Contact__c '+'FROM Application__c '+'WHERE Job__c =:\'+JobOrder+'+'AND Stage__c =:\'+offer+\')'+'AND RecordTypeId =:\'+ProfileId+\';';

 

 Single lined. I have not formated it for multiline.
magandrezmagandrez

Hi,

 

I've been trying with that token ( '\' ) but it doesn't work at all.

 

Any other idea?

 

MGA
soofsoof

Several issues I can see...  The error message that you're getting is b/c there's no space b/w your last column name in the SELECT (which is 'j'), and the keyword FROM.  You'll still see several other errors even if you fix this.  Try the below query:

String assesment_query = 
	'SELECT a,b,c,d,e,f,g,h,i,j '+ 
	'FROM Assesment__c ' +
	'WHERE Candidate__c IN ' +
		'(SELECT Candidate_Contact__c ' +
		'FROM Application__c ' +
		'WHERE Job__c = \'' + JobOrder + '\' ' +
			'AND Stage__c = \'' + offer + '\') ' +
		'AND RecordTypeId = \'' + ProfileId + '\'';
List<Assesment__c> assesment = Database.query(assesment_query);

 

Here's the issues that I've fixed:

1) Removed the colons (:) in where clause - you don't need them in dynamic query.

2) Surrounded variables w/ single-quotes (\') - you need them in dynamic query.

3) Removed the semi-colon at the end of the query - you don't need it in dynamic query.

 

I haven't executed this query b/c I don't have the data-model, so there' might be a few fixes required here and there.  Hope this helps.

 

Thanks.

This was selected as the best answer
magandrezmagandrez

Hi soof,

 

Many thanks for this valuable help. Indeed the query didn't need anything else besides the points you mention.

 

And besides get this thing done, I learned those three points about dynamic queries.

 

Thank you again.

 

MGA.