4

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 ';
Improve this question
4
  • 1
    What error are you receiving? Also, this looks like SOQL, not SOSL. Please edit title and tags.
    – Thomas Taylor
    Oct 28, 2014 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, 2014 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, 2014 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, 2014 at 16:20

2 Answers 2

Reset to default
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), ' +
Improve this answer
3
  • Thanks! That fixed it! So in case I need to check opposite, is it, WHERE Risk__Status__c !=\'Closed\' ?
    – Tinkerbell
    Oct 28, 2014 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, 2014 at 16:04
  • Ok, I actually didn't know this! Thanks for your clarification and explanation! Thanks a bunch!!
    – Tinkerbell
    Oct 28, 2014 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
Improve this answer
2
  • 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, 2014 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, 2014 at 16:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .