3

I have the following SOQL and I only need values from Risk__Actions__r WHERE Risk__Status__c(Picklist) equals to 'Open'. But current way i'm doing displays an error.

no viable alternative at character '"'

Refer to the first Inner Query. Please help on this!

queryString += 'SELECT Id, Name, Owner.Name, grc__Description__c, Risk__Risk_Template__r.Name, Risk__Control_Effectiveness__c, Risk__Control_Effectiveness_Rating__c, grc__Business_Unit__c, grc__Business_Unit__r.Name, ' + 
                            'grc__Category__c, grc__Impact__c, Risk__Inherent_Rating_Lookup__r.Risk__Colour__c, Risk__Residual_Rating_Lookup__r.Risk__Colour__c, grc__Risk_No_Value__c, ' + 
                            'grc__Risk_Owner__r.Name, grc__Next_Review_Date__c, grc__Ranking__c, grc__Risk_Appetite_Statement__c, ' +
                            'Risk__Inherent_Score__c, Risk__Inherent_Impact_Text__c, Risk__Inherent_Likelihood_Text__c, Risk__Inherent_Rating__c, ' +
                            'Risk__Residual_Score__c, Risk__Residual_Impact_Text__c, Risk__Residual_Likelihood_Text__c, Risk__Residual_Rating__c, ' +
                            'Risk__Target_Score__c, Risk__Target_Impact_Text__c, Risk__Target_Likelihood_Text__c, Risk__Target_Rating__c, grc__Risk_Name__c, ' +
                            '(SELECT Name, Risk__Status__c FROM Risk__Actions__r WHERE Risk__Status__c="Open" order by Name), ' +
                            '(SELECT Title, Body FROM Notes WHERE IsPrivate = false), ' +                           
                            '(SELECT Risk__Cause__r.Name FROM Risk__Cause_Risk_Lookup__r order by Risk__Cause__r.Name), ' +
                            '(SELECT Risk__Control__r.Name, Risk__Control__r.grc__Description__c, Risk__Is_Key_Control__c FROM Risk__Risk_Control_Lookup__r WHERE Risk__Is_Key_Control__c = true order by Risk__Control__r.Name) ' +
                        'FROM grc__Risk__c ' +
                        'WHERE grc__Business_Unit__c IN :selectedBUs ' +
                            'AND grc__Status__c IN :riskStatus ' +
                            'AND grc__Category__c IN :categories '; 
  • 1
    What error are you receiving? Also, this looks like SOQL, not SOSL. Please edit title and tags. – Thomas Taylor Oct 28 '14 at 15:43
  • Ok, I have updated the title and tags. The error I get when I run my VF page is- no viable alternative at character '"' – Tinkerbell Oct 28 '14 at 15:46
  • 2
    Is there a reason you need to use Dynamic SOQL in this case rather than "Regular" SOQL? If not, I would change your query to use regular SOQL, as the code is easier to maintain. For instance, you don't have to worry about escaping single quotes in the string and don't have to worry about single quotes around each line, etc. – James Loghry Oct 28 '14 at 16:10
  • Yeah, true! This's been written ages ago and i'm modifying it now! I should consider using Regular SOQL instead Dynamic! Thanks – Tinkerbell Oct 28 '14 at 16:20
10

You need escaped single quotes around the picklist value instead of double quotes:

'(SELECT Name, Risk__Status__c FROM Risk__Actions__r WHERE Risk__Status__c=\'Open\' order by Name), ' +
  • Thanks! That fixed it! So in case I need to check opposite, is it, WHERE Risk__Status__c !=\'Closed\' ? – Tinkerbell Oct 28 '14 at 15:59
  • 1
    Right. The principles at work are that a) you must use single quotes to delimit strings in SOQL, and b) you must escape single quotes with backslashes in Apex strings in order to make them part of the string. The SOQL operators are not affected. – Thomas Taylor Oct 28 '14 at 16:04
  • Ok, I actually didn't know this! Thanks for your clarification and explanation! Thanks a bunch!! – Tinkerbell Oct 28 '14 at 16:16
3

You can't use those double quotes like that. Single quotes should work just fine to query for a picklist value.

so try:

SELECT Name, Risk__Status__c FROM Risk__Actions__r WHERE Risk__Status__c=\'Open\' order by Name
  • 1
    Totally sorry about the duplicate post/answer! Was writing my reply at the same time i was talking to someone else. Glad its fixed!!!! PS this may help you out with using characters in strings. Looks like you're working with some big queries there. salesforce.com/us/developer/docs/soql_sosl/Content/… – cricketlang Oct 28 '14 at 16:30
  • You are most welcome and thanks for the answer, I really appreciate it. Thank you very much for the link provided, really helful! Regards – Tinkerbell Oct 28 '14 at 16:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.